Overview
This site documents the DirtView database schema, including table purpose, columns, relationships, security expectations, and operational behavior.
Core Concepts
Start here if you are new to the schema or need the shared mental model before reading individual table pages.
Conventions
Shared modeling rules, RLS expectations, naming standards, JSONB policy, immutability, and audit rules.
Glossary
Definitions for terms like tenant, membership, project member, drawing set, callout, immutable, and more.
Tenant Model
The root of the shared-schema multi-tenant architecture.
Identity Model
How profiles, memberships, and employee records fit together.
RBAC Model
How roles, permissions, project overrides, and module access work.
Drawings Model
How drawing sets, versions, sheets, annotations, and callouts are structured.
Workflow Model
How RFIs, forms, and notifications are represented in the database.
Audit Model
How tenant and platform actions are recorded for trust, support, and compliance readiness.
Global Admin Layer
| Table | Scope | Purpose | Status |
|---|---|---|---|
|
super_admins
Global
Reviewed
|
Global | Internal DirtView platform operators with cross-tenant administrative power | Reviewed |
|
admin_audit_log
Global
Immutable
Reviewed
|
Global | Append-only audit trail of all super-admin actions | Reviewed |
|
feature_flags
Global
Tenant Override
Proposed
|
Global / Tenant | Feature toggles for controlled rollout, operational control, and tenant-specific overrides | Proposed |
Tenant & Identity
| Table | Scope | Purpose | Status |
|---|---|---|---|
|
tenants
Tenant
Proposed
|
Tenant | Customer organizations and tenant-level configuration root | Proposed |
|
profiles
Global Identity
Proposed
|
Global User Profile | Base authenticated person profile | Proposed |
|
tenant_memberships
Tenant
Proposed
|
Tenant | User membership inside a specific tenant | Proposed |
|
employees
Tenant
Planned
|
Tenant | Business employee records that may or may not map to login-enabled users | Planned |
RBAC
| Table | Scope | Purpose | Status |
|---|---|---|---|
| roles | Tenant | Tenant-defined role catalog | Proposed |
| permissions | Global Catalog | Atomic permission definitions | Proposed |
| role_permissions | Tenant | Role-to-permission mapping | Proposed |
| user_company_roles | Tenant | Tenant-level default role assignment | Proposed |
| project_members | Project | Project membership access boundary | Proposed |
| user_project_roles | Project | Project-level role overrides | Proposed |
| project_module_access | Project / Module | Fine-grained module access inside a project | Proposed |
Projects
| Table | Scope | Purpose | Status |
|---|---|---|---|
| projects | Project | Project root record | Proposed |
| project_contacts | Project | Project contact directory | Proposed |
| distribution_groups | Tenant / Project | Reusable workflow notification groups | Proposed |
| distribution_group_members | Tenant / Project | Internal or external recipients in a group | Proposed |
Files & Drawings
| Table | Scope | Purpose | Status |
|---|---|---|---|
| files | Tenant / Project | File metadata registry | Proposed |
| file_links | Tenant | Associates files to resources | Proposed |
| file_scan_results | Tenant | Malware/virus scan history | Proposed |
| drawing_sets | Project | Logical grouping of drawing revisions | Proposed |
| drawing_versions | Project / Version | Uploaded revisions of a drawing set | Proposed |
| drawing_sheets | Project / Sheet | Confirmed sheets extracted from a version | Proposed |
| drawing_callouts | Project / Sheet | Detected or created drawing callouts | Proposed |
| drawing_annotations | Project / Sheet | User markups and measurement annotations | Proposed |
| drawing_sheet_links | Project / Sheet | Resolved links between sheets | Proposed |
Workflow Engine
| Table | Scope | Purpose | Status |
|---|---|---|---|
| rfis | Project | Main RFI business record | Proposed |
| rfi_threads | Project / RFI | RFI conversation and history entries | Proposed |
| form_templates | Tenant | Reusable form definitions | Proposed |
| form_submissions | Project | Draft or submitted form instances | Proposed |
| notifications | Tenant | Notification delivery records | Proposed |
Operations & Control
| Table | Scope | Purpose | Status |
|---|---|---|---|
|
audit_log
Tenant
Immutable
|
Tenant | Tenant-scoped audit history | Proposed |
| token_revocations | Tenant | Immediate access invalidation records | Proposed |
| background_job_runs | Global / Tenant | Application-visible async job execution history | Proposed |
| external_guest_invites | Tenant / Project | Invitations sent to outside collaborators | Proposed |
| external_guest_access_grants | Tenant / Project | Scoped external access rules after invite acceptance | Proposed |