Document Management Data Model

Overview

Document Management provides centralized, polymorphic file storage for attaching documents to any entity in Client Processing. A single reusable table (file_upload) supports attachments across 18+ entity types (invoices, cash receipts, deals, settlements, etc.) without requiring entity-specific file tables.

Architecture

Key Design Decisions

Polymorphic Storage — Instead of maintaining separate file tables for invoice_files, cash_receipt_files, etc., a single file_upload table uses (entity_type, entity_id) to link files to any entity.

S3 Backend — Files are stored in S3 with database records tracking metadata, S3 location, and associations. The database holds references only; the actual file content lives in cloud storage.

Pending Upload Pattern — Files can be uploaded before the parent entity exists. A pending_entity_token (UUID) holds the file; once the parent entity is created, a link operation associates the file to it.

PDF Slicing — Supports extracting page ranges from PDF files to create entity-specific slices without duplicating storage (via parent_file_upload_id self-reference and page_range_start/end).

---

2. Data Model

2.1 Entity-Relationship Diagram

erDiagram
    file_upload ||--o| party : "client_id FK"
    file_upload ||--o| uta_entity : "uta_entity_id FK"
    file_upload ||--o{ file_upload : "parent_file_upload_id (slices)"
    
    cash_receipt ||--o{ file_upload : "entity_type='cash_receipt'"
    cash_receipt_worksheet ||--o{ file_upload : "entity_type='cash_receipt_worksheet'"
    deal ||--o{ file_upload : "entity_type='deal'"
    party ||--o{ file_upload : "entity_type='party'"
    invoice ||--o{ file_upload : "entity_type='invoice'"
    settlement_packet ||--o{ file_upload : "entity_type='settlement_packet'"
    write_off_packet ||--o{ file_upload : "entity_type='write_off_packet'"

---

2.2 file_upload (Polymorphic File Storage)

Central table for storing file metadata and associations. One row per file upload, regardless of how many pages or how it's used downstream.

Field	Type	Required	Default	Description
file_upload_id	serial	Yes	Auto	Primary key. Referenced by slices (self-join) and workflows.
S3 Storage
s3_bucket	varchar(100)	Yes	—	S3 bucket name (e.g., client-processing-docs-prod).
s3_key	varchar(500)	Yes	—	S3 object key/path (e.g., invoices/2026-05/inv-123.pdf). Unique across bucket.
File Metadata
original_file_name	varchar(255)	Yes	—	Original filename as uploaded (e.g., invoice-2026-01.pdf). Used for download.
file_size_bytes	bigint	Yes	—	File size in bytes. Used for quota enforcement and download progress.
mime_type	varchar(100)	Yes	—	MIME type (e.g., application/pdf, image/jpeg, text/csv). Determines preview capability.
content_category	varchar(50)	No	—	Normalized content category: document, image, spreadsheet, archive, other. Auto-derived from mime_type by getContentCategory() helper.
Polymorphic Association
entity_type	varchar(50)	Yes	—	Type of entity this file is attached to (e.g., cash_receipt, deal, party, invoice). See Section 4 for full list.
entity_id	varchar(255)	No	—	ID in the referenced entity table (e.g., cash_receipt_id, deal_id). Nullable to support pending uploads. Supports both integer IDs (cast to varchar) and UUIDs.
Pending Upload Pattern
pending_entity_token	varchar(36)	No	—	UUID token for files uploaded before the parent entity exists. Once the entity is created, call linkPendingFiles(token, entity_type, entity_id) to establish the permanent association. Mutually exclusive with entity_id (one is null, one is populated).
PDF Slicing / Extraction
parent_file_upload_id	integer	No	—	FK to file_upload.file_upload_id for the original file. When a PDF is sliced and pages are extracted to a new file, this points back to the source. Null for non-sliced files.
page_range_start	integer	No	—	First page (1-indexed) of the slice. Null for full files. Used to reconstruct the slice range when displaying or regenerating.
page_range_end	integer	No	—	Last page (1-indexed) of the slice (inclusive). Null for full files.
Domain Context
client_id	integer	No	—	FK to party.party_id. The client (artist/entity) this file relates to. Used for filtering and access control. May be null for files not tied to a specific client (e.g., bank statements).
uta_entity_id	integer	No	—	FK to uta_entity.uta_entity_id. The UTA legal entity owning this file. Used for access control and reporting scopes.
Flexible Metadata
metadata	text (JSON)	No	—	JSONB-encoded key-value pairs for entity-specific or use-case-specific metadata. Example: { "invoice_number": "INV-2026-001", "tax_form_type": "W-9", "bank_statement_provider": "BOFA" }. Supports arbitrary attributes without schema changes.
User Info & Audit
description	varchar(500)	No	—	Optional user-provided description (e.g., "Q4 2025 cash receipts"). Shown in file list UI.
uploaded_by	varchar(100)	No	—	User ID of the uploader.
uploaded_dt	timestamp	Yes	NOW()	Timestamp when the file was uploaded.
Soft Delete
active_ind	boolean	No	true	true = file is active; false = file is logically deleted. Hard deletes are not used; deletion is soft to preserve audit trail and handle undelete scenarios.
Audit Columns
created_by, created_dt	varchar(100), timestamp	—	NOW()	Record creation audit.
updated_by, updated_dt	varchar(100), timestamp	—	NOW()	Last modification audit.

Primary Key: file_upload_id

Unique Constraints:

• (s3_bucket, s3_key) — Prevents duplicate S3 objects (one row per S3 location)

Indexes:

• (entity_type, entity_id) — Fast lookup of all files for a given entity
• (parent_file_upload_id) — Fast lookup of slices for a parent file
• (client_id) — Fast lookup by client for access control and filtering
• (uta_entity_id) — Fast lookup by UTA entity for multi-entity scoping
• (pending_entity_token) — Fast lookup of pending files by UUID token

2.3 Relations & Self-References

The file_upload table defines Drizzle ORM relations:

fileUploadRelations = relations(fileUpload, ({ one, many }) => ({
  client: one(party, {...}),           // FK to client_id
  utaEntity: one(utaEntity, {...}),    // FK to uta_entity_id
  parentFile: one(fileUpload, {...}),  // Self-join: parent_file_upload_id
  slices: many(fileUpload, {...}),     // Self-join: child slices
}));

Use in queries:

• await query.file_upload.with({ client: true }) — Eager-load client details
• await query.file_upload.with({ slices: true }) — Eager-load all page slices of a PDF

---

3. Supported Entity Types

Files can be attached to the following entity types. Expand as new features require document attachments.

Entity Type	Example Entity	Use Cases
cash_receipt	Cash receipt record	Bank statement excerpts, payment proofs, check images
cash_receipt_match	Manual cash match	Reference documentation for matched payments
cash_receipt_worksheet	Worksheet (grouped cash)	Supporting receipts, internal memos, correspondence
cash_receipt_statement	AI-parsed bank statement	Extracted statements for split generation
cash_receipt_split	Individual split (applied to invoice)	Split justification, email confirmations, invoice scans
payment_item	Outbound payment record	Remittance copies, wire instructions, approval docs
deal	Deal / agreement	Signed contracts, amendments, SOW (Statement of Work)
party	Client, agent, vendor	ID documents, tax forms (W-9, W-8BEN), bank account verification
billing_item	Invoice header or line	Invoice template, internal billing memo, amendments
invoice	Generated invoice	Source POs, rate cards, payment instructions
participant_settlement	Participant payout settlement	Settlement statement, tax forms, authorization docs
generated_statement	Batch statement report	Generated PDF statement, sent to client
deposit	Bank deposit record	Deposit slip, clearing details
write_off_packet	Write-off batch (header)	Approval memo, audit summary, collection notes
write_off_packet_receivable	Individual write-off line	Age analysis, legal correspondence, recovery notes
profit_statement	Gross profit statement	Source contracts, rate documentation, accrual memos
package_rebate_request	Rebate claim request	Claim justification, supporting contracts, budget docs
booking_project_header	Booking (project header)	Project charter, participant agreements, guild forms
settlement_packet	Settlement calculation batch	Settlement run log, reconciliation, approval docs

Adding New Entity Types:

1. Add the entity_type string to the FILE_UPLOAD_ENTITY_TYPES const in code
2. Update this table with the new entity and use cases
3. Add index on the new entity table if frequent queries by entity occur
4. No database migration needed — entity types are open-ended

---

4. Content Categories

Files are automatically categorized by MIME type to support smart previewing and filtering.

Category	MIME Types	Extensions	UI Behavior
document	application/pdf, application/msword, text/*	PDF, DOC, DOCX, TXT	Inline PDF preview; Word docs download-only
image	image/*	JPG, JPEG, PNG, GIF, BMP	Inline image thumbnail + lightbox
spreadsheet	application/vnd.ms-excel, text/csv, *spreadsheet*	XLS, XLSX, CSV	Spreadsheet icon; download-only
archive	application/zip, application/rar	ZIP, RAR, 7Z	Archive icon; download-only
other	(all other MIME types)	All others	Generic file icon; download-only

Helper function (in code):

function getContentCategory(mimeType: string): FileContentCategory {
  // Examines MIME type and returns category
  // Used during upload to auto-populate content_category
}

---

5. Workflows & Common Operations

5.1 Upload a New File (Entity Already Exists)

Inputs: file (binary), entity_type, entity_id, description (optional)

Steps:

1. Validate file size (max 50 MB, configurable)
2. Validate MIME type against allowed list
3. Generate S3 key (e.g., {entity_type}/{fiscal_period}/{timestamp}_{filename})
4. Upload to S3
5. Insert into file_upload with:
   • s3_bucket, s3_key (from S3 response)
   • original_file_name, file_size_bytes, mime_type
   • content_category (from getContentCategory(mime_type))
   • entity_type, entity_id (from input)
   • client_id, uta_entity_id (optional, for scoping)
   • uploaded_by (current user), uploaded_dt (now)
6. Return file_upload_id + S3 signed URL for download

Outcome: File immediately available for preview/download linked to the entity

---

5.2 Upload a Pending File (Entity Does Not Yet Exist)

Inputs: file (binary), entity_type (declared, not yet instantiated), description (optional)

Steps:

1. Generate a pending_entity_token (UUID v4)
2. Upload file to S3 (as above)
3. Insert into file_upload with:
   • s3_bucket, s3_key (from S3)
   • entity_type (from input)
   • entity_id = NULL
   • pending_entity_token = generated UUID
   • All other metadata as usual
4. Return pending_entity_token to the client (e.g., store in form state)

Outcome: File is uploaded but not yet associated. Later, when the entity is created:

---

5.3 Link Pending Files (After Parent Entity Created)

Inputs: pending_entity_token, entity_id (newly created)

Steps:

1. Query file_upload where pending_entity_token = :token
2. For each pending file:
   • Update: entity_id = :entity_id, pending_entity_token = NULL
   • Validate no duplicate associations
3. Return count of linked files

Outcome: Pending files now permanently associated with the entity

---

5.4 Download a File

Inputs: file_upload_id

Steps:

1. Query file_upload where file_upload_id = :id
2. Verify user has access (client scope, uta_entity scope)
3. Generate AWS S3 presigned URL (valid for 15 min)
4. Return URL + original filename
5. Client browser downloads via presigned URL

Outcome: User downloads file from S3; no database storage bandwidth used

---

5.5 Slice a PDF (Extract Page Range)

Inputs: parent_file_upload_id, page_range_start, page_range_end, entity_type (for new slice), entity_id (for new slice)

Steps:

1. Query parent file: file_upload where file_upload_id = :parent_id
2. Validate parent is a PDF (mime_type = 'application/pdf')
3. Download PDF from S3
4. Extract pages [start, end] using PDF library (pypdf2, pdfplumber, etc.)
5. Upload extracted PDF to S3 with new key (e.g., {parent_key}_pages_{start}_{end})
6. Insert new file_upload row with:
   • parent_file_upload_id = :parent_id
   • page_range_start = :start, page_range_end = :end
   • entity_type, entity_id (new association)
   • All other metadata
7. Return new file_upload_id

Outcome: New file record created pointing back to parent; both are queryable

---

5.6 Delete a File (Soft Delete)

Inputs: file_upload_id

Steps:

1. Update file_upload set active_ind = false where file_upload_id = :id
2. Optionally: Archive the S3 object (move to Glacier for long-term retention)
3. Return success

Outcome: File logically deleted; not shown in UI; S3 object retained for audit

---

6. Access Control & Scoping

Query Filtering by User Scope

Files should be filtered based on user's data scope:

1. Client Scope: If user can only access certain clients, filter: client_id IN (user's accessible client_ids)
2. UTA Entity Scope: If user can only access certain UTA entities, filter: uta_entity_id IN (user's accessible uta_entity_ids)
3. Entity Scope: If user's access is scoped to specific entities (e.g., only worksheets they created), filter: entity_type IN (...) AND entity_id IN (...)

Example Query:

SELECT * FROM file_upload
WHERE entity_type = 'invoice' AND entity_id = 12345
  AND (client_id IS NULL OR client_id IN (user's clients))
  AND (uta_entity_id IS NULL OR uta_entity_id IN (user's entities));

---

7. Audit & Compliance

All file uploads are immutably recorded:

• uploaded_by — User who uploaded
• uploaded_dt — When uploaded
• created_by, created_dt — Record insertion
• updated_by, updated_dt — Last modification (soft delete)
• active_ind — Soft delete flag (not hard-deleted)

Retention Policy (pending implementation):

• Active files: Kept indefinitely
• Deleted files (active_ind = false): Retained in database for 7 years for audit/compliance
• S3 Archival: Move deleted files to Glacier after 90 days

---

8. Design Decisions Pending (Jira Tasks)

CP-E2-001 — File Upload Data Model (This Document)

Status: DOCUMENTED
Architecture and schema for polymorphic file storage with S3 backend.

CP-E2-002 — File Upload Service API & Operations

Status: PENDING DESIGN
Define the service layer (create, download, slice, delete operations):

• API endpoints for upload/download/preview
• Batch operations (link multiple pending files)
• Presigned URL generation and expiry
• File size limits and quota enforcement
• Virus scanning integration (ClamAV or equivalent)

CP-E2-003 — File Association & Link Resolution

Status: PENDING DESIGN
Define how files are discovered and linked to entities:

• Query optimization for "all files for entity"
• Pagination for large file lists (1000+ files)
• Search/filter by filename, date range, content category
• Dependency handling (what happens when an entity is deleted?)

---

9. References

Document	Relationship
Cash Receipts Data Model	cash_receipt, cash_receipt_split, cash_receipt_worksheet entities use file_upload for statements and correspondence
Invoices & Statements Data Model	invoice, generated_statement entities link to files
Write-Offs Data Model	write_off_packet attachments via file_upload
Jira Tasks	CP-E2-001, CP-E2-002, CP-E2-003

---

10. Implementation Notes

• S3 Configuration: Bucket name, region, and presigned URL expiry are environment variables, not hard-coded
• MIME Type Validation: Whitelist allowed types (PDF, images, spreadsheets); reject executables and scripts
• Concurrent Uploads: Use optimistic locking (row versioning) to prevent race conditions during link operations
• Soft Delete Immutability: Once active_ind = false, do not re-activate; create a new file record instead
• JSONB Indexing: For frequently-searched metadata fields, create expression indexes (e.g., (metadata->>'invoice_number'))