I'm working on a small and simple comment hosting platform in PHP (SQLITE database) to host comments for my static blog https://prahladyeri.github.io/. No login, sign-ups or third party OAuth, just plain old Wordpress.org style commenting system.

I have come up with the following DB schema so far to store the comments (comments table) and enable the administrator's dashboard authentication (users table). Can this be improved further?

-- init.sql
--
drop table if exists comments;
drop table if exists posts;
drop table if exists users;

create table comments (
    id integer primary key,
    reply_to_id integer references comments (id),
    post_id integer references posts (id),
    message text,
    name text,
    email text,
    website text,
    ip text, -- $_SERVER['REMOTE_ADDR']
    notify text default 'n',
    status text default 'Approved', -- Approved/Spam
    created_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime')),
    modified_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime'))
);

create table posts (
    id integer primary key,
    user_id integer references users (id),
    uri text -- /blog/2024/05/some-slug.html
);

create table users (
    id integer primary key,
    username text not null,
    password text not null,
    email text, -- can be null
    name text not null,
    website text, -- comments will be posted to this site
    role text not null, -- Admin/Staff
    created_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime')),
    modified_at datetime default (datetime(CURRENT_TIMESTAMP, 'localtime')),
    unique (username),
    unique (email)
);

-- create default data
-- create a default admin user who handles to dashboard
insert into users(username,password, name, role, website)
values("admin", "admin108", 'Admin', 'Admin', 'https://example.com/');

Note that the users table here is for the admin users like myself who will administer the hosting system, not the commenters on the blog.