The Schema, Table by Table

One row per chemical element. Unusually, the primary key isn't a generated SERIAL id — it's atomic_number itself, since that number is already a natural, permanent identifier for an element.

Named mixtures such as “Sterling Silver.” A generated SERIAL id, since alloy names aren't a natural key the way atomic numbers are.

Connects alloys to elements (many-to-many: an alloy has several elements, an element appears in several alloys), plus one extra column of its own, percent_of_alloy. Its primary key is the pair of foreign keys, (alloy_id, atomic_number).

Real-world coins. Each coin belongs to exactly one alloy (alloy_id is a required foreign key), so this is a plain one-to-many relationship, not a join table.

sql/metals-db.sqlCREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Notice there is no password column — only password_hash. The API (in metals_api/services/auth_service.py) runs every password through Argon2id before it's ever written here, and the real password is never stored or logged anywhere. username is unique so two accounts can't collide.

sql/metals-db.sqlCREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

Deliberately tiny — just a name. This project seeds exactly two rows, Admin and Customer, but the table design doesn't hardcode that; a third role could be added with a single INSERT.

sql/metals-db.sqlCREATE TABLE user_roles (
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    CONSTRAINT pk_user_roles PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id)
        REFERENCES users (user_id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id)
        REFERENCES roles (role_id) ON DELETE RESTRICT
);

One row per (user, role) pairing. Deleting a user cascades and cleans up their role assignments automatically (ON DELETE CASCADE), but a role that's still assigned to anyone can't be deleted (ON DELETE RESTRICT) — Postgres blocks that delete until the assignments are removed first.

INSERT INTO roles (name)
VALUES ('Customer'), ('Admin')
ON CONFLICT (name) DO NOTHING;

Both accounts share the same password. Only the Argon2id hash is stored in the script — the actual password, and how to log in with it, is covered on the next page.

A single query joins a hand-written list of (username, role_name) pairs against the rows that were just inserted, so the script never has to hardcode numeric ids — it looks up admin → Admin and customer → Customer by name instead.

Primary key (PK)

The column (or columns) that uniquely identify a row. user_roles uses a composite primary key — the pair (user_id, role_id) together, since neither column alone is unique.

Foreign key (FK)

A column that must match a primary key value in another table — it's how Postgres enforces that user_roles.user_id can never point at a user that doesn't exist.

Join / junction table

A table that exists only to connect two other tables in a many-to-many relationship. user_roles and alloy_elements are both examples.

ON DELETE CASCADE vs RESTRICT

What happens to a row when the thing it references is deleted: CASCADE deletes it too; RESTRICT blocks the original delete instead.

ON CONFLICT ... DO NOTHING

Tells Postgres to silently skip an INSERT that would violate a uniqueness rule, instead of raising an error — what makes the seed data safe to load more than once.