Ekklesia/docs/architecture/decisions/0016-module-database-access.md

0016: Module database access

• Status: Accepted
• Date: 2026-07-25
• Extends: 0006 and 0015

Context

ADR 0007 and ADR 0008 already let a module own a Drizzle schema and a validated, checksummed migration; ADR 0009 already applies those migrations transactionally. ADR 0015 let a module mount permissioned HTTP routes and receive an authorized principal. None of these gave a module's executable code — a start/stop lifecycle hook or a route handler — any supported way to actually query or write the tables its own schema declares. A module wanting to persist or read product data had no platform-sanctioned path to PostgreSQL at all.

The platform already owns exactly one physical PostgreSQL pool per API runtime (apps/api/src/database.ts), wrapped in typed Drizzle views for Core's own identity and authorization schemas (createIdentityDrizzleView, createAuthorizationDrizzleView). Handing a module that raw pool, a connection string, or an independently constructed Drizzle instance would either leak platform connection infrastructure into module code or require a second, competing pool — both rejected by the same reasoning ADR 0006 and ADR 0015 already applied to capability registration and HTTP routing. This ADR records the third typed view over that one pool, and the runtime-services boundary that delivers it to trusted module code without exposing the pool itself.

Decision

ModuleDatabase: a restricted, typed view over NodePgDatabase

packages/contracts/src/module-database.ts defines ModuleDatabase as Pick<NodePgDatabase, "select" | "selectDistinct" | "selectDistinctOn" | "insert" | "update" | "delete" | "execute" | "transaction"> — derived from Drizzle's own node-postgres database type with Pick, not hand-declared, so every method keeps Drizzle's own table-aware argument and result inference exactly as-is. A module calling database.select().from(table) or database.insert(table).values(...) gets the identical typed builder a direct Drizzle caller would; no any or unknown erasure exists anywhere in the public type. ModuleDatabase carries no schema generic: a module brings its own Drizzle table objects to each call, so there is no global relational-query object spanning every installed module's tables for this type to expose.

The type excludes everything else NodePgDatabase or the drizzle(...) factory otherwise carries: $client (the raw pg client/pool), _ (internal session/dialect/schema state), query (a schema-bound relational query builder), $cache, $count, $with, with, and refreshMaterializedView. There is no connection string, pool, session, or dialect object reachable through the type.

The facade physically closes the same gaps at runtime, not only in the type system

core/src/modules/database.ts's createModuleDatabaseView(pool) does not return the drizzle(pool) instance directly. Drizzle's own query methods are plain prototype methods that read this.session/this.dialect internally, not arrow-function instance properties, so handing that instance to a module directly would both break the moment a module destructured a method off it (const { select } = database loses this) and — more importantly — would leave every non-public property physically reachable on the object at runtime, reachable to a module written in plain JavaScript or one that bypasses the ModuleDatabase type with a cast, not merely hidden by TypeScript. createModuleDatabaseView instead builds a fresh, Object.freezed object with each of the eight operations explicitly bound to the underlying Drizzle instance: only those eight exist on the object at all, each correctly bound, and nothing else does. Building this view performs no query and no migration — drizzle(pool) only wraps the given client, synchronously and without I/O, the same construction createIdentityDrizzleView/createAuthorizationDrizzleView already rely on. Core never constructs a pg.Pool itself; apps/api/src/database.ts remains the one place that does, and createModuleDatabaseView wraps the pool it is given.

ModuleRuntimeServices is the one typed boundary carrying it into trusted module code

ModuleRuntimeServices (packages/contracts/src/module-database.ts) currently contains exactly one field: database: ModuleDatabase. It is frozen, and it is never handed to a module's register hook — ModuleRegistrationContext (ADR 0006, extended by ADR 0015 with http) gains no services field at all, so registration remains exactly as declarative and database-free as originally decided. ModuleRuntimeContext (the shared start/stop context) and ModuleRouteHandlerContext (ADR 0015's authorized-handler context, now exactly moduleId, principal, params, services) both gain a services field instead. For a given API runtime, start, stop, and every authorized HTTP handler receive the same ModuleRuntimeServices object — the same object identity, not a fresh one built per hook or per request — so a module never needs to stash a database reference in module-global state to share it between lifecycle code and its own route handlers; the platform simply provides it directly, every time, to every authorized call site.

One pool, one facade, one services object, per API runtime

apps/api/src/server.ts's startApi composes in a fixed order: discover the module inventory once and compose the permission registry from it; construct the one runtime pg.Pool (createApiDatabase, wrapping identity, authorization, and module views over it); construct the frozen ModuleRuntimeServices from that same pool's module view; construct Identity, the authorization store, and the evaluator; start the already-discovered module runtime, passing it that services object; create the Hono application and mount the started runtime's routes, passing the identical services object; bind the HTTP server. startDiscoveredModuleRuntime threads the same services object into ModuleRuntimeContext for every module's start and stop; mountModuleHttpRoutes threads the identical object into every authorized handler's ModuleRouteHandlerContext. No second pool, no second facade, and no second services object is ever constructed for one running API. A services/Identity/Authorization construction failure closes the one pool and starts no module; a module-runtime failure closes the pool without binding a server; an app-construction or bind failure stops the module runtime, then closes the pool — matching the cleanup-ordering discipline ADR 0015 already established, now additionally covering the services object's own dependencies.

Shutdown remains drain → stop → close, now load-bearing for in-flight database use

Normal shutdown is unchanged from ADR 0015: drain and close the HTTP listener first, so no new or in-flight request can reach a module handler while the module runtime is stopping; stop the module runtime next — a module's stop hook may still use services.database, which is safe because the pool has not yet closed; only then close the shared pool, so no module code can run against it afterward. This ordering was already correct under ADR 0015; it is now additionally load-bearing, because a stop hook doing real database work depends on the pool still being open when it runs. Shutdown remains memoized — one shared promise every concurrent or later caller awaits — and a module-stop failure still does not prevent pool closure, though (unlike a startup-cleanup path) it is not swallowed: it remains a genuine result a caller observes.

Module schemas and migrations are unchanged; the facade is not a migration tool

A module continues to own and import its own Drizzle table definitions (ADR 0007) and its own generated, checksummed migration (ADR 0008), applied transactionally and explicitly (ADR 0009). ModuleDatabase exposes no migration operation, and nothing in the module runtime infers a schema, creates a table automatically, or runs a migration as a side effect of module discovery, registration, start, or an HTTP request. A module's schema and migration changes still follow the existing generation, validation, policy, and deployment flow unchanged; they must be applied, exactly as before, before starting module code that depends on the new shape.

Trust and security boundary

Modules remain trusted, compiled backend plugins — the same trust model ADR 0006 established for capability registration and ADR 0015 extended to HTTP handlers. ModuleDatabase is a deliberately restricted integration boundary, not a security sandbox: it exists to keep a module's code Drizzle-typed and decoupled from how the platform physically connects to PostgreSQL, not to defend against a malicious module, which already runs arbitrary code in this process and could reach PostgreSQL through other means regardless of what this facade does or does not expose. It provides no tenant isolation, no PostgreSQL-role-level separation between modules, and no runtime enforcement that one module's code only ever queries its own tables — table ownership is an architectural and migration-policy convention (ADR 0007's naming/prefix rules), not something this facade inspects or restricts at the SQL level; execute in particular lets a trusted module issue arbitrary SQL, exactly as select/insert/update/delete/transaction already let it construct arbitrary queries against any table object it imports. Authorization still fully protects the HTTP path into that trusted code: requirePermission and, for state-changing methods, requireTrustedOrigin (ADR 0013, ADR 0015) complete before a handler — and therefore before services.database is ever reachable — runs. A handler-thrown database error, caught or not, remains sanitized by the existing module-handler boundary (ADR 0015): an uncaught failure becomes exactly 500 {"code":"MODULE_HANDLER_FAILED"} with Cache-Control: no-store and no SQL, PostgreSQL message or code, table/constraint name, stack, connection detail, or filesystem path; this ADR introduces no second, database-specific public error contract.

Consequences

Positive

• A module can persist and query its own product data with full Drizzle type inference, without ever owning a connection, a pool, or connection configuration.
• The platform still owns exactly one physical pool and one shutdown lifecycle per API runtime; adding module database access did not require a second one.
• Lifecycle hooks and HTTP handlers share one consistent services boundary (ModuleRuntimeServices), extensible the same deliberate way in the future.
• Registration remains declarative and side-effect-free, preserving every existing discovery, Doctor, and modules:validate guarantee (none of them constructs the facade or executes module code).

Tradeoffs

• Adding a new Drizzle operation to the module-facing surface requires deliberately extending ModuleDatabase's Pick, not merely relying on whatever NodePgDatabase happens to expose.
• A trusted module can still issue arbitrary SQL through execute; this facade does not, and does not claim to, prevent that.
• Table ownership remains a naming/migration-policy convention, not a runtime-enforced sandbox — the same tradeoff ADR 0007 already accepted for schema ownership.
• Static installation and API-restart requirements are unchanged: a module's database access, like its routes and permissions, only takes effect on the next restart.
• A module's migration must be applied before starting code that depends on it; the platform does not infer or run it automatically, so an operator error here surfaces as a real database error from the module's own code, sanitized like any other handler failure.

Rejected alternatives

• Exposing pg.Pool or $client directly was rejected: it would leak platform connection infrastructure into module code and make every module responsible for connection lifecycle the platform already owns correctly.
• Supplying DATABASE_URL to modules was rejected for the same reason, plus the operational risk of a module opening its own competing connections outside the platform's one validated pool.
• One pool per module was rejected: it would multiply connection overhead and configuration surface for no isolation benefit this facade does not already provide at the type level, and contradicts the one-pool-per-runtime model ADR 0015 already established.
• Letting a module construct its own Drizzle instance was rejected: it would reintroduce exactly the raw-pool/connection-string exposure this ADR exists to avoid.
• Database access during register was rejected: registration remains declarative — the same reasoning ADR 0006 already applied to capability provision — and a database-free registration hook can never have a partially-open connection to clean up if discovery aborts.
• Module-global service assignment (a module stashing services in a top-level variable from start for its handlers to read later) was rejected as the documented pattern: the platform provides the identical object directly to every authorized call site, so no module needs to invent its own storage for it.
• Running migrations automatically at startup was rejected: it would violate the existing, explicit, operator-invoked migration model (ADR 0009) and risk running unreviewed DDL as a side effect of an API restart.
• A generic, string-keyed service locator (services.get("database")) was rejected in favor of a fixed, typed ModuleRuntimeServices shape: a locator would erase type information at exactly the boundary this ADR exists to keep typed, and would let a module probe for services that do not exist yet.
• A global schema/query object spanning every installed module (Drizzle's own schema-bound query relational builder, extended to cover every module) was rejected: it would require the platform to aggregate every module's schema into one object at runtime, reintroducing exactly the cross-module coupling ADR 0007's per-module schema ownership already rejected.
• SQL parsing, rewriting, or query interception as a runtime isolation boundary was rejected: modules are trusted compiled code already capable of running arbitrary queries through execute; a parsing layer would be both bypassable and a false promise of a security boundary this facade does not attempt to provide.

Testing evidence

• Unit coverage (contracts, Module SDK, Core, apps/api) proves: the public ModuleDatabase surface preserves Drizzle's table-aware inference with no any; raw pool/client/session/dialect properties are absent from the type and, separately, from the constructed facade at runtime; every forwarded method remains correctly bound when destructured; register receives no services/database field; start/stop receive the same frozen services object; a module HTTP handler's database is reference-equal to its module's lifecycle database; denied, trusted-origin-rejected, and OPTIONS requests never touch the database fake; a zero-module runtime never constructs or touches a database; startup/shutdown cleanup ordering holds under every failure path exercised.
• A real installed-module PostgreSQL integration fixture (core/tests/fixtures/ module-database-integration/db-facade/, exercised from apps/api/tests/ module-database.integration.ts) proves the same guarantees against a real, disposable PostgreSQL database: the module's table does not exist before its migration is explicitly applied; constructing the pool and facade performs no DDL; starting the API never runs or regenerates a migration; a real start hook write is visible to a later authorized HTTP read through the identical facade object; a real authorized mutation persists and a later read observes it, gated by permission enforcement and trusted-Origin checking exactly as ADR 0015 requires; a real committed transaction persists and a real rollback (a deliberate unique- constraint violation) leaves no partial mutation; a module deliberately catching a real PostgreSQL failure returns its own response untouched, while an uncaught real failure sanitizes to the existing MODULE_HANDLER_FAILED boundary with no leaked SQL, error text, or path; a stop hook's write completes and is independently observable before the runtime's pool closes.