Audit trail & soft-delete pattern template

Tables in this data model

• users — 4 columns
• documents — 5 columns
• document_versions — 7 columns
• audit_events — 6 columns
• audit_field_changes — 5 columns
• retention_policies — 3 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE users (
  user_id    INTEGER      NOT NULL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL,
  created_at TIMESTAMP    NOT NULL,
  deleted_at TIMESTAMP
);

CREATE TABLE documents (
  document_id INTEGER      NOT NULL PRIMARY KEY,
  owner_id    INTEGER      NOT NULL REFERENCES users(user_id),
  title       VARCHAR(200) NOT NULL,
  current_version INTEGER  NOT NULL,
  deleted_at  TIMESTAMP
);

CREATE TABLE document_versions (
  version_id  INTEGER   NOT NULL PRIMARY KEY,
  document_id INTEGER   NOT NULL REFERENCES documents(document_id),
  version_no  INTEGER   NOT NULL,
  body        TEXT      NOT NULL,
  author_id   INTEGER   NOT NULL REFERENCES users(user_id),
  valid_from  TIMESTAMP NOT NULL,
  valid_to    TIMESTAMP
);

CREATE TABLE audit_events (
  event_id    INTEGER     NOT NULL PRIMARY KEY,
  actor_id    INTEGER     REFERENCES users(user_id),
  entity_type VARCHAR(40) NOT NULL,
  entity_id   INTEGER     NOT NULL,
  action      VARCHAR(20) NOT NULL,
  occurred_at TIMESTAMP   NOT NULL
);

CREATE TABLE audit_field_changes (
  change_id INTEGER      NOT NULL PRIMARY KEY,
  event_id  INTEGER      NOT NULL REFERENCES audit_events(event_id),
  field     VARCHAR(60)  NOT NULL,
  old_value TEXT,
  new_value TEXT
);

CREATE TABLE retention_policies (
  policy_id   INTEGER     NOT NULL PRIMARY KEY,
  entity_type VARCHAR(40) NOT NULL,
  keep_days   INTEGER     NOT NULL
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates