Database Schema: Document Module
This document details the database structure for the Document module.
1. Overview
The Document module uses PostgreSQL with Drizzle ORM. It stores document content as structured JSONB to preserve TipTap editor state and history.
Core Domain Model
2. Table Definitions
documents
Core metadata for a collaborative document.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
org_id | varchar | Organization scope. |
title | varchar | Document title. |
status | enum | draft, in_review, published. |
current_version_id | uuid | Reference to the latest document_versions.id. |
document_versions
Historical snapshots of document content.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
document_id | uuid | FK to documents.id. |
version_number | integer | Incremental version counter. |
content | jsonb | TipTap-compatible JSON structure. |
approval_requests
Workflow tracking for publication.
| Field | Type | Description |
|---|---|---|
id | uuid | PK. |
document_id | uuid | FK to documents.id. |
status | enum | pending, approved, changes_requested. |
reviewer_id | varchar | Shell User ID assigned to review. |
3. Enumerated Types
document_status
draft: Open for editing by the author.in_review: Locked for editing; awaiting approval.published: Read-only (unless a new draft version is created).
approval_status
pending: Awaiting reviewer action.approved: Workflow completed successfully.changes_requested: Reviewer sent back for revisions.