Database Per Service Strategy

Phase: 1 Architecture Plan Status: pre-implementation contract

Principle

The target platform uses database-per-service. The legacy Prisma database is the source for audit, migration, and parity checks, but it is not a shared runtime database for new Go services.

Database Naming

Service	Database	Initial Migration Mode
iam-service	hoctapaz_iam_db	Backfill users' auth material and refresh tokens after contract lock
profile-service	hoctapaz_profile_db	Backfill profiles and KYC after auth identity mapping
school-service	hoctapaz_school_db	Backfill organizations and IAM-synchronized membership read models
classroom-service	hoctapaz_classroom_db	Backfill after school IDs are mapped
course-service	hoctapaz_course_db	Backfill after document/question references are stable
document-service	hoctapaz_document_db	Early backfill for media assets and teaching documents
docx-import-service	hoctapaz_import_db	Early native job schema; legacy import rows read-only for parity
ai-classifier-service	hoctapaz_ai_classifier_db	Backfill jobs/provider settings after question IDs are mapped
question-bank-service	hoctapaz_question_bank_db	Backfill question types, taxonomy, questions, versions, options
exam-service	hoctapaz_exam_db	Backfill exams/templates/snapshots after questions are mapped
attempt-service	hoctapaz_attempt_db	Backfill only after exam snapshot contract is frozen
notification-service	hoctapaz_notification_db	Rebuild from canonical events where possible
analytics-service	hoctapaz_analytics_db	Rebuild read models from event/backfill jobs
admin-service	hoctapaz_admin_db	Backfill feature maintenance, audit logs, support tickets
payment-service	hoctapaz_payment_db	Backfill/reconcile provider payment orders and idempotency records
wallet-service	hoctapaz_wallet_db	Backfill AZ Credit ledger only after billing/payment event policy is locked
billing-service	hoctapaz_billing_db	Backfill plans, subscriptions, invoices, and entitlement snapshots
usage-service	hoctapaz_usage_db	Rebuild quotas, feature gates, and usage counters from billing/import events
search-service	hoctapaz_search_db	Rebuild copied indexes from owner-service projections
audit-service	hoctapaz_audit_db	Backfill append-only security/compliance event history

Shared Reference Data

Taxonomy tables such as Subject, Chapter, Topic, EducationLevel, Grade, Curriculum, ExamTrack, QuestionSource, and DifficultyLevel are shared in legacy. Initial owner is question-bank-service unless later product analysis moves school-specific grade/curriculum references into school-service.

Services that need taxonomy use:

• local replicated read model, or
• query API from question-bank-service.

They must not join the question-bank database directly.

ID Strategy

• Preserve legacy IDs in migrated records as legacy_id.
• Native primary keys may remain UUID/string-compatible if existing frontend contracts expose IDs.
• Every migrated table needs a unique index on legacy_id when legacy data is backfilled.
• Cross-service references use the target service's public ID plus optional legacy_id for validation reports.

Migrations

Each service owns:

• migrations/ SQL consumed by tools/migrator with schema-up --service=<service>.
• queries/ using sqlc.
• internal/store or equivalent storage adapter.

Migration rules:

• One service migration cannot create tables in another service database.
• No migration reads the legacy database directly.
• Backfill jobs live outside schema migration commands.
• Applied schema files are tracked in each service database with schema_migrations(service, filename, checksum, applied_at).
• Schema migrations must be forward-only in CI; rollback scripts may exist for local and emergency recovery.
• make test-compose-migrations must stay green after runtime changes; it verifies every active service has a local Compose runtime entry and that DB-backed services use service-owned runtime and migration DSNs.
• Compose initialization revokes PostgreSQL CONNECT and TEMPORARY from PUBLIC for every service database before granting the owner role access. make test-postgres-database-isolation starts an independently named, portless PostgreSQL container, proves all 20 owner roles can connect to their own database, checks all 380 cross-service CONNECT pairs are denied, and verifies an actual denied connection for every role. It removes only that test container when complete.

Transactions

• Transactions stay inside one service database.
• Cross-service workflow uses events and idempotency keys.
• Gateway adapters must not wrap multiple services in a fake distributed transaction.
• Import approval must be designed as a saga:
  1. lock import job/draft
  2. create questions or exam draft through owning services
  3. write approval result
  4. publish events
  5. compensate only with explicit domain actions, not hidden deletes

JSON Snapshot Fields

Keep JSON snapshot fields where they preserve product behavior:

• question rich content
• question scoring rules
• exam question snapshots
• attempt question snapshots
• import parse results and warnings
• provider/raw OCR metadata where needed for troubleshooting

Do not normalize snapshots away in Phase 2. That would change exam/attempt behavior.

Legacy Read Adapters

Allowed:

• offline inventory scripts
• parity tests
• backfill jobs
• audit reports

Not allowed:

• native service write path depending on legacy DB joins
• silent runtime fallback to legacy DB after a route has been declared migrated