Enterprise MVP database design: domain model to PostgreSQL

Series · Part 4 of 7Enterprise MVP, end to end

Enterprise MVP, end to end: from the first workshop to production
Requirements gathering for an enterprise MVP: what to ask, what to write down
Architecture design for an enterprise MVP: C4 diagrams, a modular monolith and the seams that matter
Database design for an enterprise MVP: from domain model to the first PostgreSQL migration
Developing an enterprise MVP with Flask and React: structure, workflow and tests
CI/CD for an enterprise MVP: GitHub to Cloud Build to Cloud Deploy
GKE or Cloud Run? Choosing where an enterprise MVP runs, and the go-live checklist

On this page · 9 sections

How do you get from requirements to entities?
What does the ERD look like?
Should primary keys be UUIDs or integers?
Enum or lookup table?
Where does jsonb belong?
The first migration
How should an audit log be designed?
How should migrations be managed?
The data dictionary

The database is the part of an MVP that outlives everything else. The React app will be redesigned, the API may be split, but the rows written in the first month will still be there in year seven, because in this project the audit requirement says so. That’s why I give data design its own phase, short as it is.

This is part 4 of a series following an illustrative supplier onboarding portal from requirements to production. The inputs are the state diagram and non-functional requirements from part 2 and the module boundaries from part 3.

How do you get from requirements to entities?

Read the nouns off the workflow, then ask what evidence each state change must leave behind.

Supplier: the company being onboarded, plus its contacts.
Application: one onboarding attempt by a supplier, which moves through the states in the workflow.
Document: an uploaded file of a given document type, attached to an application, with its own review status.
Review decision: a reviewer’s decision (approve, reject, request changes) on an application, by team.
App user: an internal user, as known from the identity provider.
Audit log: an append-only record of every action.
Outbox: events waiting to be delivered to other systems (the ERP sync from part 3).

Each module from part 3 owns its tables: suppliers owns supplier and contact, documents owns document and document type, and so on.

What does the ERD look like?

The audit log and outbox are deliberately left off the diagram. They reference every other table by type and ID rather than by foreign key, which keeps them append-only and independent of the domain tables’ lifecycle.

Should primary keys be UUIDs or integers?

For anything that appears in a URL or leaves the system (application IDs in emails, the reference sent to the ERP), I use UUIDs. They don’t leak volume (“we are application 412”), can’t be enumerated, and can be generated by the app before the insert, which makes idempotent retries easy. Prefer time-ordered UUIDv7 (built into PostgreSQL 18 as uuidv7(), or generated in the app) to keep B-tree inserts local; gen_random_uuid() is fine at this scale.

For internal, high-volume, never-exposed tables (the audit log and outbox) I use a bigint identity: smaller, faster, naturally ordered.

Enum or lookup table?

The rule I use: if the code branches on it, it’s an enum; if the business edits it, it’s a table.

application_status is an enum. The workflow code has a branch for every value, so adding a value is a code change anyway, and the enum stops anyone inserting 'aproved'.
document_type is a lookup table. Compliance will add “ISO 14001 certificate” in month three, and it shouldn’t need a deploy. The table also carries business rules: whether the type is mandatory, and how many months it stays valid.

Where does jsonb belong?

Only where the shape genuinely varies. The application questionnaire changes by supplier category (a raw-materials supplier answers questions a logistics provider doesn’t), so the answers are stored as jsonb, validated by a versioned schema in the API.

Everything you filter, join, sort or constrain on stays a real column: status, supplier, timestamps, tax ID. The test I apply: if a reviewer dashboard will ever need WHERE on it, it’s a column.

The first migration

Here is the core of the initial schema, as the SQL that Alembic’s migration produces (trimmed):

CREATE TYPE application_status AS ENUM (
  'draft', 'submitted', 'in_review', 'changes_requested',
  'approved', 'rejected', 'synced_to_erp', 'sync_failed'
);
CREATE TYPE review_team AS ENUM ('compliance', 'finance');
CREATE TYPE decision AS ENUM ('approve', 'reject', 'request_changes');

CREATE TABLE supplier (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legal_name  text NOT NULL CHECK (length(legal_name) BETWEEN 2 AND 200),
  tax_id      text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz
);
-- Unique only among live suppliers, so a soft-deleted record doesn't block re-registration.
CREATE UNIQUE INDEX supplier_tax_id_live ON supplier (tax_id) WHERE deleted_at IS NULL;

-- app_user, supplier_contact, document_type and document follow the same pattern; omitted here.

CREATE TABLE application (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  supplier_id    uuid NOT NULL REFERENCES supplier (id),
  status         application_status NOT NULL DEFAULT 'draft',
  version        integer NOT NULL DEFAULT 1,  -- optimistic locking
  answers        jsonb NOT NULL DEFAULT '{}',
  answers_schema smallint NOT NULL DEFAULT 1,
  erp_vendor_id  text,
  submitted_at   timestamptz,
  decided_at     timestamptz,
  created_at     timestamptz NOT NULL DEFAULT now(),
  CHECK (status NOT IN ('approved', 'synced_to_erp') OR decided_at IS NOT NULL)
);
-- Reviewer queues only ever look at open applications.
CREATE INDEX application_open ON application (status, submitted_at)
  WHERE status IN ('submitted', 'in_review', 'changes_requested');

CREATE TABLE review_decision (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  application_id uuid NOT NULL REFERENCES application (id),
  reviewer_id    uuid NOT NULL REFERENCES app_user (id),
  team           review_team NOT NULL,
  decision       decision NOT NULL,
  comment        text,
  created_at     timestamptz NOT NULL DEFAULT now(),
  -- coalesce matters: length(NULL) is NULL, and a NULL check passes.
  CHECK (decision = 'approve' OR length(coalesce(comment, '')) >= 10)
);
CREATE INDEX review_decision_application ON review_decision (application_id);

CREATE TABLE audit_log (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  actor_type  text NOT NULL,          -- 'user' | 'supplier' | 'system'
  actor_id    text NOT NULL,
  entity_type text NOT NULL,
  entity_id   uuid NOT NULL,
  action      text NOT NULL,
  before      jsonb,
  after       jsonb,
  request_id  text
);
CREATE INDEX audit_log_entity ON audit_log (entity_type, entity_id, occurred_at);

CREATE TABLE outbox (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  event_type    text NOT NULL,
  aggregate_id  uuid NOT NULL,
  payload       jsonb NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  dispatched_at timestamptz,
  attempts      integer NOT NULL DEFAULT 0
);
CREATE INDEX outbox_pending ON outbox (id) WHERE dispatched_at IS NULL;

A few choices worth calling out:

Constraints carry business rules. “A rejection or change request needs a comment” is a CHECK, not just a form validation. The database is the last line of defence, and it defends against every client, including the admin script someone writes in month eight.
Partial indexes match the real queries: reviewers only look at open applications, and the dispatcher only looks at undelivered outbox rows. They stay small as history grows.
Optimistic locking via version. Two reviewers acting on the same application at once is a real scenario; SQLAlchemy’s version_id_col turns the second write into a clean conflict error instead of a silent overwrite.
Soft delete only where it’s needed. Suppliers can be deactivated; applications and documents are never deleted, because the audit requirement says they must be retrievable for seven years. A replaced document is marked superseded, not removed.

How should an audit log be designed?

Three properties matter: it’s complete, it’s append-only, and it’s cheap to query by entity.

Complete: the service layer writes the audit row in the same transaction as the change it describes, with the actor and request ID from the request context. I prefer this to database triggers, because only the application knows who the actor is and why.
Append-only: the application’s database role is granted INSERT and SELECT on audit_log, but not UPDATE or DELETE. Tampering then requires a different, more privileged credential, and Cloud SQL’s pgAudit support can log what that credential does.
Queryable: the (entity_type, entity_id, occurred_at) index makes “show me the history of this application” instant.

Seven years of retention isn’t an MVP problem at 500 applications a year. The plan, written in an ADR rather than built, is monthly partitioning and export of older partitions to Cloud Storage when the table passes a size threshold.

How should migrations be managed?

With SQLAlchemy models and Alembic (via Flask-Migrate), from the very first table:

"""add document review status

Revision ID: 7c2e1f4a9b10
Revises: 3a91d0c2e5f7
"""
from alembic import op
import sqlalchemy as sa

revision = "7c2e1f4a9b10"
down_revision = "3a91d0c2e5f7"

document_status = sa.Enum(
    "pending", "accepted", "changes_requested", name="document_status"
)


def upgrade():
    document_status.create(op.get_bind(), checkfirst=True)
    # Expand: add as nullable with a default, so the running app version is unaffected.
    op.add_column(
        "document",
        sa.Column("status", document_status, nullable=True, server_default="pending"),
    )


def downgrade():
    op.drop_column("document", "status")
    document_status.drop(op.get_bind(), checkfirst=True)

The rules the team follows:

Autogenerate, then read. flask db migrate writes a first draft; a human reviews every line in the pull request. Autogenerate misses things (enum changes, server defaults, renames it sees as drop-and-add).
Never edit a migration that has run anywhere shared. Write a new one.
Expand, then contract. Add the new column as nullable, deploy code that writes both, backfill, then make it NOT NULL and remove the old column in a later release. During a rolling deploy the old and new app versions run against the same schema, so every migration must be compatible with both.
Migrations run as a separate step before the new version takes traffic, never on app start-up, where several instances would race to run them. Part 6 shows how that step fits into the Cloud Deploy pipeline.
Test migrations in CI against a real PostgreSQL: upgrade from empty to head, then downgrade one step and upgrade again.

The data dictionary

The last artefact of this phase is a one-page data dictionary: each table, its owner module, what a row means in business terms, retention, and whether it contains personal data. Bank details and contact emails are personal data, and the client’s data protection officer will want to know exactly where they live. It takes an afternoon to write and saves a week of back-and-forth in the security review.

Next: Development, building it in Flask and React, one demo at a time.

Schema changes are the only deploys you can’t simply roll back. Design them as if you’ll have to.