ADR-002: Table Prefix over Separate Schemas
Status
Accepted
Context
In our modular monolith, each service owns its database tables. We needed to decide how to separate tables between services:
- Separate Schemas:
auth.users,org.organizations,rx.prescriptions - Table Prefix:
identity_users,org_organizations,rx_prescriptions
Decision
We will use table prefixes (e.g., auth_users, org_organizations) rather than separate PostgreSQL schemas.
Rationale
Why Table Prefix?
- Simpler migration management - All tables in one schema, easier to manage with Goose
- Cross-service queries - No schema qualification needed for joins
- Tool compatibility - Works seamlessly with all SQL tools and ORMs
- Extraction path - Easy to dump tables with prefix when extracting to microservice
- No GRANT management - Simpler permissions (single schema)
Why Not Separate Schemas?
| Aspect | Separate Schemas | Table Prefix |
|---|---|---|
| Isolation | Better (schema-level) | Good (naming convention) |
| Migrations | More complex | Simpler |
| Cross-service queries | Need schema qualification | Direct |
| Permissions | Schema-level GRANTs | Database-level |
| Extraction | Move schema | Dump by prefix |
Naming Convention
| Prefix | Service | Example Tables |
|---|---|---|
auth_ | Identity | auth_users, auth_sessions |
org_ | Organization | org_organizations, org_members, org_teams |
rx_ | Prescription | rx_prescriptions, rx_prescription_items |
subscription_ | Subscription | Planned: subscription_plans, subscription_subscriptions, subscription_invoices, subscription_payments |
admin_ | Admin | admin_users |
authz_ | Authorization | authz_roles, authz_permissions, authz_role_assignments |
notification_ | Notification | notification_notifications, notification_templates |
Doctor profiles belong to the doctor service under ADR-013, not identity. Its persistence is deferred; this ADR does not assign a doctor table name or create any of the planned billing tables. Admin no longer owns tenant projections (ADR-012). Existing identity migrations use the auth_ prefix.
Consequences
Positive
- Simpler database management
- Easier cross-service queries during monolith phase
- Straightforward extraction path
Negative
- Less isolation than separate schemas
- Naming convention must be enforced
Related Decisions
ADR-001: Modular Monolith Architecture