93 lines
4.2 KiB
Markdown
93 lines
4.2 KiB
Markdown
# Document Data Model
|
||
|
||
This note describes the core persistence model for documents: the metadata held in
|
||
`documents`, how versions are tracked, and the way auxiliary assets are stored.
|
||
|
||
## documents
|
||
|
||
Each row represents the logical document a user interacts with in the UI. Key
|
||
fields:
|
||
|
||
- `id (uuid)` – Stable identifier used in API paths.
|
||
- `tenant_id (uuid)` – Multi-tenancy boundary; all joins filter by this.
|
||
- `title (varchar)` – Display name editable via PATCH.
|
||
- `filename / original_name (varchar)` – Current storage filename vs. the name
|
||
captured during upload.
|
||
- `folder_id (uuid, nullable)` – Parent folder, `NULL` means root.
|
||
- `metadata (jsonb)` – Arbitrary structured metadata (source import details,
|
||
custom fields, etc.).
|
||
- `issued_at (timestamptz, nullable)` – User-provided timestamp for when the
|
||
document was issued (invoice date, etc.).
|
||
- `current_version_id (uuid)` – FK pointing at the active `document_versions`
|
||
row; updated whenever a new version is promoted.
|
||
- `deleted_at (timestamptz, nullable)` – Soft-delete marker; non-NULL rows are
|
||
treated as living in the trash.
|
||
- `created_at / updated_at (timestamptz)` – Audit stamps; `updated_at` reflects
|
||
metadata or version changes.
|
||
|
||
Other indexes enforce per-tenant uniqueness for `(folder, filename)` and support
|
||
common queries (folder listing, trash filtering).
|
||
|
||
## document_versions
|
||
|
||
Every binary revision lives here. Fields of interest:
|
||
|
||
- `document_id (uuid)` – Back-reference to the logical document.
|
||
- `version_number (int)` – Monotonic per document (1, 2, …); enforced via
|
||
`UNIQUE(document_id, version_number)`.
|
||
- `s3_key (varchar)` – Object storage path for the binary (used for download).
|
||
- `size_bytes`, `checksum` – Stored metadata about the binary; checksum is a
|
||
hex-encoded SHA-256 hash used for dedupe/conflicts.
|
||
- `metadata (jsonb)` – Small metadata blob specific to the version (extracted
|
||
text summary, processing hints, etc.).
|
||
- `tenant_id (uuid)` – Mirrors the owning document’s tenant.
|
||
|
||
The row referenced by `documents.current_version_id` is treated as the latest
|
||
revision. Older versions remain queryable for download or audit.
|
||
|
||
## Assets
|
||
|
||
A document version can have zero or more derived artifacts (thumbnails, OCR
|
||
output, previews). These are modelled via:
|
||
|
||
- `document_assets`
|
||
- `document_version_id` – FK to the owning version.
|
||
- `asset_type (text)` – Logical type identifier (e.g. `thumbnail`, `ocr_text`).
|
||
- `mime_type (text)` – Media type for consumers.
|
||
- `metadata (jsonb)` – Asset-specific metadata (dimensions, page count, etc.).
|
||
- `cardinality (int, nullable)` – Optional hint for multi-object assets.
|
||
- `tenant_id (uuid)` – Tenant scoping.
|
||
- Uniqueness on `(document_version_id, asset_type)` ensures one logical asset
|
||
per type; multi-object cases are stored in `document_asset_objects`.
|
||
|
||
- `document_asset_objects`
|
||
- `asset_id` – FK to `document_assets`.
|
||
- `ordinal (int)` – 1-based position for multi-part assets.
|
||
- `s3_key (text)` – Object storage key for the binary blob.
|
||
- `metadata (jsonb)` – Per-object metadata if needed (e.g. page number).
|
||
|
||
Simple assets (single thumbnail) live solely in `document_assets`. Complex ones
|
||
(e.g. per-page previews) use `document_asset_objects` to point at multiple S3
|
||
objects under a single logical asset.
|
||
|
||
## Related tables
|
||
|
||
- `document_tags` and `document_correspondents` provide many-to-many
|
||
relationships for categorisation.
|
||
- `jobs` records background work (OCR, thumbnails, indexing) keyed by tenant.
|
||
- `api_tokens`, `user_sessions`, and `user_passkeys` live alongside but do
|
||
not alter the document schema directly.
|
||
|
||
## Lifecycle summary
|
||
|
||
1. Upload creates a `documents` row and an initial `document_versions` entry.
|
||
2. Workers generate derived assets, inserting rows into `document_assets`
|
||
(and possibly `document_asset_objects`).
|
||
3. When a new version is promoted, a fresh `document_versions` row is written
|
||
and `documents.current_version_id` is updated atomically.
|
||
4. Soft-deleting the document sets `deleted_at`; restore clears it and the
|
||
document reappears in listings.
|
||
|
||
This schema allows arbitrary metadata expansion while maintaining a clear
|
||
separation between logical documents, their version history, and derived assets.
|