FreClean Data

FreClean's production data layer: PostgreSQL schema, a small dependency-light migration runner, a data dictionary, and an ERD.

This is the intended replacement for freclean-api's in-memory demo store (freclean-api/src/data/store.ts) — see that file's own warning comments. freclean-api's CRUD factory pattern was written specifically so this swap changes the data access layer only, not route, validation, or RBAC logic.

Status

Schema defined and CI-verified against a real Postgres instance on every push — not yet wired into freclean-api. This is a deliberate, sequenced next step: get the schema right and provable first, then point freclean-api at it.

Structure

migrations/    Numbered, plain SQL migration files — no ORM
seeds/         Demo data (clearly marked, dev/staging only)
scripts/       migrate.ts (runner), run-sql.ts (seed helper)
docs/          Data dictionary + ERD

Why plain SQL, no ORM

FreClean's schema is explicit and reviewable as SQL — adding an ORM's migration DSL on top would be exactly the kind of unnecessary complexity this ecosystem's engineering principle avoids (see freclean-payment's hand-written Celo client for the same reasoning applied elsewhere). The migration runner (scripts/migrate.ts) is under 60 lines: it tracks applied files in a schema_migrations table and runs each new one inside a transaction.

Getting started

npm install
cp .env.example .env        # point DATABASE_URL at a real Postgres instance
npm run migrate               # applies migrations/*.sql in order
npm run seed:demo             # loads clearly-marked demo data (refuses to run if NODE_ENV=production)

Schema highlights

• Every status field is a Postgres ENUM matching freclean-api's Zod schemas exactly — an invalid status is a schema-level error, not just an application bug.
• Several of FreClean's realism-rule commitments are enforced as database CHECK constraints, not just application code — see docs/DATA-DICTIONARY.md's "integrity rules worth calling out" section. A product cannot be available without a real SKU, and a Celo asset cannot be payment_enabled without a verified contract address, even if application code has a bug.
• audit_log has no foreign keys, by design, so the audit trail survives even if a referenced record is later deleted.

Roadmap for this repo

• Wire freclean-api to read/write through this schema instead of its in-memory store
• Add a rollback/down migration convention (current migrations are forward-only)
• Add row-level security policies matching freclean-api's RBAC model, as defense in depth
• Add a backup/restore runbook — tracked as a launch blocker in freclean-docs/whitepaper/26-disaster-recovery.md

License

Not provided.