Safe Hands DB

A relational database for a fictional insurance company: a PostgreSQL schema with real constraints, migrations, seed data, ten business-scenario queries, a test suite, and a live demo that runs the whole database in your browser.

Live demo → (PostgreSQL 18 compiled to WebAssembly; nothing is sent to a server)

About

This started as a university database course project at the Lebanese American University in December 2022: an ER design for "Safe Hands", an insurance company in Lebanon, mapped to 38 Oracle tables, with sample data and ten business scenarios. The original report is kept in this repository.

As submitted, the script did not run: only 17 of its 38 tables could be created (on PostgreSQL; Oracle would manage one more), four scenario queries did not parse or referenced missing columns, and others returned wrong answers, two of them visibly in the report's own screenshots. This repository is the same design rebuilt properly. Every entity, relationship and scenario is kept, and each of the 49 defects found along the way is logged with its evidence in docs/errata.md; most are also pinned by a test.

Features

• Schema that enforces the rules. 31 tables and 2 views with primary, foreign, unique and check constraints; explicit ON DELETE behaviour; an index on every foreign key; money as numeric, not float.
• Every relationship stored once. The original kept six duplicate relationship tables that disagreed with the foreign keys; derived values (age, head counts, amount due) are now views and generated columns.
• Versioned migrations with dbmate, each with a tested rollback.
• Ten business scenarios as plain SQL files, from "find the robbery suspects" to "retire maintenance staff at 60", each documenting what changed from the 2022 version.
• Self-documenting. Every table and column carries a database comment with its original name; docs/schema.md and the diagram below are generated from them, and CI fails if they go stale.
• Tested on two engines. The suite runs on an in-process PostgreSQL (PGlite) with zero setup, and on a PostgreSQL 18.3 server in CI.
• One command to run locally (docker compose up), and a free, static live demo.

Schema

The core model: entities and the tables that link them. The 13 tables holding multivalued attributes (phones, emails, addresses, colours, currencies, ...) and all columns are in docs/schema.md.

erDiagram
    employee ||--o| department : "manager_id"
    department ||--o{ employee : "dept_no"
    employee |o--o{ employee : "supervisor_id"
    employee ||--o{ dependent : "employee_id"
    customer ||--o{ serves : "customer_id"
    employee ||--o{ serves : "employee_id"
    customer ||--o{ deals_with : "customer_id"
    department ||--o{ deals_with : "dept_no"
    customer ||--o{ calls_for_emergency : "customer_id"
    department ||--o{ calls_for_emergency : "dept_no"
    customer ||--o| medical_record : "customer_id"
    insurance |o--o{ vehicle : "insurance_id"
    customer ||--o{ vehicle : "owner_id"
    insurance |o--o{ house : "insurance_id"
    customer ||--o{ house : "owner_id"
    customer ||--o| health_report : "customer_id"
    insurance ||--o{ health_report : "insurance_id"
    customer ||--o{ payment : "customer_id"
    insurance ||--o{ payment_policy : "insurance_id"
    payment ||--o{ payment_policy : "receipt_no"
    department ||--o{ pays : "dept_no"
    expense ||--o{ pays : "expense_receipt_no"
    contracted_company ||--o{ transacts : "company_name"
    department ||--o{ transacts : "dept_no"

Loading

Quick start

With Docker (PostgreSQL 18.3, migrations and seed data):

git clone https://github.com/malfallah/Database-Project.git safe-hands-db
cd safe-hands-db
docker compose up

Then connect with any client on localhost:5432 (user safehands, password safehands-local, database safe_hands; change them in .env, see .env.example):

docker compose exec db psql -U safehands -d safe_hands

Without Docker (Node.js 22+; uses PGlite, an in-process PostgreSQL):

npm ci
npm test                    # the full test suite
npm run scenario -- list    # list the ten scenarios
npm run scenario -- 4       # run one
npm run serve:site          # the demo, at http://localhost:8080

Usage

Each scenario is a story from the report plus a query. Scenario 4, "Robbery in the Finance department":

-- Only Finance staff had a key, the manager has an alibi, and the thief is male.
select e.employee_id, e.first_name, e.last_name, e.role
from employee as e
inner join department as d on e.dept_no = d.dept_no
where d.name = 'Finance'
  and e.gender = 'Male'
  and e.employee_id <> d.manager_id
order by e.employee_id;

$ npm run scenario -- 4
...
┌─────────┬─────────────┬────────────┬───────────┬────────────┐
│ (index) │ employee_id │ first_name │ last_name │ role       │
├─────────┼─────────────┼────────────┼───────────┼────────────┤
│ 0       │ 202147      │ 'Mohammad' │ 'Temsah'  │ 'Security' │
│ 1       │ 202183      │ 'Elias'    │ 'Shehab'  │ 'Advisor'  │
└─────────┴─────────────┴────────────┴───────────┴────────────┘
2 row(s).

The same two suspects as the report's screenshot, but the original needed four UNIONed copies of this query, three of which referenced columns that do not exist.

Write scenarios use PostgreSQL 18's RETURNING old.*, new.* to show exactly what they changed, and the constraints still apply. Scenario 3 cuts the pay of everyone who served an unhappy customer, and if a cut would take a salary below the 5000 minimum, the whole statement fails and nobody's pay changes.

#	Scenario	Kind	Original problem
1	Rami is in search of an old house	read	ignored insurance, off-by-one year, an unasked city filter
2	Explosion in the Health department	write	two statements joined by AND (syntax error), not atomic
3	Punishment	write	none (restyled)
4	Robbery in the Finance department	read	non-existent columns, needless UNIONs
5	Tragedy is happening	write	uncorrelated EXISTS: raised people with no dependents
6	All customers should be satisfied	read	NOT EXISTS filter that filtered nothing
7	Mother's Day	read	uncorrelated EXISTS: every woman counted as a mother
8	New fatal disease	read	none (restyled)
9	Happy anniversary	read	joined on a column the DDL never created
10	New policy	write	UPDATE inside DELETE ... WHERE; would delete from every department

Architecture

There is one source of truth for the schema, the SQL files in db/migrations/, and three places that apply it:

db/migrations/*.sql ─┬─ dbmate ─────────────► PostgreSQL 18.3   docker compose up / CI
db/seed.sql          ├─ src/database.js ────► PGlite or server   npm test, npm run scenario
queries/*.sql        └─ scripts/build-site ─► PGlite snapshot    GitHub Pages demo

• src/sql-files.js parses migrations and scenario files; the tests and the demo share it.
• src/database.js opens either PGlite (default) or a server (DATABASE_URL), applies migrations into dbmate's schema_migrations table, and loads the seed.
• scripts/build-site.js builds the database once with PGlite in Node and ships its data directory, so the demo skips initdb (10-20 s in WebAssembly) and starts in a few seconds.

The reasoning behind these and other choices (PostgreSQL over Oracle, deferred constraints, keys, delete rules, the demo's trade-offs) is in docs/design-decisions.md.

Project layout

.
├── db/
│   ├── migrations/        schema, views, and table/column documentation (dbmate)
│   └── seed.sql           fictional sample data
├── queries/               the ten business scenarios
├── src/                   database access, migration runner, scenario CLI
├── scripts/               docs generator, demo build, local static server
├── tests/                 schema, seed, scenario and docs tests
│   └── original/          reproductions of the 2022 defects
├── web/                   the in-browser demo
├── docs/
│   ├── schema.md          generated schema reference
│   ├── errata.md          every defect found, with evidence
│   ├── design-decisions.md
│   └── original/          the 2022 report, ER diagram and transcribed Oracle SQL
├── docker-compose.yml
└── .github/workflows/     CI and Pages deployment

Testing

npm test                 # PGlite, no setup
DATABASE_URL=postgres://user:pass@localhost:5432/postgres npm test   # a real server
npm run lint             # Biome (JS) and SQLFluff (SQL)

• Constraints: every rule is exercised with bad data and must fail with the right SQLSTATE (check, unique, foreign key, not null); deferred and cascading behaviour is tested both ways.
• Catalog: every table has a primary key, every foreign key is indexed, no floating-point columns, every object documented.
• Migrations: apply, roll everything back, re-apply.
• Scenarios: exact results on the seed data, plus edge cases.
• Original defects (tests/original/): the 2022 SQL is run and must misbehave exactly as docs/errata.md says, matching the report's screenshots where it has them.

CI runs all of this on PGlite and on a PostgreSQL 18.3 service container, checks the dbmate migrations up and down, and smoke-tests docker compose up end to end.

Roadmap

Ideas that would take the project further, roughly in order of value:

• Model what the original design could not express: several hospital stays per customer (the 1:1 CHECKS relationship), customer birth dates instead of a stored age, and the unmapped WORKS_FOR.Time attribute.
• Claims as a first-class entity (an insurer's core workflow, absent from the 2022 design).
• Row-level security so each department sees only its own customers.
• Larger generated data sets with EXPLAIN ANALYZE comparisons for the scenario queries.
• Property-based tests that generate random valid and invalid rows against the constraints.

Credits

Original design and report (2022): Bilal Delbani, Omar Temsah, Mohammad Al Fallah and Mohammad Alizzi, Lebanese American University. Rebuild (2026): Mohammad Al Fallah.

Built with PostgreSQL, PGlite, dbmate, node-postgres, Mermaid, Biome and SQLFluff. See NOTICE.

All people and companies in the data are fictional.

License

MIT. The original report in docs/original/ is the joint work of its four authors; see NOTICE. To cite this project, see CITATION.cff.