Core Tables¶

Entities (`entities`)¶

Purpose: Flexible table for storing both individuals and businesses as entities. Replaces the old people and vendors tables.

Use Case Example: When adding a director, actor, supplier, or any other participant to a project, create an entity record with the appropriate type.

Column	Type	Description
id	UUID	Unique identifier (auto-generated)
type	ENUM	Entity type: 'Person' or 'Business'
organisation_id	UUID	Organisation this entity belongs to (required)
user_id	UUID	Reference to public.users if this entity is a system user (nullable). Security boundary — write-restricted. Subject self-visibility for sensitive records keys off this column, so it is EXCLUDED from the `authenticated` INSERT/UPDATE column grants and is not part of any public create/update DTO. Written only by trusted server paths (`entity/base.linkEntityUser` under a service-role context — see `entity/server.createOwnUserEntity` / `unlinkEntityUser`). See SENSITIVE_DATA_SYSTEM.md § `entities.user_id` write boundary
first_name	TEXT	First name (required for Person type)
last_name	TEXT	Last name (required for Person type)
preferred_name	TEXT	Preferred display name for the entity (Person type)
business_name	TEXT	Business name (required for Business type)
registration_number	TEXT	Entity registration number (e.g., Company Registration Number or Schedule D Number for tax purposes)
trading_name	TEXT	Trading name for businesses that operate under a different name than their legal business name
is_accounting_entity	BOOLEAN	Flag indicating this entity was created from an accounting project (personal or company accounting, default: false)
establishment_date	DATE	Date when entity was established. For companies: incorporation date. For personal/self-employed: Self-Employment Start Date
date_of_birth	DATE	Date of birth (Person type only)
email	TEXT	Email address of the entity (nullable)
phone_number	TEXT	Phone number of the entity (nullable)
payment_details	JSONB	Structured payment details (replaces flat bank_* columns and `bank_address_id`). Shape: `{ type, account_holder, bank: { payee_name, account_number, sort_code, bank_name, country_code, overseas_code, additional_reference, address_id } }`. `type` discriminates the payload (currently only `bank_account`). The dropped `bank_account_type` enum is now stored as the `account_holder` string. Nullable.
address_id	UUID	Reference to the entity's residential address (FK to addresses, nullable)
tax_number	TEXT	Tax identification number (e.g., TIN, SSN, VAT number) - nullable
citizenship_country_code	TEXT	Country of citizenship - references country(code) (Person type only, nullable)
residency_country_code	TEXT	Country of residency - references country(code) (nullable)
metadata	JSONB	Additional metadata about the entity (see Metadata Structure below)
is_active	BOOLEAN	Whether the entity is active (default: true)
is_secondary_supplier	BOOLEAN	Indicates if this entity is a secondary supplier (Business type, default: false)
is_customer	BOOLEAN	Flag indicating if this entity is a customer who receives invoices from us (default: false)
search_query	TSVECTOR	Full-text search vector over non-sensitive identity fields only — `business_name`, `first_name`, `last_name`, `preferred_name`, `trading_name`, `registration_number` (auto-generated). Maskable values (email, phone_number, bank details) were removed from the vector so a row-visible-but-uncleared caller cannot infer a masked value by probing `idx_entities_search`. Readable by `authenticated` (still in the SELECT column grant)
created_at	TIMESTAMPTZ	Timestamp when the entity was created
updated_at	TIMESTAMPTZ	Timestamp when the entity was last updated
created_by_user_id	UUID	User who created this entity
updated_by_user_id	UUID	User who last updated this entity

Key Features:

Unified table for both individuals and businesses
Type-specific constraints ensure data integrity
Automatic sync with users table for system users
Metadata field tracks change history
Can exist without a corresponding user account
Used for cast/crew, suppliers, vendors, etc.
Linked to organisations for multi-tenancy support
Full-text search support with GIN index on search_query column. The search_query tsvector covers non-sensitive identity fields only (business_name, first_name, last_name, preferred_name, trading_name, registration_number); maskable values (email, phone_number, bank fields) were deliberately dropped from the vector so a masked value cannot be inferred by probing idx_entities_search
payment_details has its own GIN index (idx_entities_payment_details) supporting JSONB containment queries (e.g. payment_details @> '{"type":"bank_account"}')
Maskable-column read protection. The email, phone_number, and payment_details columns are EXCLUDED from the authenticated SELECT column grant — the raw columns are unreadable via the Data API, and the ONLY authenticated read path is the masking view v_entities (via the DEFINER helper private.entity_masked_fields), so field masks cannot be bypassed by selecting the base table. INSERT/UPDATE still cover these value columns (writes are unaffected). user_id is additionally excluded from the INSERT/UPDATE grants (see the column note above). Fail-closed: any future column on entities must be added to the appropriate column-grant list — a maskable one is OMITTED from SELECT and routed through the helper + view; a non-maskable one is ADDED — or it is unreadable/unwritable by authenticated.
Migration 20260505231911_add_payment_details_to_entities_and_relationships.sql consolidated the flat bank_account_number / bank_name / bank_sort_code / bank_payee_name / bank_account_type / bank_overseas_code / bank_address_id columns and dropped the bank_account_type enum. Existing data was backfilled from the flat columns into payment_details

Entity Metadata JSONB Structure:

The metadata field can store the following structured data:

{
  "emergency_contact": {
    "full_name": "string",
    "phone": "string (E.164 format, e.g., +44123456789)",
    "email": "string (optional)",
    "relationship": "string (e.g., Spouse, Parent, Sibling, Friend)"
  },
  "identity_documents": {
    "passport": {
      "number": "string",
      "place_of_issue": "string",
      "date_of_issue": "YYYY-MM-DD",
      "expiry_date": "YYYY-MM-DD",
      "country_code": "string (ISO 3166-1 alpha-2)"
    },
    "drivers_licence": {
      "number": "string",
      "place_of_issue": "string",
      "date_of_issue": "YYYY-MM-DD",
      "expiry_date": "YYYY-MM-DD",
      "country_code": "string (ISO 3166-1 alpha-2)"
    },
    "other_ids": [
      {
        "type": "string (e.g., National ID, Social Security Card)",
        "number": "string",
        "place_of_issue": "string (optional)",
        "date_of_issue": "YYYY-MM-DD (optional)",
        "expiry_date": "YYYY-MM-DD (optional)",
        "country_code": "string (optional)"
      }
    ]
  },
  "source": "string — creation provenance (e.g. auth_user_creation, ui_creation, transactions)",
  "created_by_id": "uuid of the creating user",
  "created_by_processing_job_id": "uuid — set ONLY when the entity was created fresh by a transaction-processing job; the type-discovery step compares it against its own job id to decide per-type auto-sensitivity (see SENSITIVE_DATA_SYSTEM.md § Auto-applied marks). Same stamp convention applies to project_relationships.metadata."
}

Users (`users`)¶

Purpose: System users who can authenticate and access the application

Use Case Example: When a new crew member needs system access, a user account is created and automatically creates a corresponding entity record with auth credentials.

Column	Type	Description
id	UUID	Same as auth.users.id for consistency
first_name	TEXT	User's first name
last_name	TEXT	User's last name
preferred_name	TEXT	User's preferred display name
email	TEXT	Email address (synced from auth.users)
phone	TEXT	Phone number (synced from auth.users)
is_active	BOOLEAN	Whether the user account is active and can log in
is_invite_pending	BOOLEAN	True for invited users who haven't completed the Sana acceptance flow (default: false)
is_internal	BOOLEAN	Flag to identify internal/system users (default: false)
auth_deleted	BOOLEAN	Soft delete flag when auth user is deleted
auth_deleted_at	TIMESTAMPTZ	When the auth user was deleted
last_login_at	TIMESTAMPTZ	Last successful login timestamp
created_at	TIMESTAMPTZ	When the user account was created
updated_at	TIMESTAMPTZ	Last update timestamp
avatar_attachment_id	UUID	Reference to attachment for user's profile picture
bio	TEXT	User's biography/description

Key Features:

Automatically synced with auth.users via triggers
Soft deleted when auth user is deleted
Email/phone fields are read-only (synced from auth)
Name fields (first_name, last_name, preferred_name) stored directly on users table
Synced with corresponding entity record via triggers
Internal users (is_internal = true) don't have auth.users records

Reserved internal system accounts¶

There is MORE THAN ONE internal user, so is_internal = true no longer identifies a specific account. Always resolve a system account by its reserved email address, never by the flag — a lookup keyed on is_internal alone is ambiguous and will pick an arbitrary account.

Reserved email	Name	Purpose
`message-system@farmcove.internal`	Email System	System-generated messages that have no human sender
`external-respondent@farmcove.internal`	External Respondent	Inbound email replies from an address matching no platform user; the real address rides `messages.metadata.external_responder_email` and surfaces as `external_responder_email` on the approval views

The ids are generated per environment (gen_random_uuid() in the seeding migration), so nothing may hard-code them — application code resolves both accounts by email.

User Settings (`user_settings`)¶

Purpose: User preferences and settings

Use Case Example: Users can customize their experience by setting preferred language (es, en), theme (dark/light), timezone for accurate scheduling, and their country for regional settings.

Column	Type	Description
user_id	UUID	Reference to the users record (primary key)
language_code	TEXT	User's preferred language (default: 'en')
preferred_theme	TEXT	UI theme preference (light/dark/system)
timezone_id	TEXT	User's timezone for date/time display
country_code	TEXT	User's country code (ISO 3166-1 alpha-2, default: 'GB')
currency_code	TEXT	User's default currency - references currency(code) (default: 'GBP')
notification_preferences	JSONB	User notification preferences - channel settings and per-template overrides (see below)
organisation_id	UUID	Default organisation for the user
table_views	JSONB	Saveable DataTable list views per user, keyed by table key (see below)
updated_at	TIMESTAMPTZ	Timestamp when settings were last updated

Notification Preferences JSON Structure:

{
  "email_enabled": false,           // Global email notifications (default: false)
  "whatsapp_enabled": true,         // Global WhatsApp notifications (default: true)
  "in_app_enabled": true,           // Global in-app notifications (default: true)
  "digest_frequency": "immediate",  // Options: "immediate", "daily", "weekly"
  "quiet_hours_start": null,        // Start of quiet hours in "HH:MM" format or null
  "quiet_hours_end": null,          // End of quiet hours in "HH:MM" format or null
  "template_overrides": {           // Per-template overrides (optional)
    "<template_key>": {
      "email_enabled": boolean,
      "whatsapp_enabled": boolean,
      "in_app_enabled": boolean
    }
  }
}

Table Views JSON Structure (table_views):

Stores a user's saved list-table layouts, keyed by a fully-qualified table key (e.g. "transactions:expense") so the same component rendered in different tabs persists independently. The shape is forward-compatible with future named/multiple views — today only a single "default" view per key is read/written.

{
  "<tableKey>": {
    "activeViewId": "default",
    "views": {
      "default": {
        "id": "default",
        "name": "Default",
        "state": {
          "columnOrder": ["col_a", "col_b"],
          "columnVisibility": { "col_c": false },
          "sorting": [{ "id": "date", "desc": true }],
          "columnFilters": [],
          "grouping": []
        },
        "createdAt": "2026-06-19T00:00:00.000Z",
        "updatedAt": "2026-06-19T00:00:00.000Z"
      }
    }
  }
}

Core Tables¶

Entities (entities)¶

Users (users)¶

Reserved internal system accounts¶

User Settings (user_settings)¶

Entities (`entities`)¶

Users (`users`)¶

User Settings (`user_settings`)¶