DocForge — MySQL Database Schema
Schema Design Principles
Before diving in: the schema follows third normal form (3NF), uses soft deletes (deleted_at)
everywhere so audit history is never destroyed, stores all AI provenance as first-class data (not
JSON blobs), and separates template definitions from document instances cleanly so templates
are never mutated when a user opens one.
1. Organizations & Multi-Tenancy
sql
CREATE TABLE organizations (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
slug VARCHAR(100) NOT NULL UNIQUE, -- used in URLs: [Link]/acme
plan ENUM('starter','business','enterprise') NOT NULL DEFAULT 'starter',
seats_limit SMALLINT UNSIGNED NOT NULL DEFAULT 5,
logo_url VARCHAR(500),
industry VARCHAR(100),
tech_stack JSON, -- ["[Link]","[Link]","MySQL"] for AI context
country VARCHAR(100),
timezone VARCHAR(100) NOT NULL DEFAULT 'UTC',
sso_enabled BOOLEAN NOT NULL DEFAULT FALSE,
saml_metadata TEXT, -- SAML XML for Enterprise SSO
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL
);
Usage: Top-level tenant. Every piece of data in the system belongs to an org. slug powers
workspace URLs. tech_stack and industry are fed into the AI context assembler so auto-fill can
make smarter suggestions without users re-entering this data per project.
sql
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
organization_id BIGINT UNSIGNED NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
full_name VARCHAR(255) NOT NULL,
avatar_url VARCHAR(500),
password_hash VARCHAR(255), -- NULL if SSO-only user
is_sso_user BOOLEAN NOT NULL DEFAULT FALSE,
saml_subject VARCHAR(255), -- SSO identity binding
role ENUM('owner','admin','member','guest') NOT NULL DEFAULT 'member',
email_verified_at TIMESTAMP NULL,
last_active_at TIMESTAMP NULL,
notification_prefs JSON, -- {"email":true,"slack":false,"reminder_days":2}
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id)
);
Usage: One user always belongs to one org (multi-org access via external reviewer tokens, not
duplicate accounts). notification_prefs stores per-user preferences rather than a separate
settings table to avoid unnecessary joins.
2. Projects & Workspaces
sql
CREATE TABLE projects (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
organization_id BIGINT UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
client_name VARCHAR(255),
description TEXT,
phase
ENUM('initiation','requirements','design','development','testing','deployment','maintenance')
NOT NULL DEFAULT 'initiation',
status ENUM('active','on_hold','completed','archived') NOT NULL DEFAULT 'active',
start_date DATE,
end_date DATE,
context_summary TEXT, -- AI-readable plain-text project brief
compliance_standards JSON, -- ["IEEE 830","ISO 9001","PMBOK"] for template filtering
created_by BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
FOREIGN KEY (organization_id) REFERENCES organizations(id),
FOREIGN KEY (created_by) REFERENCES users(id)
);
Usage: A project is the container for all documents in one client engagement. context_summary
is a concise, human-written or AI-generated blurb the AI context assembler uses when building
prompts — keeping token usage lean. compliance_standards filters which templates show up as
recommended for this project.
sql
CREATE TABLE project_members (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
project_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
role ENUM('manager','editor','reviewer','viewer') NOT NULL,
invited_by BIGINT UNSIGNED NOT NULL,
joined_at TIMESTAMP NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_project_user (project_id, user_id),
FOREIGN KEY (project_id) REFERENCES projects(id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (invited_by) REFERENCES users(id)
);
Usage: Drives all permission checks. A user's effective permission on a document is the highest
of their org role and their project role. manager can submit for review and publish. editor can
write. reviewer can only annotate and decide. viewer is read-only.
3. Template Library
sql
CREATE TABLE template_categories (
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL, -- "Requirements & Specifications"
phase
ENUM('initiation','requirements','design','development','testing','deployment','cross_cutting')
NOT NULL,
sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0
);
sql
CREATE TABLE templates (
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_id SMALLINT UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL, -- "Business Requirements Document"
short_code VARCHAR(20) NOT NULL UNIQUE, -- "BRD", "SRS", "TEST_PLAN"
description TEXT,
compliance_tags JSON, -- ["BABOK v3","IEEE 830","ISO 9001"]
estimated_minutes SMALLINT UNSIGNED, -- avg time to complete
is_active BOOLEAN NOT NULL DEFAULT TRUE,
sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES template_categories(id)
);
Usage: Defines the 50+ template types. Never mutated once published — treated as immutable
master definitions. short_code is used throughout the system (e.g., to identify BRD context
when building AI prompts for a linked SRS).
sql
CREATE TABLE template_sections (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
template_id SMALLINT UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL, -- "1. Business Objectives"
description TEXT, -- guidance text shown above section
sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
is_required BOOLEAN NOT NULL DEFAULT TRUE,
FOREIGN KEY (template_id) REFERENCES templates(id)
);
sql
CREATE TABLE template_fields (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
section_id INT UNSIGNED NOT NULL,
field_key VARCHAR(100) NOT NULL, -- "project_name", "stakeholder_list"
label VARCHAR(255) NOT NULL,
field_type ENUM(
'text_short',
'text_long',
'date',
'dropdown',
'multi_select',
'person_select',
'table',
'signature',
'rich_text',
'auto_id' -- auto-generates BR-001, TC-001 etc.
) NOT NULL,
options JSON, -- for dropdown/multi_select: ["Low","Medium","High"]
table_schema JSON, -- for table fields: column definitions
is_required BOOLEAN NOT NULL DEFAULT FALSE,
is_ai_fillable BOOLEAN NOT NULL DEFAULT TRUE,
ai_context_hint TEXT, -- prompt hint: "Use the project's primary client name"
validation_rule VARCHAR(255), -- regex or rule name: "email", "date_future"
sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0,
FOREIGN KEY (section_id) REFERENCES template_sections(id)
);
Usage: This is the schema of each template — the blueprint for what fields exist, their types,
and how the AI should approach them. ai_context_hint is injected into the context assembler
prompt for that specific field to guide Claude's inference. auto_id type fields automatically
generate compliance-formatted IDs (BR-001, TC-001) without user input.
4. Document Instances
sql
CREATE TABLE documents (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
project_id BIGINT UNSIGNED NOT NULL,
template_id SMALLINT UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL,
status
ENUM('draft','in_review','revisions_requested','resubmitted','approved','published') NOT NULL
DEFAULT 'draft',
version_number SMALLINT UNSIGNED NOT NULL DEFAULT 1,
completion_pct TINYINT UNSIGNED NOT NULL DEFAULT 0, -- 0-100, recalculated on each
save
is_locked BOOLEAN NOT NULL DEFAULT FALSE, -- TRUE when published
lock_hash VARCHAR(64), -- SHA-256 of final content, set on publish
locked_at TIMESTAMP NULL,
locked_by BIGINT UNSIGNED NULL,
created_by BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL,
FOREIGN KEY (project_id) REFERENCES projects(id),
FOREIGN KEY (template_id) REFERENCES templates(id),
FOREIGN KEY (created_by) REFERENCES users(id),
FOREIGN KEY (locked_by) REFERENCES users(id)
);
Usage: One row per document instance. When a user clicks a template, this row is created.
lock_hash is the cryptographic fingerprint of the document's content at publish time — used to
prove the document has not been altered post-approval, satisfying ISO 9001 clause 7.5.
sql
CREATE TABLE document_field_values (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
document_id BIGINT UNSIGNED NOT NULL,
template_field_id INT UNSIGNED NOT NULL,
value_text TEXT, -- for text, rich_text, date, dropdown
value_json JSON, -- for table fields, multi_select, person_select
source ENUM('human','ai','system') NOT NULL DEFAULT 'human',
ai_model VARCHAR(100), -- "claude-sonnet-4-6" — NULL if source = human
ai_confidence DECIMAL(4,3), -- 0.000 to 1.000 — NULL if source = human
ai_run_id BIGINT UNSIGNED, -- links to the ai_fill_runs table
is_ai_accepted BOOLEAN, -- TRUE = user clicked Accept, FALSE = rejected/overridden
accepted_by BIGINT UNSIGNED NULL,
accepted_at TIMESTAMP NULL,
created_by BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
UNIQUE KEY uq_doc_field (document_id, template_field_id),
FOREIGN KEY (document_id) REFERENCES documents(id),
FOREIGN KEY (template_field_id) REFERENCES template_fields(id),
FOREIGN KEY (created_by) REFERENCES users(id),
FOREIGN KEY (accepted_by) REFERENCES users(id)
);
Usage: The single most important table. Every field in every document stores its current live
value here. The UNIQUE KEY on (document_id, template_field_id) ensures one value per field
— updates use ON DUPLICATE KEY UPDATE. The full history lives in document_field_audit
below, keeping this table lean for real-time editor reads.
sql
CREATE TABLE document_field_audit (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
document_id BIGINT UNSIGNED NOT NULL,
template_field_id INT UNSIGNED NOT NULL,
previous_value_text TEXT,
previous_value_json JSON,
new_value_text TEXT,
new_value_json JSON,
change_source ENUM('human','ai','system') NOT NULL,
ai_model VARCHAR(100),
ai_confidence DECIMAL(4,3),
actor_id BIGINT UNSIGNED NULL, -- NULL for system changes
actor_label VARCHAR(255), -- "Sarah Mitchell" or "AI Auto-Fill"
document_version SMALLINT UNSIGNED NOT NULL,
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_document_audit (document_id, changed_at),
FOREIGN KEY (document_id) REFERENCES documents(id),
FOREIGN KEY (template_field_id) REFERENCES template_fields(id)
);
Usage: Append-only audit log. Never updated, never deleted. Every time
document_field_values changes, a trigger (or application-layer hook) writes the before/after to
this table. This is the ISO 9001 traceability record and the source for the diff view shown to
reviewers during resubmission.
5. AI Fill Engine
sql
CREATE TABLE ai_fill_runs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
document_id BIGINT UNSIGNED NOT NULL,
triggered_by BIGINT UNSIGNED NOT NULL, -- user who opened the doc
model VARCHAR(100) NOT NULL DEFAULT 'claude-sonnet-4-6',
context_sources JSON, -- [{"type":"project","id":12},
{"type":"document","id":45,"short_code":"BRD"}]
prompt_tokens SMALLINT UNSIGNED,
completion_tokens SMALLINT UNSIGNED,
fields_attempted TINYINT UNSIGNED,
fields_filled TINYINT UNSIGNED, -- confidence >= 0.75
fields_suggested TINYINT UNSIGNED, -- confidence 0.40–0.74
fields_skipped TINYINT UNSIGNED, -- confidence < 0.40
duration_ms SMALLINT UNSIGNED,
status ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
error_message TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (document_id) REFERENCES documents(id),
FOREIGN KEY (triggered_by) REFERENCES users(id)
);
Usage: One row per AI auto-fill execution. Lets the product team monitor AI quality, token costs
per document type, and per-template fill rates. context_sources is the JSON record of exactly
which project/document data was fed into the prompt — shown in the AI panel as "Context
used: Project Charter, SRS."
6. Approval & Review Workflow
sql
CREATE TABLE review_cycles (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
document_id BIGINT UNSIGNED NOT NULL,
round_number TINYINT UNSIGNED NOT NULL DEFAULT 1,
submitted_by BIGINT UNSIGNED NOT NULL,
submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
due_date DATE,
lock_during_review BOOLEAN NOT NULL DEFAULT FALSE,
status ENUM('open','completed','cancelled') NOT NULL DEFAULT 'open',
completed_at TIMESTAMP NULL,
FOREIGN KEY (document_id) REFERENCES documents(id),
FOREIGN KEY (submitted_by) REFERENCES users(id)
);
sql
CREATE TABLE review_cycle_reviewers (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
review_cycle_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NULL, -- NULL for external reviewers
external_email VARCHAR(255), -- populated when user_id is NULL
external_name VARCHAR(255),
reviewer_type ENUM('must_approve','optional') NOT NULL DEFAULT 'must_approve',
access_token VARCHAR(128), -- signed JWT for external link access
token_expires_at TIMESTAMP,
decision ENUM('pending','approved','changes_requested','rejected') NOT NULL DEFAULT
'pending',
decided_at TIMESTAMP NULL,
decision_note TEXT,
reminder_sent_at TIMESTAMP NULL,
FOREIGN KEY (review_cycle_id) REFERENCES review_cycles(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
Usage: Handles both internal users and external client reviewers in one table. External
reviewers get a signed access_token embedded in their email link — the API validates the token
and serves them a read-only view. reminder_sent_at is checked by a nightly cron job to fire
auto-reminders.
sql
CREATE TABLE review_comments (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
review_cycle_id BIGINT UNSIGNED NOT NULL,
document_id BIGINT UNSIGNED NOT NULL,
template_field_id INT UNSIGNED NULL, -- NULL = document-level comment
author_user_id BIGINT UNSIGNED NULL,
author_external_email VARCHAR(255),
author_display_name VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
is_resolved BOOLEAN NOT NULL DEFAULT FALSE,
resolved_by BIGINT UNSIGNED NULL,
resolved_at TIMESTAMP NULL,
parent_comment_id BIGINT UNSIGNED NULL, -- for threaded replies
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
FOREIGN KEY (review_cycle_id) REFERENCES review_cycles(id),
FOREIGN KEY (document_id) REFERENCES documents(id),
FOREIGN KEY (template_field_id) REFERENCES template_fields(id),
FOREIGN KEY (parent_comment_id) REFERENCES review_comments(id)
);
Usage: Field-anchored comments. When template_field_id is set, the comment renders inline
next to that specific field in the editor — not in a floating sidebar. The author's "Edit field"
button on the author dashboard is powered by this FK. parent_comment_id enables threaded
replies within a comment.
7. Export & Document Versions
sql
CREATE TABLE document_exports (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
document_id BIGINT UNSIGNED NOT NULL,
exported_by BIGINT UNSIGNED NOT NULL,
format ENUM('pdf','docx') NOT NULL,
file_url VARCHAR(500) NOT NULL, -- S3 key
file_size_bytes INT UNSIGNED,
document_version SMALLINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (document_id) REFERENCES documents(id),
FOREIGN KEY (exported_by) REFERENCES users(id)
);
sql
CREATE TABLE document_snapshots (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
document_id BIGINT UNSIGNED NOT NULL,
version_number SMALLINT UNSIGNED NOT NULL,
snapshot_json LONGTEXT NOT NULL, -- full document state at this version
created_by BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_doc_version (document_id, version_number),
FOREIGN KEY (document_id) REFERENCES documents(id),
FOREIGN KEY (created_by) REFERENCES users(id)
);
Usage: A full snapshot is written every time a review cycle begins and when a document is
published. This powers the diff view — the reviewer sees changes between version_number N-1
and N. Snapshots are stored as JSON rather than column-by-column so they're cheap to serialize
and compare.
8. Notifications
sql
CREATE TABLE notifications (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
organization_id BIGINT UNSIGNED NOT NULL,
recipient_user_id BIGINT UNSIGNED NULL, -- NULL for external email targets
recipient_email VARCHAR(255),
type ENUM(
'review_requested',
'review_decided',
'comment_added',
'comment_resolved',
'document_published',
'review_reminder',
'ai_fill_complete',
'member_invited'
) NOT NULL,
payload JSON, -- {"document_id":12,"document_title":"BRD
v1","review_cycle_id":3}
channel ENUM('email','slack','in_app') NOT NULL,
status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
sent_at TIMESTAMP NULL,
error_message TEXT,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (organization_id) REFERENCES organizations(id),
FOREIGN KEY (recipient_user_id) REFERENCES users(id)
);
Usage: All notifications flow through this queue. A background worker polls status = 'pending'
rows and dispatches via SendGrid (email) or Slack webhook. payload carries all data needed to
render the notification without additional DB queries — keeping the worker stateless.
9. Subscription & Billing
sql
CREATE TABLE subscriptions (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
organization_id BIGINT UNSIGNED NOT NULL UNIQUE,
plan ENUM('starter','business','enterprise') NOT NULL,
billing_cycle ENUM('monthly','annual'),
stripe_customer_id VARCHAR(100),
stripe_subscription_id VARCHAR(100),
current_period_start DATE,
current_period_end DATE,
seats_purchased SMALLINT UNSIGNED NOT NULL DEFAULT 5,
status ENUM('active','past_due','cancelled','trialing') NOT NULL DEFAULT 'trialing',
trial_ends_at TIMESTAMP NULL,
cancelled_at TIMESTAMP NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
FOREIGN KEY (organization_id) REFERENCES organizations(id)
);
Entity Relationship Summary
organizations
└── users (many)
└── projects (many)
└── project_members (many)
└── documents (many)
└── document_field_values (many, one per field)
└── document_field_audit (many, append-only history)
└── review_cycles (many, one per submission round)
│ └── review_cycle_reviewers (many)
│ └── review_comments (many, field-anchored)
└── document_snapshots (one per version)
└── document_exports (many)
└── ai_fill_runs (many)
└── subscriptions (one)
└── notifications (many)
templates (global, not org-scoped)
└── template_categories
└── template_sections
└── template_fields
Key Index Recommendations
sql
-- Fast document load (editor opening)
ALTER TABLE document_field_values ADD INDEX idx_document_fields (document_id);
ALTER TABLE review_comments ADD INDEX idx_doc_comments (document_id, is_resolved);
-- Audit log export queries
ALTER TABLE document_field_audit ADD INDEX idx_audit_doc_time (document_id,
changed_at);
-- Notification worker polling
ALTER TABLE notifications ADD INDEX idx_pending_notifications (status, created_at);
-- Review reminder cron
ALTER TABLE review_cycle_reviewers ADD INDEX idx_pending_reminders (decision,
reminder_sent_at);
-- AI run analytics
ALTER TABLE ai_fill_runs ADD INDEX idx_ai_document (document_id, created_at);
Design Decisions Worth Noting
Why separate document_field_values and document_field_audit? The values table is read on
every keystroke in the editor — it must stay small and indexed tightly. The audit table grows
unboundedly and is only read during compliance exports or diff views. Keeping them separate
avoids full-table scan penalties on the hot path.
Why store snapshot_json as LONGTEXT and not normalize it? Snapshots are write-once, read-
occasionally. Normalizing them would require 50+ row inserts per snapshot and complex
reconstruction queries just to render a diff. JSON serialization is the right trade-off here.
Why is templates not org-scoped? Templates are global master definitions maintained by the
DocForge team. Org-specific custom templates (roadmap, Enterprise tier) will be handled by a
custom_templates table that mirrors this structure but includes an organization_id FK.
Why actor_label VARCHAR in the audit table? External reviewers have no user_id. Storing their
display name directly ensures the audit log remains readable even if their token or email record
is later purged for GDPR compliance.