What is a schema-per-module modular monolith, and why enforce table ownership?
A schema-per-module modular monolith keeps a single Postgres database but gives each module its own schema: billing, catalog, identity, and so on. Every table belongs to exactly one module. That module is the only code allowed to write to it, and other modules reach its data through the owner's public interface, such as an in-process API, a read-only view, or published events.
The defining property is that the database enforces ownership, not a naming convention. A billing_ prefix only says who should touch a table. Postgres schemas combined with per-module roles and grants actually prevent other modules from touching it. An UPDATE from the wrong module fails at runtime instead of depending on a reviewer to catch it in a diff.
You get much of the isolation of separate databases without giving up what one database provides: a single transaction can still cover several modules when you need it, you have one backup and restore story, and there is one connection pool to operate. Boundaries become visible in the schema itself. Refactoring a table is safer because you know exactly which module reads and writes it. If a module later moves into its own service, its data is already separated, and its dependencies are already explicit.
The pattern suits teams whose monolith has outgrown informal conventions. It fits when several teams share one database, or when cross-module queries keep getting past code review. It works as a step before a microservice split, and often as a replacement for one.
+-- Application process (one deployable) ----------------------------------+
| |
| +-------------+ +-------------+ +-------------+ |
| | identity | | billing | | catalog | |
| | module |<--- API ---| module |--- API --->| module | |
| +------+------+ +------+------+ +------+------+ |
| | | | |
+---------|--------------------------|--------------------------|----------+
| identity_rw | billing_rw | catalog_rw
| | |
+---------|--------------------------|--------------------------|----------+
| v v v |
| +-------------+ +-------------+ +-------------+ |
| | identity | | billing | | catalog | |
| | schema | | schema | | schema | |
| | users | | invoices | | products | |
| | sessions | | payments | | prices | |
| +-------------+ +-------------+ +-------------+ |
| |
| catalog_rw: UPDATE billing.invoices --> ERROR: permission denied |
| one transaction may span schemas; one backup; one connection pool |
+-- Postgres: one database, one schema per module -------------------------+
each *_rw role holds write grants on its own schema only
Each module connects through its own Postgres role that can write only to its own schema, so other modules must go through the owner's API and a cross-module write is rejected by the database rather than caught in review.
How do you build a table ownership map?
Start from evidence, not from the directory tree. Three sources together give a reliable picture of who writes what:
- Query logs. Setting
log_statement = 'mod'for a representative period captures every INSERT, UPDATE and DELETE. If the application tags connections or statements with the calling module, each write maps back to its source. Thepg_stat_statementsextension shows which write statements run most often. - ORM model usage. List every place a model class is saved, updated or deleted. Imports alone don't tell you much, because they mix reads and writes.
- Repository grep. Search for raw SQL strings and table names. These catch writes that bypass the ORM, such as reporting jobs, scripts and migrations that backfill data.
Assign exactly one owning module per table. Any table with more than one writer is a design problem, and it goes at the top of the backlog. Usually one module should expose an operation that the other modules call.
Some tables really are shared. users, tenants and audit_log are written or referenced almost everywhere. If you force them into one module, that module becomes a dependency for everything else and turns into a god-module. A small shared_kernel schema is more honest. Its contents should be few and stable, and changes to them should be deliberate.
Sample ownership map
| Table | Owner | Writers found | Readers | Status |
|---|---|---|---|---|
orders |
ordering | ordering | billing, shipping | Clean |
invoices |
billing | billing, ordering | ordering | Multiple writers: fix first |
tenants |
shared_kernel | admin | all | Shared |
audit_log |
shared_kernel | all (append-only) | compliance | Shared |
-- 1. Log every INSERT/UPDATE/DELETE with the client's application_name (%a).
-- Each module sets its own name when it connects, e.g.
-- application_name=ordering or application_name=billing.
ALTER SYSTEM SET log_statement = 'mod';
ALTER SYSTEM SET log_line_prefix = '%m [%p] app=%a user=%u db=%d ';
SELECT pg_reload_conf();
-- 2. Summarize write statements from pg_stat_statements.
-- Requires pg_stat_statements in shared_preload_libraries (restart),
-- then: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- pg_stat_statements does not record application_name. If each module
-- connects as its own role, userid tells you which module wrote.
SELECT
substring(query FROM '(?i)^\s*(?:insert\s+into|update|delete\s+from)\s+([a-z0-9_."]+)')
AS target_table,
userid::regrole AS db_role,
sum(calls) AS calls,
sum(rows) AS rows_affected
FROM pg_stat_statements
WHERE query ~* '^\s*(insert|update|delete)\s'
GROUP BY 1, 2
ORDER BY target_table, calls DESC;
-- 3. Flag tables written by more than one role. These are the first
-- design problems to fix. The regex skips writes that start with WITH
-- (CTEs), so cross-check those with the query log and grep.
SELECT target_table, array_agg(DISTINCT db_role) AS writers
FROM (
SELECT
substring(query FROM '(?i)^\s*(?:insert\s+into|update|delete\s+from)\s+([a-z0-9_."]+)') AS target_table,
userid::regrole::text AS db_role
FROM pg_stat_statements
WHERE query ~* '^\s*(insert|update|delete)\s'
) w
GROUP BY target_table
HAVING count(DISTINCT db_role) > 1;
This example turns on write-statement logging tagged with application_name and uses pg_stat_statements to list each table's writers by database role, then flags tables that have more than one writer.
How does one Postgres schema per module compare with prefixes or separate databases?
Once every table has exactly one owner, you need a physical boundary that matches the map. There are three common options:
| Criterion | Table prefixes (billing_invoices) |
Schema per module (billing.invoices) |
Database per module |
|---|---|---|---|
| Isolation | Naming convention only | Namespace plus privileges | Separate catalogs and connections |
| Enforceability | Code review and linting | GRANT/REVOKE per schema, default privileges |
Connection credentials |
| Cross-module query cost | Plain joins, nothing stops them | Joins still possible, but only where granted | Requires a foreign data wrapper or application-side composition |
| Transactions | Single local transaction | Single local transaction | No atomic commit without two-phase commit or sagas |
| Operational effort | Lowest | Low: one cluster, one backup | Higher: more pools, migrations, and monitoring |
Prefixes cost almost nothing, but the database cannot enforce them. Postgres privileges attach to schemas and tables, not to name patterns, so a new billing_ table has no protection unless someone remembers to grant it.
Schemas are the better default. They give each module a namespace that roles can be scoped to, and default privileges cover tables created later. They also keep one connection pool, one backup, and real ACID transactions. You can still allow a deliberate cross-module read during migration, and that permission is visible in the grants.
Separate databases make sense when a module is about to become its own service, needs different scaling or retention, or has compliance requirements for physical separation. You pay for this in distributed consistency, so take that cost on deliberately.
How do you move tables and enforce ownership with Postgres roles?
Create one schema per module (CREATE SCHEMA billing) and move each table with ALTER TABLE ... SET SCHEMA billing. The move is a catalog change, not a data rewrite. Indexes, constraints and owned sequences move with the table. Views and foreign keys reference the table by internal OID, so they keep working. The statement needs a brief ACCESS EXCLUSIVE lock, so run it when no long transactions are open. Function bodies that use unqualified table names are not updated and will break.
Give each module its own role. Grant it USAGE on its schema and the table privileges it needs. Revoke the defaults that let other roles reach in, starting with PUBLIC's privileges on public. Then run ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing so that tables created later get the same grants. Default privileges apply only to objects created by the named role. This is one reason to run each module's migrations under its own role as well as its application connections. When the reporting module queries billing.invoices directly, it gets a permission error in tests, not a silent dependency. A superuser connection bypasses all of this, so keep superusers out of application paths.
During the move, set search_path per role with ALTER ROLE ... SET search_path, or per connection, so that unqualified queries still resolve. Remove that setting once the code uses qualified names. If two schemas on the path contain the same table name, the first match wins without any warning, and you lose track of which copy a query reads.
The cost is operational. You manage more roles and grants, and connection poolers usually pool by user and database, so you need a separate pool and credentials for each module.
-- Run as an admin role (not from the app). Placeholder passwords only.
CREATE ROLE billing_owner LOGIN PASSWORD 'change-me-owner'; -- runs billing migrations
CREATE ROLE billing_app LOGIN PASSWORD 'change-me-app'; -- billing app connections
CREATE SCHEMA billing AUTHORIZATION billing_owner;
-- Move existing tables. This changes the catalog only and does not rewrite data.
-- Fail fast instead of queuing behind long transactions.
BEGIN;
SET LOCAL lock_timeout = '5s';
ALTER TABLE public.invoices SET SCHEMA billing;
ALTER TABLE billing.invoices OWNER TO billing_owner; -- owned sequences follow
COMMIT;
-- Stop every role from creating objects in public (already the default in PG15+).
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
-- Only billing roles can reach the billing schema.
REVOKE ALL ON SCHEMA billing FROM PUBLIC;
GRANT USAGE ON SCHEMA billing TO billing_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing TO billing_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA billing TO billing_app;
-- Future objects get the same grants. This applies only to objects
-- created by billing_owner, so migrations must run as that role.
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO billing_app;
ALTER DEFAULT PRIVILEGES FOR ROLE billing_owner IN SCHEMA billing
GRANT USAGE, SELECT ON SEQUENCES TO billing_app;
-- Temporary: keep unqualified names resolving during the move.
-- Remove it once the code uses billing.invoices explicitly.
ALTER ROLE billing_app SET search_path = billing, public;
-- Check in tests: another module's role is rejected.
-- SET ROLE reporting_app;
-- SELECT * FROM billing.invoices; -- ERROR: permission denied for schema billing
Creates a billing schema and moves a table into it under a short lock timeout, then sets up owner and app roles with explicit and default grants and a temporary search_path, so that access from other modules fails with a permission error.
What should replace cross-module joins and foreign keys?
Start with an inventory. List every query that reads tables from more than one schema: search repository and ORM code, and check pg_stat_statements if it is enabled, because it catches raw SQL that code search misses. Give each join an owner and pick one replacement for it.
| Replacement | Use when | Cost |
|---|---|---|
| In-process call to the owning module's API | Transactional paths with small, bounded result sets | N+1 risk unless the API accepts batches of IDs. Joins that ran inside Postgres now run in application memory, which slows large reads |
| View or read model published by the source module | The consumer needs many rows or filters on another module's columns | The source module must version it like any other API. A materialized read model can serve stale data |
| Documented exception | Removing the join costs more than it is worth for now | It stays as coupling. Track it with an owner and a review date |
Cross-schema foreign keys can stay during the move. They protect data while code changes. If you replace them with ID-only references and application checks, you lose real guarantees. Postgres no longer rejects an insert that points at a missing row. A concurrent delete can slip between your existence check and your write. ON DELETE behaviour disappears. Add scheduled orphan-detection jobs that find dangling IDs, then report or repair them. Decide per relationship whether deleting the rows is ever acceptable.
Reporting, analytics and admin tooling need wide reads that no module API should serve. Give them a sanctioned path: a dedicated reporting role with SELECT grants across schemas, preferably on a read replica. That way ad hoc queries cannot block module writes or quietly turn into application dependencies.
-- 1. Inventory: list every foreign key that crosses a schema boundary.
SELECT c.conname,
src_ns.nspname || '.' || src.relname AS from_table,
dst_ns.nspname || '.' || dst.relname AS to_table
FROM pg_constraint c
JOIN pg_class src ON src.oid = c.conrelid
JOIN pg_namespace src_ns ON src_ns.oid = src.relnamespace
JOIN pg_class dst ON dst.oid = c.confrelid
JOIN pg_namespace dst_ns ON dst_ns.oid = dst.relnamespace
WHERE c.contype = 'f'
AND src_ns.nspname <> dst_ns.nspname;
-- 2. Orphan detection after dropping orders -> customers FK:
-- run on a schedule; report or repair rows whose referenced customer is gone.
SELECT o.id, o.customer_id
FROM orders.orders o
WHERE NOT EXISTS (
SELECT 1 FROM customers.customers cu WHERE cu.id = o.customer_id
);
-- 3. Sanctioned read path: a read-only reporting role across module schemas.
CREATE ROLE reporting_ro NOLOGIN;
GRANT USAGE ON SCHEMA orders, customers TO reporting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA orders, customers TO reporting_ro;
-- Cover future tables; applies to tables created by the role running this.
ALTER DEFAULT PRIVILEGES IN SCHEMA orders, customers
GRANT SELECT ON TABLES TO reporting_ro;
-- Login user for BI/admin tools; point it at a read replica where possible.
CREATE ROLE report_user LOGIN PASSWORD 'change-me-placeholder' IN ROLE reporting_ro;
ALTER ROLE report_user SET default_transaction_read_only = on;
SQL that inventories cross-schema foreign keys from the system catalogs, detects orphaned IDs once a foreign key is replaced by an ID-only reference, and sets up a read-only reporting role as the sanctioned path for cross-module reads.
How should you handle migrations and transactions that span modules?
Give each module its own migration directory, or at minimum a naming convention that encodes the owner, and run each module's migrations under that module's role. Then a migration that touches another module's schema fails on permissions instead of slipping through review.
When one change spans two modules, use expand-and-contract ordering. The providing module ships its additive change first, such as a new column, view, or API field. The consuming module switches to it in a later migration. Removals run in reverse: the consumer stops reading, and only then does the provider drop. Keep a single deploy pipeline that applies all module migrations in a fixed, deterministic order. Splitting ownership is about accountability, and separate pipelines would bring back the version-skew problems you chose a monolith to avoid.
For transactions, be pragmatic. Both modules share one process and one database, so a single local transaction spanning two modules' writes is correct and cheap. Route it through an application-level coordinator so neither module reaches into the other's tables.
Use an outbox when the boundary is a likely extraction candidate, or when the consumer's work is slow or can fail independently. The producing module writes an event row to an outbox table in its own schema, inside its own transaction. A relay then delivers it to consumers, which must be idempotent. The cost is real: you take on eventual consistency, a relay to operate, and retry and deduplication logic. If extraction never happens, you pay that cost and get nothing back.
When should you avoid this approach, and what does the rollout cost?
Roll out in stages so each step is reversible until the last one:
- Map ownership for every table, including the ambiguous ones.
- Add CI checks that fail on new cross-module SQL. Existing violations go on an allowlist that only shrinks.
- Move low-coupling tables first. These are tables with few foreign readers, so early moves are mostly
ALTER TABLE ... SET SCHEMAand grants rather than refactoring. - Run in log-only mode. Keep the broad grants, and use query logs to record which module roles touch which schemas.
- Enforce by revoking cross-schema privileges once the logs stay clean.
| Saves time | Costs time | |
|---|---|---|
| Daily work | The database and CI catch violations, so reviewers don't have to | Grants and roles for each new table or module |
| Design | Module boundaries are explicit, not tribal knowledge | Read models and events to build and operate |
| Later | Extraction is mostly a connection-string change | Every existing cross-module join must be refactored |
Illustrative estimate (assumed numbers, not a benchmark)
Effort = (tables × h_move) + (cross-module joins × h_join) + (modules × h_roles)
Assume 80 tables at 1 h each, 40 joins at 6 h each, and 6 modules at 8 h each:
80 + 240 + 48 = 368 engineer-hours, about 9 engineer-weeks.
The joins dominate this total, so count them first.
Avoid this approach in three cases:
- Small apps owned by one team. The team already coordinates directly, so there is no coordination problem for the boundaries to solve.
- Heavily shared data models. If most modules read and write the same core entities, you would mostly be drawing arbitrary lines through one domain.
- Teams that can't finish the migration. A half-moved schema, with some tables isolated, some not, and an allowlist nobody trusts, is harder to reason about than either the original monolith or the fully split end state.
How OneEnv can help
Disclosure: OneEnv is the product behind this blog.
OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.
Applied to modular monolith database schema per module, this is where it helps:
Saves time
- Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
- One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
Saves cost
- Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
- Less duplicated configuration to maintain: one shared definition inherited by many services.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.
These are qualitative benefits, not measured results — how much you save depends on how many services share the value.
Want to try it? Start at oneenv.io.