Project Management Tables

Projects (projects)

Purpose: Core project/production management

Use Case Example: Create a main project "Summer Blockbuster 2024" with sub-projects for "VFX Unit" and "Second Unit", each tracking their own budgets and teams.

Column	Type	Description
id	UUID	Unique identifier
organisation_id	UUID	Reference to organisation that owns this project
entity_id	UUID	Reference to the entity that owns/operates this project (for accounting projects, this represents the business or individual) - References entities(id)
project_code	TEXT	Unique project code (auto-generated by trigger from title)
url_key	TEXT	Unique 6-character alphanumeric key used in URLs instead of UUID
parent_project_id	UUID	Reference to parent project (for sub-projects)
title	TEXT	Project title
created_by_user_id	UUID	User who created the project
updated_by_user_id	UUID	User who last updated
country_code	TEXT	ISO country code where the production company is headquartered (required, determines currency and tax scheme) - References countries(code)
currency_code	TEXT	Currency for financial transactions (auto-populated from country via trigger) - References currencies(code)
exchange_rate_mode	exchange_rate_mode	Project-wide foreign-currency conversion mode (live | stamped), NOT NULL DEFAULT stamped. live = always the currently-active project rate (corrections self-heal); stamped = the immutable rate version each transaction was processed under (transactions.exchange_rate_id), freezing already-processed figures. Read by resolve_transaction_exchange_rate; drives every converted amount in v_transactions / budget views.
fixed_exchange_rates_json	JSONB	Fixed exchange rates if applicable
tax_scheme_id	UUID	Tax scheme for the project (auto-populated from country via trigger) - References tax_schemes(id)
poster_attachment_id	UUID	Reference to the project poster image
logo_attachment_id	UUID	Reference to the project logo image
start_date	DATE	Date when the project started or will start
is_active	BOOLEAN	Whether the project is active or archived
is_tax_registered	BOOLEAN	Whether the production company or individual is registered for tax (GST/VAT). Default is false (not registered)
metadata	JSONB	Additional project metadata. Keys: emails (inbound-parsing addresses, e.g. {"emails": {"generic": "project.title_urlkey@docs.farmcove.co.uk"}}); transaction (per-transaction settings object — see note below)
current_budget_id	UUID	The budget currently being tracked (baseline budget). Set via UI when user selects active budget
current_schedule_id	UUID	The schedule currently being tracked (baseline schedule). Initially set on project creation, can be updated via UI
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Indexes:

• idx_projects_country_code on country_code for efficient country-based lookups
• idx_projects_entity_id on entity_id for efficient entity-based lookups

metadata.transaction — per-transaction-type project settings map, keyed EXCLUSIVELY by the six direction-aware transaction type keys (Expense, Invoice_inbound, Invoice_outbound, Payroll Invoice, Reimbursement, Unknown — see getTransactionTypeKey in utils/transaction.ts). Each entry is { entities_created_as_sensitive: boolean, line_item_processing?: 'detailed' | 'summary' }. The form always persists the full six-key map (a missing key would read as "not sensitive"), replacing metadata.transaction wholesale while preserving root metadata siblings (e.g. emails). The shape invariant is pinned by packages/database/supabase/tests/13_project_metadata_transaction_shape.sql.

metadata.transaction.<key>.entities_created_as_sensitive (boolean, default off) — when on for a type, an entity and project relationship auto-created while processing a transaction of that type are marked sensitive (auto_applied = true), so their transactions stay hidden until a producer/accountant reviews them. Because the transaction's type is only known after AI type discovery, the mark lands at that step, not at record creation. See SENSITIVE_DATA_SYSTEM.md § Auto-applied marks.

metadata.transaction.<key>.line_item_processing ('detailed' | 'summary', default detailed) — how the processing pipeline shapes a source document's extracted lines onto a transaction of that type. detailed keeps every extracted line as its own item; summary consolidates them into one item per distinct tax treatment before the matching steps run. An absent map, an absent key and an absent value are all equivalent to detailed (resolveLineItemProcessingMode in utils/transaction.ts), so existing projects need no backfill. The value is read at step time by the transaction_line_item_summarisation pipeline step, which stamps the mode it applied on transactions.line_item_mode — changing the setting does not retroactively rewrite existing transactions (the targeted Transaction Regenerate Line Items template does that). See PROCESSING_ARCHITECTURE.md § Line-item processing mode.

Per-type processing fallbacks are NOT part of this map — they live in their own table, project_transaction_fallbacks below.

Project Transaction Fallbacks (project_transaction_fallbacks)

Purpose: Per-project, per-transaction-type fallback defaults the processing pipeline applies when a line item cannot be matched to a chart of account or a budget item.

Use Case Example: A project routes every unmatched expense line to the "6000 — General Expenses" account rather than leaving it unassigned, so the accountant reviews one issue instead of re-coding each line.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Project the fallbacks apply to (FK → projects, CASCADE)
transaction_type	transaction_type	Transaction type the fallbacks apply to
direction	transaction_direction (nullable)	Invoice direction the fallbacks apply to. NOT NULL exactly when transaction_type is Invoice
coa_reference_id	UUID (nullable)	Chart-of-accounts reference used when a line cannot be matched (FK → integration_reference_data, ON DELETE SET NULL). NULL means no COA fallback configured
budget_item_id	UUID (nullable)	Budget item used when a line cannot be matched (FK → budget_items, ON DELETE SET NULL). NULL means no budget fallback configured
created_by_user_id	UUID (nullable)	User who created the row (DEFAULT auth.uid())
updated_by_user_id	UUID (nullable)	User who last updated the row (DEFAULT auth.uid())
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

"Enabled" is simply "the column is NOT NULL" — there is no separate flag. The COA fallback is only usable when a connected accounting integration has synced a chart of accounts; the budget fallback only when the project has an active budget (current_budget_id).

Constraints:

• project_transaction_fallbacks_direction_matches_type CHECK ((transaction_type = 'Invoice') = (direction IS NOT NULL)) — a fallback row is scoped to exactly one product-level transaction kind, and Invoice is the only type that splits by direction. Without it a single kind would be addressable by two rows and the resolver's lookup would be ambiguous. A cross-column relational invariant, not a value domain (both columns are already enums).
• project_transaction_fallbacks_unique_type UNIQUE NULLS NOT DISTINCT (project_id, transaction_type, direction) — the NULLS NOT DISTINCT clause is load-bearing: every non-Invoice row carries direction IS NULL, and under the default NULLS DISTINCT those NULLs never collide, so a project could accumulate unlimited duplicate rows per non-Invoice type.

Tenancy is trigger-enforced, not FK-enforced. A plain FK proves the target row exists, not that it belongs to this project, so the project_transaction_fallbacks_assert_ownership BEFORE INSERT/UPDATE trigger walks each budget item (budget_items → budget_headers → budgets) and each COA reference (integration_reference_data → project_integrations) back to its owning project and rejects a mismatch. Without it a project could point its fallbacks at another tenant's budget item and the pipeline would allocate to it.

Why a table rather than JSONB. These settings previously lived in projects.metadata.transaction.<key>.fallbacks as bare UUIDs with no referential integrity: a deleted budget item or COA reference left a dangling id that the read path silently ignored. The ON DELETE SET NULL FKs mean a deleted fallback target now nulls the column out — the setting visibly disappears instead of appearing configured but doing nothing. The migration backfilled only values whose legacy enabled flag was true and whose target still existed and belonged to the project, reporting every dropped value, then stripped the fallbacks key from every metadata.transaction entry.

Row Level Security: SELECT is ordinary project membership (v_user_accessible_projects); INSERT/UPDATE/DELETE require project:edit. Writes go through the atomic save_project_transaction_settings RPC, which treats the supplied set as the complete desired state.

Applying a fallback does not suppress the issue. When one is applied, the unmatched-linking processing issue is still raised, carrying fallbackApplied/fallbackName in its context_data so the issue card shows a "default fallback applied" legend.

Organisations (organisations)

Purpose: Represents organisations that own projects. Each user gets a "Personal" organisation by default.

Use Case Example: When a user signs up, they automatically get a "Personal" organisation. Companies can create separate organisations for team collaboration.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Organisation name
organisation_code	TEXT	Unique code generated from organisation name for internal use
url_key	TEXT	Unique 6-character alphanumeric key used in URLs instead of UUID
logo_attachment_id	UUID	Reference to attachment for organisation logo
is_active	BOOLEAN	Whether the organisation is active or archived
created_by_user_id	UUID	User who created the organisation
updated_by_user_id	UUID	User who last updated the organisation
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
is_default	BOOLEAN	Whether this is the default organisation for new users

User Accesses (user_accesses)

Purpose: Unified permission system that grants roles or permissions to users at organisation or project scope.

Use Case Example: Grant Jane the "Line Producer" role for "Summer Blockbuster 2024" project, or grant Bob "organisation:edit" permission for the entire production company.

Column	Type	Description
id	UUID	Unique identifier
user_id	UUID	Reference to user being granted access
grant_type	TEXT	Type of grant: 'role' or 'permission'
grant_id	UUID	ID of permission_role or permission being granted
scope_type	TEXT	Scope of grant: 'organisation' or 'project'
scope_id	UUID	ID of organisation or project
project_relationship_id	UUID	Reference to project_relationship (for project scope)
status	TEXT	Status: 'Active', 'Invited', or 'Revoked'
granted_by_user_id	UUID	User who granted this access
granted_at	TIMESTAMPTZ	When this access was last granted (initial or re-grant after revoke)
revoked_by_user_id	UUID	User who performed the most recent revoke (nullable)
revoked_at	TIMESTAMPTZ	Timestamp of the most recent revoke (preserved across re-grants)
expires_at	TIMESTAMPTZ	Optional expiration timestamp
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Unique constraint on (user_id, grant_type, grant_id, scope_type, scope_id, project_relationship_id)
• Supports both role-based and direct permission grants
• Can grant access at organisation or project level
• Links to project_relationship for project-level grants
• granted_at + granted_by_user_id capture the latest grant event; revoked_at + revoked_by_user_id capture the latest revoke event. Compare granted_at vs revoked_at to determine current state history (a future audit_events table will hold the full log)

Indexes:

• idx_user_accesses_user_scope on (user_id, scope_type, scope_id) — composite index backing the permission-resolution primitives (caller_accessible_project_ids / caller_has_permission via v_user_permissions) and the sensitive-data clearance functions (get_user_clearance / get_user_clearance_for_user), the clearance functions and caller_has_permission probe the full (user_id, scope_type, scope_id) triple; caller_accessible_project_ids seeks on the leading user_id and reads the scope columns from the index.

Note on revoked_at / role is_active: the permission-resolution path (v_user_permissions) filters on status but checks neither revoked_at nor the role's is_active (it never joins permission_roles), whereas the clearance functions require revoked_at IS NULL and permission_roles.is_active = true. A grant left status = 'Active' with a non-null revoked_at, or one referencing a deactivated role, still confers permissions but not clearance — known divergences flagged for a follow-up ticket (see PERMISSIONS_SYSTEM.md § Access primitives & semantic contracts).

Role Definitions (role_definitions)

Purpose: Defines standard production industry roles with department classification and role characteristics

Use Case Example: Create roles like "Director", "Producer", "Cinematographer" that can be assigned to entities on projects.

Column	Type	Description
id	UUID	Unique identifier
title	TEXT	Role title (e.g., Director)
description	TEXT	Detailed description of the role and its responsibilities
department_id	UUID	Reference to departments table (nullable)
is_hod	BOOLEAN	Whether this is a Head of Department role
is_chain_of_title	BOOLEAN	Whether this role affects chain of title
is_on_screen	BOOLEAN	Whether this is an on-screen role (e.g., Cast)
is_not_common	BOOLEAN	Whether this role is uncommon in standard productions
can_be_vendor	BOOLEAN	Whether this role can be assigned to Organisation entities
can_be_person	BOOLEAN	Whether this role can be assigned to Person entities
is_active	BOOLEAN	Whether role is currently used
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Department classification links roles to production departments (Above/Below the Line)
• Boolean flags identify special role characteristics (HOD, chain of title, on-screen, uncommon)
• Project types array indicates which project types the role applies to
• Supports both individual persons and vendor organisations
• Includes 305 standard production roles and 64 business service roles

Project Relationships (project_relationships)

Purpose: Links entities to projects with specific production roles

Use Case Example:

• Add Steven Spielberg as "Director" (Person entity)
• Add a production company as "Producer" (Organisation entity)
• Add catering service as "Supplier" (Organisation entity)

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Reference to project
entity_id	UUID	Reference to entity (required)
role_definition_id	UUID	Standard role from role_definitions table
role_title_override	TEXT	Custom role title if different
is_active	BOOLEAN	Record lifecycle on the project. true = currently participating, false = deactivated. NOT NULL, default true
access_status	ENUM	App access lifecycle, using the shared access_status enum (Active/Invited/Revoked). NULL means no invite has been sent (e.g. crew member added without granting app access)
expense_reimbursement_code	TEXT	Code used by production to match submitted receipts/expenses to this entity (nullable)
deal_terms	TEXT	Free-text agreed engagement terms (rates, conditions, other negotiated terms) for this relationship. Plain, non-sensitive, unmasked — visible to anyone who can view the relationship. Nullable; empty/whitespace-only input is normalized to NULL by the app
metadata	JSONB	Relationship-specific data (contains identity_document object with document details captured at acceptance time). Default: '{}'::JSONB
invited_by_user_id	UUID	User who sent the invitation to join the project
invited_at	TIMESTAMPTZ	Timestamp when the invitation was sent
accepted_at	TIMESTAMPTZ	Timestamp when the invitation was accepted. Immutable once set
cancelled_by_user_id	UUID	User who cancelled a pending invitation (never-accepted path). Distinct from revoke
cancelled_at	TIMESTAMPTZ	Timestamp when a pending invitation was cancelled
revoked_by_user_id	UUID	User who revoked access on an accepted relationship. Cleared when access is granted again
revoked_at	TIMESTAMPTZ	Timestamp of the most recent revoke on an accepted relationship. Cleared when access is granted again
invite_failed_at	TIMESTAMPTZ	Set when the Sana identity-onboarding flow crashed; presence bypasses the 1-hour resend throttle. Cleared on successful resend
employment_type	ENUM	How this crew member is engaged. public.employment_type: employee, self_employed, loan_out, other. Nullable — set after identity/starter-form flow
loan_out_via_project_relationship_id	UUID	For loan_out employment type: the Business entity relationship on this same project that invoices on behalf of this person. Must reference a Business entity in the same project. Enforced by trg_validate_loan_out_relationship BEFORE INSERT/UPDATE trigger
payment_details	JSONB	Snapshot of the entity payment details at the time the relationship was finalised (same shape as entities.payment_details). Backfilled from the legacy metadata.bank blob and now stored as a first-class column with its own GIN index (idx_project_relationships_payment_details). Raw reads are revoked for authenticated — this column is EXCLUDED from the authenticated SELECT column grant, so the ONLY authenticated read path is the masking view v_project_relationships (via the DEFINER helper private.project_relationship_masked_fields), which enforces the field mask and subject self-visibility. INSERT/UPDATE/DELETE are unchanged, so it stays WRITABLE via the base table. See SENSITIVE_DATA_SYSTEM.md
requires_identity_document	BOOLEAN	Whether the project requires an identity document from this crew member. Default false
identity_document_waived_by_user_id	UUID	User who waived the identity document requirement (nullable)
identity_document_waived_at	TIMESTAMPTZ	When the identity document requirement was waived (nullable)
created_by_user_id	UUID	User who created the relationship
updated_by_user_id	UUID	User who last updated the relationship
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• entity_id references the entities table (can be Person or Organisation type)
• Supports all types of project participants through unified entity model
• Role definitions determine applicable entity types
• metadata.identity_document stores a snapshot of identity details at the time of acceptance (entity always has latest data, but each relationship preserves the state when accepted). The actual file linkage now lives in project_relationship_attachments (with attachment_type = 'identity_document'); the legacy identity_attachment_id column was dropped in migration 20260403000000 and existing rows were backfilled
• cancelled_* is reserved for cancelling a pending invitation (never accepted); revoked_* is reserved for revoking access on an accepted relationship. The two paths are distinct so the UI and analytics can tell them apart
• invite_failed_at is stamped by Sana form-handler catch blocks when the identity-onboarding flow crashes. The inviter's "Resend Invite" button reads this column to bypass the 1-hour throttle; a successful resend clears it back to NULL
• The relationship is modelled on two independent axes:
• is_active — record lifecycle on the project: true (currently participating) vs false (deactivated)
• access_status — app-access lifecycle: Invited (sent, awaiting acceptance) → Active (accepted, access granted) → Revoked (access revoked after acceptance, or invite cancelled). NULL means no invite was ever sent