DirtView Schema Docs

1. Scope model

DirtView tables generally fall into one of four scope types:

2. tenant_id convention

Every tenant-owned business table should carry a tenant_id column. This is a core rule of the shared-schema multi-tenant design.

Rules

• All tenant-scoped rows must be attributable to exactly one tenant.
• tenant_id should be indexed on all tenant-scoped tables.
• If a row is project-scoped, it should usually carry both tenant_id and project_id.
• Global internal admin tables should not carry tenant_id.

Examples

• projects.tenant_id → identifies the owning tenant
• rfis.tenant_id → ensures tenant-safe filtering and RLS
• admin_audit_log → no tenant_id, because it is global

3. Identity model

DirtView separates identity into layers:

A single person may have one profile, multiple tenant memberships, and possibly one or more employee records depending on business modeling.

4. RBAC model

DirtView uses a 3-level access model:

1. Company level — tenant-wide default role
2. Project level — project membership and project role override
3. Module level — fine-grained module access inside a project

Core RBAC chain

• roles = reusable role definitions
• permissions = atomic capabilities
• role_permissions = role-to-permission mapping
• user_company_roles = tenant-level default role assignment
• project_members = project presence
• user_project_roles = project-level override
• project_module_access = module-level read/write control

Roles are definitions. Role assignment tables are where access is actually granted.

5. Timestamp conventions

Timestamp consistency is important across the schema.

Standard fields

• created_at — when the row was created
• updated_at — when the row was last modified
• deleted_at — soft-delete marker if soft deletion is supported

Guidelines

• Use timestamptz for time-aware timestamps.
• Store all timestamps in UTC.
• updated_at should be maintained automatically where possible.
• Append-only tables may not need updated_at.

6. Soft delete conventions

Many DirtView business tables should prefer soft delete over hard delete.

Use deleted_at when:

• Historical traceability matters
• The record may still be referenced elsewhere
• Users may need archive/recovery behavior

Examples

• projects
• employees
• files

Do not rely on soft delete for:

• Audit log rows
• Append-only operational history rows
• Immutable compliance-sensitive records

7. Immutable and append-only tables

Some tables should never allow row updates or deletions after insertion.

Immutability should be enforced at the database level where possible, not only by application code.

8. JSONB usage policy

JSONB is useful, but it should be used deliberately.

Use JSONB for:

• Flexible metadata that is not yet stable enough to normalize
• Schema-driven structures like form definitions
• OCR/raw extraction payloads
• Before/after snapshots in audit tables
• Geometry / structured annotation payloads

Do not use JSONB for:

• Core foreign keys
• Frequently joined business entities
• Stable relational concepts like memberships or roles

Guideline

If the application needs to query/filter/join a field often, that field probably belongs in a normal column.

9. File and storage conventions

File binaries live in object storage. The database stores metadata and access context.

Rules

• Use the files table as the metadata source of truth.
• Use tenant-prefixed storage paths.
• Use file_links to associate files to business records when appropriate.
• Uploaded files should be scanned before broad exposure.
• Signed URLs should be generated server-side.

Common categories

• drawings
• documents
• photos
• certifications
• form attachments
• thumbnails

10. Audit logging conventions

DirtView should maintain two separate audit layers:

Audit logging rules

• Prefer application-layer audit creation for human-readable descriptions.
• Use machine-readable action codes consistently.
• Store before/after state only when it is meaningful.
• Audit logs should be append-only.

11. RLS conventions

Row Level Security is a core enforcement layer in DirtView.

Rules

• Tenant-scoped tables should enforce tenant_id-based access.
• Project-scoped tables should often additionally check project membership.
• Global admin tables should not be exposed via normal client paths.
• Test RLS through actual app/auth flows, not only from elevated DB consoles.

Practical principle

Application-layer checks and RLS should reinforce each other, not replace each other.

12. Naming conventions

Table names

• Use plural snake_case table names
• Examples: tenant_memberships, drawing_versions

Column names

• Use snake_case
• Foreign keys should end in _id
• Timestamp fields should end in _at
• Boolean flags should start with is_, can_, or has_ where natural

Examples

• project_id
• created_at
• is_active
• can_write

13. Status field conventions

Many tables need lifecycle state. Keep status naming simple and explicit.

Guidelines

• Use a dedicated status field when lifecycle matters
• Prefer short, clear values like draft, active, submitted, failed
• Document allowed values clearly on each table page
• Use enums only when the value set is stable enough

14. Indexing expectations

Multi-tenant apps need intentional indexing.

General expectations

• Index tenant_id on all tenant-scoped tables
• Index common lookup pairs like (tenant_id, project_id)
• Index timeline/history tables by descending time where useful
• Add composite indexes to match real query patterns, not hypothetical ones

Examples

• (tenant_id, created_at desc)
• (project_id, status)
• (drawing_version_id, sheet_number)

15. Security conventions

• Do not expose service-role credentials to clients.
• Use backend-generated signed URLs for private files.
• Keep super-admin concerns fully separate from tenant concerns.
• Revocation and suspension should take effect immediately where required.
• Use audit logs for permissions-sensitive and externally significant events.

16. Documentation conventions

Every table page in this documentation site should try to answer the same core questions:

• What is this table?
• Why does it exist?
• What does each column mean?
• How is it used?
• What tables does it relate to?
• What are the important security rules?

Recommended standard metadata block

• Domain
• Scope
• Status
• Last Updated
• Mutable / Immutable
• Primary Owner
• RLS Applies
• Client Facing