ADR: Cross-schema references are intentionally NOT enforced by foreign keys

Cross-schema references are intentionally NOT enforced by foreign keys

Context

The TKT Space backend uses per-domain Postgres schemas, declared in libs/shared/data-access-db/src/lib/schema/*.schema.ts via pgSchema(...):

Postgres schema	Drizzle file	Owns
users	users.schema.ts	users, user_profiles
companies	companies.schema.ts	companies, company_members, company_customers, company_invitations, company_whitelabel_apps, company_subscriptions, subscription_payments
activities	activities.schema.ts	spheres, sphere_audit_log, categories, locations, activities, time_slots, sessions, contributors, extras, partners, links, favorites + their join tables
bookings	bookings.schema.ts	bookings, booking_extras
passes	passes.schema.ts	pass templates, prices, entitlements, customer passes
payments	payments.schema.ts	payment_settings, liqpay, mono_settings, payments
wallet	wallet.schema.ts	wallets, wallet_transactions, refund_requests
notifications	notifications.schema.ts	device_tokens, notifications, messages, message_recipients

Within a single schema, FKs are used freely (e.g. categories.parent_id → categories.id, locations.parent_id → locations.id, sessions.activity_id → activities.activities.id). Across schemas, FKs are deliberately omitted — the referencing column is a bare uuid NOT NULL with no .references() clause.

Examples of cross-schema references without a DB-level FK:

• activities.activities.company_id → conceptually companies.companies.id
• activities.locations.company_id → conceptually companies.companies.id
• activities.categories.company_id → conceptually companies.companies.id (nullable; NULL = platform/system category)
• payments.payment_settings.company_id → conceptually companies.companies.id
• notifications.messages.target_id → polymorphic (user_id or company_id)
• payments.payments.source_id → polymorphic (booking / subscription / wallet topup, depending on source_type)

Decision

Cross-Postgres-schema references are NOT declared as foreign keys in Drizzle schemas. They are bare uuid columns, sometimes NOT NULL, never with .references(...). Integrity is maintained at the application layer (service-level checks before INSERT/UPDATE, plus authorization guards that ensure a request can only see its own company’s data).

Within a single Postgres schema, FKs are used as normal — including onDelete: 'cascade', 'restrict', or 'set null' where appropriate.

Why

1. Schema independence. Each Postgres schema is conceptually a separate bounded context. Cross-schema FKs would couple their migration timelines and operational concerns (e.g. you can’t DROP TABLE companies.companies while activities.activities holds a FK to it, even if the rows are gone). Bare-uuid references keep each schema independently deployable and easier to back up / restore in isolation.
2. Polymorphic refs are unrepresentable as FKs. messages.target_id and payments.source_id cannot be FK-constrained because their target type varies per row. The “no cross-schema FK” rule generalises this: every cross-schema reference behaves like a polymorphic-style soft reference, even when the target is fixed.
3. Soft-archive without cascades. Companies can be soft-archived (or their data redacted for GDPR) without Postgres cascading the deletion through unrelated schemas. The application layer decides whether to surface, hide, or null-out references in each context.

Consequences

• Integrity invariants must be enforced in services. Any service that writes a cross-schema reference is responsible for validating that the target exists, the user has permission to reference it, and (where applicable) the target is not archived. There is no DB safety net — orphan rows are physically possible if a service forgets a check.
• Documentation rule. Every entity that holds a cross-schema reference must record this in its Invariants section with a citation back to this ADR, so the lack of FK is recognised as intentional rather than a doc gap.
• Migration safety. Dropping or renaming a Postgres schema does not require coordinated FK removal across other schemas. Conversely, the application is responsible for keeping references valid during cross-schema changes.
• Polymorphic refs are first-class. Tables with polymorphic references (messages.target_id / payments.source_id) document their *_type discriminator column and the full set of legal *_id targets in their entity doc.

Status

ACCEPTED — this ADR records an existing, in-use architectural decision. It is not a proposal for a change.

Created retroactively while bootstrapping _workflow/docs/content/domain/ (Phase 1 of the docs initiative, 2026-05-21).