One file, one source of truth start here
sql/metals-db.sql does two jobs in one script: it drops and recreates every table this project needs (in dependency order, so foreign keys don't fail), and then it inserts starting data — including the roles, the two development login accounts, and the metals/alloys/coins catalog data the API serves.
Locally, docker-compose.yml mounts this file straight into a fresh PostgreSQL container so it runs automatically the first time the container starts — covered on the next page. In Azure, the same file is loaded by az_create_resources (manual path) or terraform/scripts/initialize_database.py (Terraform path) after the empty database server is created.
The tables, table by table structure
Seven tables in total, split into two groups: the metals catalog data the API was originally built around, and the authentication tables added on top of it.
| Table | What it's for, in plain terms |
|---|---|
| elements | One row per chemical element (atomic number as the primary key) — melting/boiling points, color, density, category, and whether it's toxic or magnetic. |
| alloys | Named mixtures, like “Sterling Silver” or “18K Yellow Gold.” |
| alloy_elements | A join table: which elements make up which alloy, and what percentage of the alloy each one is. |
| coins | Real-world coins, each made from one alloy, with weight and face-value details. |
| users | Login accounts — a username and a password hash (never the real password). (dig deeper) |
| roles | The two permission levels this API knows about: Admin and Customer. (dig deeper) |
| user_roles | A join table connecting users to the role(s) they hold. (dig deeper) |
What gets loaded automatically seed data
After creating the tables, the same script inserts starting rows: the Admin and Customer roles, two ready-to-use login accounts, and a large catalog of real elements, alloys, and coins. Every insert uses ON CONFLICT ... DO NOTHING, so re-running the script against a database that already has this data is harmless.
