Pharos Framework

← Back

Data Layer And Migrations

Pharos ships with SQLite as the default development and proving backend and PostgreSQL as the preferred production target.

Data layer and migration scope

Pharos provides a framework-owned relational floor for CRUD apps by adding:

  • app-owned entity/query/migration contracts
  • checked-in SQLite and PostgreSQL schema.sql, security.sql, and seed.sql bundles
  • pharos migrate plan ...
  • pharos migrate status ...
  • pharos migrate seed ...
  • pharos migrate verify ...
  • pharos migrate apply ...
  • pharos migrate rollback ...
  • pharos db seed ...
  • framework-owned relational runtime export/import for contract-backed apps

UCAL is the first proving app because it already has:

  • embedded user/account state
  • booking and appointment data
  • business/service/technician relationships
  • the start of auth, verification, and idempotency concepts

Backend Policy

  • production-preferred: PostgreSQL
  • development/testing first: SQLite

That policy is encoded in the UCAL app manifest and migration fixture.

New Commands

pharos migrate plan app ucal --backend sqlite --text
pharos migrate status app ucal --backend sqlite --text
pharos migrate seed app ucal --backend sqlite --text
pharos migrate verify app ucal --backend sqlite --text
pharos migrate apply app ucal --backend sqlite --text
pharos migrate rollback app ucal --backend sqlite --text
pharos db seed app ucal --backend sqlite --text

pharos migrate plan app ucal --backend postgresql --text
pharos migrate status app ucal --backend postgresql --text
pharos migrate seed app ucal --backend postgresql --text
pharos migrate apply app ucal --backend postgresql --text
pharos migrate verify app ucal --backend postgresql --text
pharos db seed app ucal --backend postgresql --text

What plan Does

plan assembles the checked-in schema/security bundle set for the selected backend into the build output tree and emits a machine-readable report.

For UCAL, that currently means:

  • identity tables
  • session and email-verification tables
  • booking and appointment tables
  • business/service/technician tables
  • idempotency and audit tables

What seed Does

seed assembles the checked-in seed SQL for the selected backend into the build output tree and emits a report that points back to the source live-state JSON used to define the bundle.

What apply Does

apply supports both SQLite and PostgreSQL. It:

  • assembles the checked-in schema.sql and security.sql bundles
  • assembles the checked-in seed.sql bundle
  • checks the migration ledger before making changes
  • applies only unapplied forward migrations
  • bootstraps seed SQL only when creating an empty managed database
  • refuses to run destructively against unmanaged or drifted databases

For SQLite, the target is the selected database file.

For PostgreSQL, the target is the selected configured database and the same forward-only ledger rules apply.

What rollback Does

rollback reports the safe rollback posture for the selected backend. For forward-only migrations it refuses destructive rollback by default and returns a machine-readable report that names the latest applied migration and the active rollback policy.

What db seed Does

db seed supports SQLite and PostgreSQL. It:

  • assembles the checked-in seed bundle
  • requires an existing managed schema database
  • keeps seed execution framework-owned while still using app-owned seed bundles
  • refuses to reseed non-empty runtime databases when bootstrap-only seed semantics would be unsafe

What verify Does

verify has live verification paths for both SQLite and PostgreSQL. It checks:

  • the target database exists
  • required tables exist
  • the pharos_schema_migration ledger contains required migration ids
  • applied migration checksums match the checked-in contract set
  • seed row-count sanity matches the app fixture's verification_contract

The verification rules are declared by the app migration fixture, but the command implementation lives in the shared Pharos framework runtime.

What status Does

status reports:

  • selected backend
  • preferred/development/testing backend policy
  • schema version
  • source fixture paths
  • schema.sql and security.sql bundle existence
  • seed.sql bundle existence
  • JSON seed source paths

UCAL Relational Runtime Bridge

UCAL proves the framework-owned relational runtime bridge.

The shared Pharos runtime exports relational state into the app host's expected state shape before execution and writes the resulting state back into the database after successful mutation work. The maintenance path uses the same bridge and writes back through transactional import, so the relational database remains the source of truth while the compiled host keeps its current request logic.

Validation Summary

Validation covers:

  • SQLite bootstrap apply
  • SQLite repeat apply with zero pending migrations
  • SQLite live verify
  • SQLite safe rollback refusal
  • SQLite drift detection on changed migration bundles
  • SQLite maintenance-path relational writeback
  • PostgreSQL live apply through Docker-backed psql
  • PostgreSQL live verify through Docker-backed psql