Time, flexible payloads, files, and generated artifacts are where otherwise clean models become ambiguous. Store instants separately from local scheduling rules, model history according to its questions, use JSONB for genuine variability, keep large file bytes in suitable object storage when appropriate, and give every derived value source lineage and a rebuild path.
Ask four time questions: when did it happen, when was it true in the business, when did we learn it, and in which local rules should it display or recur? One timestamp rarely answers all four.
For bitemporal requirements, store effective validity and recorded/system validity separately; accept the extra query and correction complexity only when the business needs it.
Use JSONB for provider payload snapshots or variable extracted metadata, then constrain required shape where feasible and promote commonly governed facts:
For files, PostgreSQL can store bytea, but large content commonly belongs in object storage. Store stable object key, size, media type, checksum, tenant, encryption/retention state, and source version in PostgreSQL. Do not store a public URL as identity.
Derived artifacts need columns such as source_version_id, source_checksum, generator_version, status, created_at, and failure details. “Current” should be a queryable state, not inferred from the newest timestamp alone.
Chunks, embeddings, OCR text, classifications, and summaries are derived from a specific source version through a specific pipeline. Without checksums and versions, stale artifacts look valid. Deletion must propagate from source to chunks, vectors, caches, evaluations, and citations according to policy.
Model two versions of one knowledge document and attach chunks to each version. Query only the active version, then deactivate it and prove old chunks are excluded without deleting lineage. Test a daylight-saving boundary for one supported business zone.
Design knowledge_documents and knowledge_document_versions plus external file metadata. Add states for uploading, ready, failed, superseded, and deleted.
Acceptance criterion: exactly one active ready version can be selected, failed uploads cannot be retrieved, and orphan cleanup is identifiable.