PostgreSQL 15+ Relational architecture

A schema designed for systems that remember.

Understand the data model behind a document platform.

A multi-tenant PostgreSQL foundation for documents, immutable version history, workspace-scoped tags, threaded comments, document permissions and durable audit events.

Explore architecture ↓ Read DATA_MODEL.md ↗
08 core tables UUID primary keys INSERT only versions
schema.graph structured
▦
documents core entity
01
↻
document_versions immutable history
⌁
permissions document ACL
#
tags workspace labels
◌
audit_log append-only events
workspace_id → tenant boundary

01 / Architecture

The system at a glance

Every table has a focused responsibility. Foreign keys preserve relationships while workspace identifiers keep tenant data partitioned.

⌘

Entity map

8 tables
▦ documents Core record
users identity
document_versions history
tags labels
comments threads
permissions access
audit_log events
document_tags join table

The document is the central entity. Versions, comments, permissions, tags and audit events connect around it.

▤

Table responsibilities

reference
01
users

Login identities and display names.

identity
02
documents

Workspace-scoped document records and workflow status.

core
03
document_versions

Immutable snapshots created on every save.

history
04
tags / document_tags

Flat labels and their many-to-many association.

taxonomy
05
comments

Threaded discussions with self-referencing replies.

collab
06
permissions

One viewer, editor, or admin role per user/document pair.

acl
07
audit_log

Append-only record of meaningful mutations.

audit

02 / Design principles

Built around durable rules

The schema places important integrity guarantees in PostgreSQL instead of relying only on application code.

↻

Immutable versioning

Each save inserts a new version. Historical rows are never updated or deleted during normal operation.

document_versions
⌂

Tenant isolation

workspace_id partitions documents and tags by tenant without pretending the external workspace service is local.

workspace_id
◈

Granular permissions

Document-level access is explicit: viewer, editor, or admin, with one role per user/document pair.

permissions
⌁

Traceable activity

Audit events preserve meaningful actions and JSONB payloads, even when related records are removed.

audit_log

03 / Integrity layer

Constraints that protect the contract

These are the rules that stop invalid states before they become production data problems.

PK

Primary keys

UUIDs identify core records. The document_tags join table uses a composite key because the relationship itself has no independent identity.

id UUID PRIMARY KEY
UQ

Unique constraints

Prevent duplicate emails, workspace slugs, version numbers, tag names and permission grants.

(workspace_id, slug)
FK

Foreign keys

Delete behavior is intentional: cascade dependent records, restrict destructive ownership changes and preserve audit history with SET NULL.

ON DELETE CASCADE
CK

Check constraints

Status, role, action and version values are limited to valid domain values.

status IN (...)

04 / Query layer

Queries that carry the load

The data model is designed around common reads: opening a document, listing workspace documents and searching document content.

⌘

Load the latest document version

Query 01

The most frequent read joins the document to its latest immutable version and owner.

latest_version.sql PostgreSQL 
SELECT
  d.id,
  d.slug,
  d.title,
  d.status,
  dv.version,
  dv.body,
  u.display_name AS owner_name
FROM documents d
JOIN document_versions dv
  ON dv.document_id = d.id
 AND dv.version = (
   SELECT MAX(dv2.version)
   FROM document_versions dv2
   WHERE dv2.document_id = d.id
 )
JOIN users u
  ON u.id = d.owner_id
WHERE d.slug = $1
  AND d.workspace_id = $2;
Index: idx_documents_workspace_slug Latest lookup: version DESC

05 / Get started

Run the schema locally.

The repository contains the SQL schema, this visual preview and the complete data model documentation.

$ createdb document_model
$ psql -d document_model -f schema.sql
$ psql -d document_model -c "\dt"
Open full documentation ↗ View repository ↗