Skip to content

Database

FastReact uses PostgreSQL with a multi-tenant schema and Sqitch for migrations. Repositories use raw SQL (no ORM), so you always see exactly what runs.

Multi-tenant schema

All business data is scoped to an organization (the tenant boundary). Core entities:

  • user — individual accounts
  • organization — the tenant; all business data belongs to one
  • role — readonly, member, org_admin, sys_admin (see Authentication)
  • session — server-side sessions
  • plan / organization_plan — subscription tiers and the org's current plan

This supports both individual users and teams without changing the data model. See Multi-Tenancy (B2B) for the organization/invitation model.

Connection config

Connection settings come from backend/.env (FS_DB_URL, FS_DB_SCHEMA) via backend/app/config/settings.py, and are injected through the DI container's db_config into every repository — so repositories never construct their own connections.

Migrations with Sqitch

Migrations are plain SQL files in backend/db/, versioned with Sqitch and reviewed alongside code in pull requests.

Create a migration:

cd backend/db
sqitch add add_feature -n "Add feature table"

This creates three files:

  • deploy/add_feature.sql — how to apply the change
  • revert/add_feature.sql — how to undo it
  • verify/add_feature.sql — how to verify it worked

Deploy:

./sqitch.sh dev deploy

The sqitch.sh wrapper adds FastReact-specific safety: per-environment database URLs (dev/beta/gamma/prod/test), a check that every migration is wrapped in BEGIN;/COMMIT;, revert protection (requires --to), and .env loading per environment.

Why Sqitch and raw SQL?

Sqitch is language-agnostic and works with plain SQL — no ORM lock-in. You review and optimize the exact SQL that runs, and can still adopt SQLAlchemy or another tool later if your project needs it. See Architecture for the repository-layer reasoning.