Modular Monolith Database Design: Schemas, Ownership and Foreign Keys

What schema-per-module data ownership means in a modular monolith database

In a schema-per-module design, the whole application runs against one PostgreSQL instance and one database, and each module gets its own schema: billing, catalog, orders. Every table belongs to exactly one module. That module is the only code that writes to the table or changes its structure. Other modules read only what the owner chooses to publish, usually through views in a separate schema. They never touch the owner's tables directly.

This keeps something a split into services would cost you: an ordinary transaction that spans several tables, because there is still only one database. It also stops the usual decay of a shared schema. When any module can join or update any table, every column becomes a de facto public API that nobody can safely rename. When each table has a single owner, a change to it has only one place to start from and one team to negotiate with.

The approach suits a monolith with several modules or teams. It also suits a system you might split later, because the schema boundaries become extraction boundaries.

Ownership has to exist in code, not just in an architecture diagram. Put it in schema names, in one migration directory per module, in one database role per module, and in an ownership manifest in the repository that tooling can check.

All three modules run on one PostgreSQL instance and share one database.

Copy  +----------------+ +----------------+ +----------------+
  | Orders module  | | Billing module | | Catalog module |
  +----------------+ +----------------+ +----------------+
           |                  |                  |
           | orders_rw        | billing_rw       | catalog_rw
           | (write + DDL)    | (write + DDL)    | (write + DDL)
+----------|------------------|------------------|---------+
| PostgreSQL instance, one database                        |
|          v                  v                  v         |
| +----------------+ +----------------+ +----------------+ |
| | schema orders  | | schema billing | | schema catalog | |
| |  owned tables  | |  owned tables  | |  owned tables  | |
| |  outbox table  | |  outbox table  | |  outbox table  | |
| +----------------+ +----------------+ +----------------+ |
|          |                  |                  |         |
|          v                  v                  v         |
| +----------------+ +----------------+ +----------------+ |
| | orders_api     | | billing_api    | | catalog_api    | |
| |  (read views)  | |  (read views)  | |  (read views)  | |
| +----------------+ +----------------+ +----------------+ |
+----------------------------------------------------------+

  Cross-module reads go only to *_api views,
  e.g.  Billing module --reads--> orders_api  (never orders.*)
  Outbox rows commit in the owner's transaction.

*Each module writes and alters only its own schema through its own role, publishes read-only views in a separate _api schema, and keeps an outbox table, all inside one PostgreSQL database.

Implementing schemas, roles and grants in PostgreSQL

Each module gets its own schema, created with CREATE SCHEMA billing AUTHORIZATION billing_rw so the module's role owns it and everything inside it. A table owner always has full rights on its own tables, so ownership is what gives a module control of its data. Isolation comes from what you withhold. Revoke CREATE on public from PUBLIC, which PostgreSQL 15 and later already do by default. Grant a role no USAGE on a schema it does not own. Without USAGE, catalog_rw cannot resolve any object in billing, even when a table-level grant exists. Never let application roles run as superusers, because superusers bypass every privilege check.

Don't rely on search_path to find objects. It resolves unqualified names, and a misconfigured path can silently pick up a same-named table from another schema. Write schema-qualified names such as billing.invoice in application code, so the boundary shows in the query text and in code review.

To expose data, the owning module creates views in its _api schema. It then grants the consumer USAGE on that schema and SELECT on the views. By default, a view checks privileges as its owner, not as the caller. The consumer can read the view without having access to the underlying tables. Billing can then change its internal tables freely, as long as the view's columns stay the same.

This adds operational work. You need a role per module, possibly with separate migration and runtime login roles. Each module also needs its own connection credentials and pool configuration. Local setup needs a bootstrap script that creates the roles, schemas and grants before migrations run, so developers hit the same permission errors that production would raise. Role and privilege details are covered in the PostgreSQL documentation.

Copy-- Local bootstrap: run as a superuser before any module migrations.
-- Placeholder passwords for local development only.
CREATE ROLE billing_rw LOGIN PASSWORD 'local-dev-billing';
CREATE ROLE catalog_rw LOGIN PASSWORD 'local-dev-catalog';

-- Nobody gets to create objects in public by default (PG15+ already does this).
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Each module owns its private schema and its contract schema.
CREATE SCHEMA billing AUTHORIZATION billing_rw;
CREATE SCHEMA billing_api AUTHORIZATION billing_rw;
CREATE SCHEMA catalog AUTHORIZATION catalog_rw;

-- Normally done by billing's migrations; inlined here to show ownership.
SET ROLE billing_rw;
CREATE TABLE billing.invoice (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  total_cents bigint NOT NULL,
  status      text   NOT NULL,
  internal_notes text
);

-- Stable read contract: only the columns consumers may depend on.
-- The view checks privileges as its owner (billing_rw), not the caller.
CREATE VIEW billing_api.invoice_summary AS
  SELECT id, customer_id, total_cents, status FROM billing.invoice;

-- Catalog can see the contract schema and read the view, nothing else.
GRANT USAGE ON SCHEMA billing_api TO catalog_rw;
GRANT SELECT ON billing_api.invoice_summary TO catalog_rw;
RESET ROLE;

-- Quick check of the boundary:
SET ROLE catalog_rw;
SELECT id, status FROM billing_api.invoice_summary;  -- allowed
-- SELECT * FROM billing.invoice;  -- ERROR: permission denied for schema billing
RESET ROLE;

A local bootstrap script that gives each module a role-owned schema and exposes billing data to catalog only through a granted view in billing_api, so catalog has no access to the underlying table.

Cross-module references: foreign key, plain ID or local copy

Once two modules live in separate schemas, any column that points at another module's data needs one of three representations. PostgreSQL allows foreign keys across schemas in the same database, so the choice is a design decision, not a technical limit.

Choose based on four questions:

• Lifecycle coupling. Is the referenced row created and destroyed alongside yours?
• Delete semantics. Should a delete be blocked, cascade, or be tolerated?
• Likelihood of a split. Could this module plausibly become a separate service?
• Cost of bad data. How much does a dangling reference cost you?

Keeping the foreign key has three costs. Migrations must run in dependency order across modules. An ON DELETE CASCADE lets one module silently remove another module's rows. Adding or validating the constraint locks both tables. Dropping the key avoids all of this, but orphaned rows become possible. You then need a scheduled job that finds and repairs them.

If you keep a cross-module key, record it as a deliberate exception:

• Add a comment on the constraint itself.
• Add an entry to an allow-list that your boundary checks read.
• Name the owning team and the reason.

That allow-list doubles as the list of constraints to remove before any extraction.

Copy-- Deliberate exception: billing.invoices -> customers.customers
-- RESTRICT, not CASCADE: customers must not silently delete billing rows.
-- NOT VALID skips the full scan of existing rows when the key is added.
ALTER TABLE billing.invoices
  ADD CONSTRAINT invoices_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers.customers (id)
  ON DELETE RESTRICT
  NOT VALID;

-- Check existing rows in a separate step, with a lighter lock than the ADD.
ALTER TABLE billing.invoices VALIDATE CONSTRAINT invoices_customer_id_fkey;

-- Record the owner and the reason on the constraint itself.
COMMENT ON CONSTRAINT invoices_customer_id_fkey ON billing.invoices IS
  'cross-module exception; owner: billing-team; reason: invoices must never reference a missing customer; drop before extracting billing';

-- Boundary check: list every foreign key that crosses a schema.
-- CI compares this output with the allow-list and fails on unlisted keys.
SELECT c.conname,
       c.conrelid::regclass  AS from_table,
       c.confrelid::regclass AS to_table
FROM pg_constraint c
JOIN pg_class src ON src.oid = c.conrelid
JOIN pg_class dst ON dst.oid = c.confrelid
WHERE c.contype = 'f'
  AND src.relnamespace <> dst.relnamespace;

-- Without the constraint: a scheduled job that finds orphaned rows.
SELECT i.id, i.customer_id
FROM billing.invoices i
LEFT JOIN customers.customers cu ON cu.id = i.customer_id
WHERE i.customer_id IS NOT NULL
  AND cu.id IS NULL;

Adds a cross-schema foreign key in PostgreSQL as a documented exception, using RESTRICT and a two-step NOT VALID then VALIDATE rollout, then shows a catalog query that lists cross-schema keys for a boundary check and the orphan-detection query you would schedule if you dropped the key.

Cross-module queries and transactions

Reads across a boundary follow the same rule as references: prefer the option that lets the owning module change its tables without telling anyone.

Interfaces tempt callers into N+1 loops: fetch 200 invoices, then call customers.get(id) 200 times. Design batch methods that accept a set of IDs from the start, so the owner can serve them with one WHERE id = ANY(...) query. For analytics, provide an explicitly allowed reporting path, such as a read-only role over published views or a replica. That keeps dashboards from turning into a pile of unsanctioned joins.

Transactions

A transaction that writes to two modules' tables works, which is the problem. It hides a dependency that no interface declares, and it fails once either module moves out. Banning these transactions outright has its own cost. Teams start building sagas and compensation logic for a process that still runs in one database.

The middle ground is the transactional outbox. The owning module writes its state change and an event row to its own outbox table in the same local transaction. A relay publishes the row afterwards, and other modules react in their own transactions. The event is recorded only if the state change commits, so nothing is lost or invented.

Copy-- Outbox lives in the owning module's schema; only the orders module writes to it.
CREATE TABLE orders.outbox (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  aggregate_id   bigint      NOT NULL,
  event_type     text        NOT NULL,
  payload        jsonb       NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  published_at   timestamptz            -- NULL until the relay has sent it
);

-- State change and event row commit or roll back together, in one local transaction.
BEGIN;

UPDATE orders.orders
SET    status = 'confirmed'
WHERE  id = 4711 AND status = 'pending';

INSERT INTO orders.outbox (aggregate_id, event_type, payload)
VALUES (4711, 'OrderConfirmed',
        jsonb_build_object('order_id', 4711, 'customer_id', 982));

COMMIT;

-- Relay, run separately: claim a batch without blocking other relay workers.
-- In application code, check the UPDATE's row count and skip the INSERT if it is 0.
BEGIN;

SELECT id, event_type, payload
FROM   orders.outbox
WHERE  published_at IS NULL
ORDER  BY id
LIMIT  100
FOR UPDATE SKIP LOCKED;

-- ...publish the selected rows to the broker, then mark them as sent:
UPDATE orders.outbox SET published_at = now() WHERE id = ANY('{1,2,3}'::bigint[]);

COMMIT;

A PostgreSQL transactional outbox in which the orders module writes its status change and an OrderConfirmed event row in one local transaction, and a relay later claims unpublished rows with FOR UPDATE SKIP LOCKED to deliver them at least once.

Per-module migrations and automated boundary enforcement

Give each module its own migration directory and its own history table inside its schema, such as billing.schema_history. A module's history then records only changes to tables that module owns. Two teams can add migrations in the same week without fighting over one global version sequence, and you can later extract the module along with its full history.

Some ordering between modules is still required. If orders keeps a foreign key to customers, the customers migrations must run first. Declare these dependencies in a small manifest that the migration runner reads, so the order is stated in one place and reviewed like code. Don't rely on alphabetical directory names or timestamps that happen to line up. A dependency cycle in that manifest tells you the boundaries need rethinking.

Enforce ownership in CI with two static checks:

• Parse each module's migration files and fail the build if any statement creates, alters or drops an object in a schema the module does not own.
• Scan ORM mappings and raw SQL in application code for schema-qualified names, and for tables that exist only in another module's schema. Unqualified names can still slip past this check, which is why runtime enforcement is also needed.

The runtime backstop is the role setup from section 2. Run each module's integration tests while connected as that module's restricted role, not as a superuser or the migration owner. A query that reaches across a boundary then fails in the test suite with PostgreSQL's insufficient_privilege error (SQLSTATE 42501). Without this, the same violation stays hidden until someone tightens production grants.

Migrating incrementally from a shared schema

Start by mapping usage before you move anything. Combine a search of the code for table names with what the database itself records. If pg_stat_statements is enabled, it lists the statements that actually run in production, which catches dynamic SQL and reporting jobs that a code search misses. For each table, record which modules read it and which write it. The module that writes a table is usually its owner. A table that several modules write is a design problem you need to resolve.

Then work through it in this order:

1. Assign owners in writing. For each table, record one owning module in a file kept in the repository, so the decision can be reviewed and later checks can enforce it.
2. Create the schema and move the tables. ALTER TABLE ... SET SCHEMA moves a table's indexes, constraints and owned sequences with it. Existing foreign keys keep working across schemas.
3. Keep old code working. Add the new schemas to the application role's search_path, so unqualified table names still resolve after the move. If two tables have the same name, leave a view with the old name in public until every caller is updated.
4. Add roles and grants for each module while the shared application role still has full access. Nothing breaks at this stage.
5. Tighten one module at a time. Switch a module's connections to its own role, revoke the shared role's access to that schema, and run the test suite. Fix any 42501 failures before you move on to the next module.

Some tables will have no clear owner. Put them in an explicit legacy schema, record who is responsible for them, and set a date for deciding what happens to each. If two modules really do write the same table, the usual answer is to split it by column into two tables, one per module.

When to avoid it, alternatives, and impact on cost and engineering time

Skip schema-per-module when one small team owns the whole codebase, when there is really only one module, or when the boundaries are still guesses. Schemas control who can touch a table. They say nothing about whether the boundary is in the right place, and moving a table between schemas later means a migration plus grant changes for every caller. For small reference data such as country codes, a read-only grant on one shared table costs less than events and local copies.

Schemas also give no runtime isolation. Every module shares the same max_connections, autovacuum workers, WAL and buffer cache. A long-running report in one module holds back the vacuum horizon for every table. A lock-heavy batch job slows down every other module on the server.

Illustrative estimate — not a measured result or benchmark. Every number below is an assumed input chosen only to show how to do the arithmetic. Replace them with your own figures before drawing any conclusion.

The formula for year-one net hours is: (boundary bugs avoided × hours per bug) + (extraction hours saved) − (setup hours + monthly upkeep × 12).

Assumed inputs (illustrative, not measured):

• Setup: 40 hours (assumed)
• Monthly upkeep: 4 hours (assumed)
• Boundary bugs avoided: 3, at 8 hours each (assumed)
• Extraction effort: cut from 120 hours to 60 (assumed)

Illustrative result with these assumed inputs: 24 + 60 − 88 = −4 hours, which is roughly break-even. Under these assumptions, the approach pays off only if you refactor or extract modules regularly.

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, 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.
• Adding or removing a column goes through one review with the inheriting services listed, instead of ad-hoc messages to each team.

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.
• Services that depend on a column get the chance to object before it disappears, avoiding emergency fixes.
• 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.