Database schema

Members table

The members table stores the login credentials for the users that can use the app. The SQL to create this database is,

CREATE TABLE members(
    id SERIAL PRIMARY KEY,
    created TIMESTAMP NOT NULL DEFAULT now(),
    email TEXT NOT NULL,
    email_verified TIMESTAMP,
    password_hash TEXT NOT NULL
);

CREATE UNIQUE INDEX members_unique_email_idx on members (LOWER(email));

The id, email, and password_hash columns are the minimum requirements for a login system. Note that a password hash and not the raw password should be stored.

The created column is useful as it allows you to track user growth over time.

The email_verified column indicates if the user has verified that they control the email, which is a crucial thing to check before sending the user emails. This could be a boolean, but storing the timestamp helps understand the user's actions.

See a note with the models that explains why the unique index is used with LOWER.

Todo table

The todos table stores all the todos, linked to the user,

CREATE TABLE todos(
    id BIGSERIAL PRIMARY KEY,
    complete BOOLEAN NOT NULL DEFAULT FALSE,
    due TIMESTAMPTZ,
    member_id INT NOT NULL REFERENCES members(id),
    task TEXT NOT NULL
);

Complete migration

The initial setup of the database should be contained in backend/db/0.py, which should be,

from databases import Database

async def migrate(db: Database) -> None:
    await db.execute(
        """CREATE TABLE members(
               id SERIAL PRIMARY KEY,
               email TEXT UNIQUE NOT NULL,
               password TEXT NOT NULL
           )
        """,
    )
    await db.execute(
        """CREATE TABLE todos(
               id BIGSERIAL PRIMARY KEY,
               complete BOOLEAN NOT NULL DEFAULT FALSE,
               due TIMESTAMPTZ,
               member_id INT NOT NULL UNIQUE REFERENCES members(id),
               task TEXT NOT NULL
           );
        """,
    )

Note

Whilst there isn't an autoformatter I'm aware of, I prefer to write SQL using the sqlstyle.guide guide.