files
This table is the central metadata registry for uploaded and generated files in DirtView. It stores the database record for a file while the binary content itself lives in object storage.
Purpose
The purpose of this table is to store the structured metadata needed to manage files safely and consistently across the platform, including uploads, generated PDFs, drawing assets, certifications, photos, and thumbnails.
What this table does
- Stores file metadata independently from the storage provider
- Tracks who uploaded the file and which tenant/project it belongs to
- Supports access control and file linking to business records
- Tracks scan status and categorization for safe use in workflows
- Provides a stable DB identity for files used throughout the system
Why this table is defined
DirtView needs a database-level representation of files because storage buckets alone are not enough. The system must know who owns a file, what it is used for, whether it passed virus scanning, and which records can access it.
Columns
| Column | Type | Required | Description | Example |
|---|---|---|---|---|
id |
uuid | Yes | Primary key for the file record | file_001 |
tenant_id |
uuid | Yes | Tenant that owns the file | tenant_001 |
project_id |
uuid | No | Project associated with the file, if project-scoped | project_phoenix |
uploaded_by |
uuid | No | User who uploaded or generated the file | user_123 |
file_name |
text | Yes | Internal or normalized file name | rev3-architectural-set.pdf |
original_file_name |
text | Yes | Name of the file as originally uploaded | Architectural Rev 3.pdf |
mime_type |
text | No | Detected MIME type | application/pdf |
size_bytes |
bigint | No | File size in bytes | 24893210 |
bucket_category |
text | Yes | Logical storage category | drawings |
storage_bucket |
text | Yes | Underlying storage bucket name | dirtview-files |
storage_path |
text | Yes | Full object storage path | tenant_001/drawings/project_phoenix/rev3.pdf |
folder_path |
text | No | Optional logical folder path for document UIs | /Project Documents/Contracts |
scan_status |
text | Yes | Malware/virus scan status | clean |
sha256 |
text | No | Content hash for integrity or deduplication | abc123... |
thumbnail_of_file_id |
uuid | No | If this file is a thumbnail, points to the original file | file_000 |
metadata |
jsonb | Yes | Flexible file metadata | {"page_count": 42} |
created_at |
timestamptz | Yes | Creation timestamp | 2026-04-11 10:00:00+00 |
deleted_at |
timestamptz | No | Soft-delete timestamp | NULL |
Relationships
- tenant_id → tenants.id
- project_id → projects.id
- id is referenced by file_links.file_id
- id is referenced by file_scan_results.file_id
- thumbnail_of_file_id self-references another file record
How it is used
- Created after upload or generation of a file
- Used to resolve signed URLs and file metadata in the app
- Used in document lists, photo galleries, certifications, and drawing pipelines
- Acts as the anchor for scan results and resource links
Access and security
- Access must respect tenant and project scope
- Files should not be downloadable until scan policy allows it
- Signed URL generation should be done server-side
- Storage path conventions should follow tenant isolation rules
Example scenarios
Scenario 1: Drawing upload
A PDF drawing set is uploaded. A row is created here before OCR and splitting begin.
Scenario 2: Certification document
An employee certification scan is uploaded and tracked as a file linked to a certification record.
Scenario 3: Generated PDF
A branded PDF export is generated by the backend and stored as a file record.
Notes and assumptions
- This table stores metadata, not the binary file itself
- Use storage paths that include tenant prefixes
- Keep category naming consistent across upload pipelines