Database Schema
This document defines the initial database schema model for WildEditorInChief (WEIC).
The schema translates the WEIC document, revision, fragment, review, publication, and tagging models into concrete relational structures that can be implemented first in SQLite and later in PostgreSQL.
The schema is intended to preserve stable identity, revision-first content storage, reusable fragments, governed review, and controlled publication.
Purpose
The database schema exists to support several goals:
- turn the WEIC data model into a buildable relational design
- preserve stable identity separate from changing content
- support revision-first storage of HTML content
- support reusable fragments with the same revision model
- support tagging, review, and publication as first-class data
The schema should remain compatible with both SQLite development and PostgreSQL production.
Design principles
The schema follows several principles.
Stable identity
Documents and fragments have stable parent records that persist across revision changes.
Revision-first storage
Body content is stored in revision tables, not overwritten in place in parent records.
Canonical HTML
Canonical document and fragment content is stored as HTML in revision records.
Pointer-based current state
Current content state is represented by current_revision_id pointers.
Governance-ready structure
Review and publication records are separate tables so governance workflows remain explicit.
Core tables
The initial schema includes:
documentsdocument_revisionsfragmentsfragment_revisionstagsdocument_tagsreviewspublications
Future tables may include:
- attachments
- evidence links
- publication channels
- cross-document references
Documents
The documents table stores stable document identity and metadata.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| id | text | primary key |
| parent_id | text nullable | parent document id |
| title | text | document title |
| slug | text | logical or URL-safe name |
| owner | text | responsible owner |
| status | text | lifecycle status |
| current_revision_id | text nullable | active revision pointer |
| created_utc | text | creation timestamp |
| updated_utc | text | last update timestamp |
Suggested notes:
idshould be stable and application-generatedparent_idsupports hierarchyslugshould be unique within an appropriate scopecurrent_revision_idpoints todocument_revisions.id
Document revisions
The document_revisions table stores versioned document content.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| id | text | primary key |
| document_id | text | owning document id |
| body_html | text | canonical HTML body |
| revision_note | text nullable | optional change summary |
| author | text | revision author |
| created_utc | text | revision timestamp |
Suggested notes:
- one document may have many revisions
body_htmlis authoritative content- revisions should be treated as immutable records once created
Fragments
The fragments table stores stable fragment identity.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| id | text | primary key |
| name | text | fragment name |
| current_revision_id | text nullable | active fragment revision pointer |
| created_utc | text | creation timestamp |
| updated_utc | text | last update timestamp |
Suggested notes:
- fragments are reusable content units
- current state is represented by
current_revision_id
Fragment revisions
The fragment_revisions table stores versioned fragment content.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| id | text | primary key |
| fragment_id | text | owning fragment id |
| body_html | text | canonical HTML fragment body |
| revision_note | text nullable | optional change summary |
| author | text | revision author |
| created_utc | text | revision timestamp |
Suggested notes:
- fragment revisions mirror document revisions
- fragment content remains revisioned and traceable
Tags
The tags table stores reusable classification labels.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| id | text | primary key |
| name | text | tag name |
Suggested notes:
- tag names should be unique
- tags remain simple reusable labels
Document tags
The document_tags table stores many-to-many tag assignments.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| document_id | text | associated document |
| tag_id | text | associated tag |
Suggested notes:
- primary key should be composite: (
document_id,tag_id) - this avoids duplicate assignments
Reviews
The reviews table stores review decisions against specific revisions.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| id | text | primary key |
| target_type | text | document or fragment |
| target_revision_id | text | reviewed revision id |
| status | text | review status |
| reviewer | text | reviewer identity |
| created_utc | text | review creation timestamp |
| resolved_utc | text nullable | completion timestamp |
| review_note | text nullable | review comments |
Suggested notes:
- review attaches to a revision, not directly to mutable content
target_typedistinguishes document and fragment review records- allowed statuses should be constrained in application logic or database checks where practical
Publications
The publications table stores publication records for specific revisions.
Suggested fields:
| Field | Type | Purpose |
|---|---|---|
| id | text | primary key |
| target_type | text | document or fragment |
| target_id | text | published document or fragment id |
| revision_id | text | published revision id |
| published_by | text | actor or workflow identity |
| published_utc | text | publication timestamp |
| channel | text nullable | optional publication channel |
| publication_note | text nullable | optional publication note |
Suggested notes:
- publication records remain separate from revision records
- the published revision remains explicit and stable
Relationships
Core relationships:
documents
└─ many document_revisions
documents
└─ many document_tags
└─ one tags
fragments
└─ many fragment_revisions
document_revisions
└─ many reviews (where target_type = document)
fragment_revisions
└─ many reviews (where target_type = fragment)
documents or fragments
└─ many publications
Current revision pointers
The schema uses pointer-based current state.
Examples:
documents.current_revision_idfragments.current_revision_id
This allows:
- stable parent identity
- immutable revision history
- current state selection without overwriting historical content
The application should ensure that the pointed revision belongs to the correct parent record.
Suggested constraints
Recommended constraints include:
- primary keys on all main tables
- foreign keys where supported and practical
- unique tag names
- unique
document_tags(document_id, tag_id) - non-null required metadata fields
- check constraints on known status values where supported
SQLite and PostgreSQL differ in how strongly they enforce some behaviors, so application validation should still remain authoritative.
Search and indexing implications
Search should derive from current revision content.
A search indexing pipeline should:
- join parent records to current revision pointers
- parse
body_html - extract searchable text
- combine text with metadata such as title, slug, tags, owner, and status
Search indexes do not require separate Markdown source fields.
SQLite and PostgreSQL considerations
The schema should be implementable in SQLite first, but designed with PostgreSQL in mind.
Practical guidance:
- use text identifiers rather than engine-specific UUID dependence in early versions
- store timestamps in ISO 8601 UTC text form initially
- keep joins and constraints relationally clean
- avoid SQLite-specific shortcuts that would complicate later migration
This supports the planned migration path without changing the conceptual model.
Example table order
A practical implementation order is:
documentsdocument_revisionsfragmentsfragment_revisionstagsdocument_tagsreviewspublications
This supports incremental buildout of the WEIC platform.
Relationship to the Oryvin plan
The database schema is one of the implementation bridges between the WEIC architecture and the running platform.
HTML storage model
↓
document and revision data model
↓
database schema
↓
API implementation and governed knowledge workflows
This is the point where the knowledge model becomes physically storable and queryable.