Database Schema Ownership

Attest ID uses a multi-schema PostgreSQL database with per-service schema ownership: each backend service owns its schema outright, migrates it independently, and no other service has a database grant on it.

Schema map


PostgreSQL: attest_pro
├── authority   (Issuer Registry)      — issuers, did_registry, signing_keys, issuer_sso_config
├── wallet      (Credential Issuance)  — credentials
├── core        (Core Engine)          — audit_logs, holder/issuer-portal/verifier auth, requests,
│                                        notifications, share_links, llm_analysis_attempts
├── console     (Admin Console)        — admin_users, admin_audit_logs, system_config, dashboard_widgets
├── catalog     (Program Catalog)      — jurisdiction, qualification_framework, catalog_entry(_version),
│                                        code_value, regulatory_scheme, scheme_field_map, issuer_scheme_registration
├── learner     (Learner Records)      — learner, learner_identifier, enrolment, outcome
└── custodian   (Custodian Registry)   — custodians, did_registry, signing_keys, custodian_sso_config

Master data (catalog, learner) is deliberately split from operational data (authority, wallet, core, console, custodian) by regulatory sensitivity, not by feature — see Master Data Registry §3.

Service-to-schema mapping

Service	REST	gRPC	DB user	Schema	Owns
Issuer Registry	8083	50053	`issuer_registry_user`	`authority`	issuers, did_registry, signing_keys, issuer_sso_config
Credential Issuance	8081	50051	`credential_issuance_user`	`wallet`	credentials
Verification Engine	8082	50052	(stateless)	N/A	none — read-only via gRPC
Core Engine	8080	N/A	`core_engine_user`	`core`	audit_logs, llm_analysis_attempts, portal/holder/verifier tables, requests, notifications, share_links
Admin Console	—	N/A	`admin_service_user`	`console`	admin_users, admin_audit_logs, system_config, dashboard_widgets
API Gateway	8000	N/A	(proxy)	N/A	none — stateless
Program Catalog	8085	50054	`program_catalog_user`	`catalog`	jurisdiction, framework, catalog_entry(_version), code_value, regulatory_scheme, issuer_scheme_registration
Learner Records	8086	50055	`learner_records_user`	`learner`	learner, learner_identifier, enrolment, outcome
Custodian Registry	8087	50056	`custodian_registry_user`	`custodian`	custodians, did_registry, signing_keys, custodian_sso_config

Users and permissions

Each service authenticates as its own login role, which owns its schema outright — created by deployment/scripts/postgres-app-users.sh, run once via /docker-entrypoint-initdb.d/ on first Postgres init. No service authenticates as the bootstrap superuser at runtime.


attestid_admin (bootstrap superuser — POSTGRES_USER; ops/migration/psql only, never a
                runtime credential)
├── issuer_registry_user      — owns authority
├── credential_issuance_user  — owns wallet
├── core_engine_user          — owns core
├── admin_service_user        — owns console
├── program_catalog_user      — owns catalog
├── learner_records_user      — owns learner
├── custodian_registry_user   — owns custodian
└── analytics_user (read-only)
    └── SELECT on authority.*, wallet.*, core.*, console.*, catalog.*, learner.*, custodian.*

Each service role owns its schema, so it has full CREATE/ALTER/DROP/CRUD inside it with no separate GRANTs — Flyway’s CREATE SCHEMA IF NOT EXISTS in each V1__schema.sql is a no-op against a schema the init script already created and assigned. analytics_user gets SELECT-only across every schema (including tables created later, via ALTER DEFAULT PRIVILEGES); it can never INSERT/UPDATE/DELETE or modify schema.

Flyway migrations

Every service follows the same two-file convention — no service/schema prefix, since the file already lives in that service’s own migration directory:


backends/<service>/src/main/resources/db/migration/
├── V1__schema.sql       (DDL for that service's schema)
└── V2__seed_data.sql    (seed/demo data — omitted where none exists, e.g. Admin Console)


# example: issuer-registry/application.yml
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    schemas: authority
    baseline-on-migrate: true
    validate-on-migrate: true

Services start independently — no migration ordering required.
Each service migrates only its own schema; a failed migration in one doesn’t affect others.

Dev-mode convention (pre-launch, no real data yet): every service’s schema/seed files are single living migrations, edited in place — a change that would normally be V3__... is folded directly into V1__schema.sql (DDL) or V2__seed_data.sql (data) instead. Once an environment holds real data that must survive an upgrade, switch to incremental V3__, V4__, … files.

Cross-schema references

Schemas are owned by different services, so foreign keys across them are soft — a comment, not a DB constraint:


-- wallet.credentials references authority.issuers
CREATE TABLE wallet.credentials (
    credential_id UUID PRIMARY KEY,
    issuer_id UUID NOT NULL,  -- FK: authority.issuers(issuer_id)
    credential_json JSONB NOT NULL,
    created_at TIMESTAMP
);

Soft FK trade-off
✅ Schemas migrate independently	✅ Services scale separately
✅ No cross-schema locks during queries	❌ Referential integrity enforced in code, via gRPC, not by Postgres

Integrity is validated in application code — e.g. Credential Issuance calls Issuer Registry over gRPC to confirm an issuer exists before writing a credential row, rather than relying on a database constraint it structurally cannot have across schemas.

No message bus/event queue exists in this codebase — every cross-service interaction is synchronous gRPC. The durable audit trail (core.audit_logs, written synchronously by AuditService in the same request) already covers “what happened and when” without one; adopt an event bus only if a real eventual-consistency requirement emerges.

Backup & recovery


# Per-schema backup
pg_dump -h localhost -U attestid_admin -d attest_pro -n authority > authority_backup.sql
 
# Full database
pg_dump -h localhost -U attestid_admin -d attest_pro > full_backup.sql
 
# Restore
psql -h localhost -U attestid_admin -d attest_pro < authority_backup.sql

A verified backup/restore drill has not yet been performed — it is one of the explicit launch-blocking items in CLAUDE.md’s Roadmap Context.

Troubleshooting


# Confirm a service's DB user and grants
psql -U attestid_admin -d attest_pro -c "\du"
psql -U attestid_admin -d attest_pro -c "SELECT * FROM information_schema.table_privileges WHERE grantee='issuer_registry_user';"
 
# Flyway history for a schema
psql -U attestid_admin -d attest_pro -c "SELECT * FROM authority.flyway_schema_history;"
 
# List tables in a schema
psql -U attestid_admin -d attest_pro -c "\dt authority.*"