Database Strategy

Phase: 1 Architecture Plan Status: pre-implementation contract

Goals

• Split the legacy single Postgres schema into service-owned Postgres databases.
• Preserve public IDs and behavior during migration.
• Keep migrations deterministic and reviewable.
• Avoid distributed transactions.
• Make parity measurable against the legacy database before cutover.

Go Data Stack

Use:

• pgxpool for connection pooling.
• sqlc for typed query generation.
• service-local SQL under services/<service>/migrations applied by tools/migrator using schema-up --service=<service>.
• Context-aware methods for every DB call.
• Service-local transaction helpers.

Avoid:

• global DB handles outside service startup composition.
• ORM models shared between services.
• implicit tenant filters hidden in generic repository code.
• schema migrations that read legacy data or write another service database.

Schema Design Defaults

• id as stable public identifier.
• legacy_id nullable but unique for migrated rows.
• organization_id on tenant-owned entities.
• created_at, updated_at, and deleted_at where legacy behavior uses soft delete/archive.
• JSONB for compatibility snapshots.
• explicit status enums represented as text plus check constraints unless a service has a strong reason for Postgres enum.

Tenant Filtering

Tenant filtering must be explicit in SQL and service APIs. Every tenant-scoped query accepts organization_id or a policy object derived from gateway auth context.

Admin all-organization access must be visible in code as a separate branch or policy, not a missing filter.

Outbox And Events

For services that publish events:

• Write domain change and outbox event in the same local transaction.
• A worker dispatches outbox records to NATS or Redis Streams.
• Consumers are idempotent by event ID.
• Event payloads include producer service, schema version, aggregate ID, organization ID when applicable, and correlation ID.

Initial required event families:

• document.media.created
• docx_import.job.updated
• docx_import.job.completed
• question.created
• question.updated
• exam.published
• exam.assigned
• attempt.started
• attempt.answer_saved
• attempt.submitted
• notification.requested

Backfill Tables

Each service may maintain a migration bookkeeping table:

create table migration_runs (
  id text primary key,
  source text not null,
  started_at timestamptz not null,
  finished_at timestamptz,
  status text not null,
  source_high_watermark text,
  stats jsonb not null default '{}'::jsonb
);

For row-level traceability:

create table legacy_id_map (
  legacy_table text not null,
  legacy_id text not null,
  target_id text not null,
  created_at timestamptz not null default now(),
  primary key (legacy_table, legacy_id)
);

Services can adjust the exact DDL, but the traceability requirement is mandatory.

Schema migration state is separate from backfill traceability. The shared schema runner records applied files in each target database with schema_migrations(service, filename, checksum, applied_at). Backfill reports and loaders must keep their own migration_runs/legacy_id_map evidence and must not run inside schema migration commands.

Query Review

Before cutover of any route:

• confirm tenant filters
• confirm pagination caps
• confirm ordering compatibility
• confirm soft-delete/archive handling
• confirm JSON serialization matches contract examples
• run parity queries against a legacy snapshot

Backup And Restore

Per-service database backup is required before cutover. Restore tests must be done on staging before production writes are enabled for migrated routes.

Rollback is route-level first, database restore second. Database restore should be rare because gateway can route traffic back to legacy while migrated DB remains frozen for diagnosis.