Notification System Tables

Notification Senders (notification_senders)

Purpose: Stores email addresses and phone numbers used to send notifications

Use Case Example: Configure notifications@farmcove.com as the default email sender and "+1234567890" as the WhatsApp sender for system notifications.

Column	Type	Description
id	UUID	Unique identifier
sender_type	TEXT	Type of sender: 'email' or 'phone'
sender_value	TEXT	The actual email address or phone number
sender_name	TEXT	Display name shown to recipients (e.g., "FarmCove Support")
provider	TEXT	Service provider (e.g. resend, twilio, ses, sendgrid)
is_active	BOOLEAN	Whether this sender is currently active
is_default	BOOLEAN	Whether this is the default sender for its type
category_id	UUID	Optional FK to categories - links sender to specific project types (NULL = all categories)
metadata	JSONB	Provider-specific configuration
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Unique constraint on (sender_type, sender_value)
• Only one default sender per type
• Supports multiple providers for redundancy
• Optional category association allows senders to be project-type specific (e.g., WhatsApp for media projects)

Default Senders:

sender_value	sender_name	Purpose
noreply@farmcove.co.uk	FarmCove	Default email sender (exports)
approval@farmcove.co.uk	Farm Cove Approvals	Approval workflow emails

Notification Template Categories (notification_template_categories)

Purpose: Categories for grouping notification templates in the UI

Use Case Example: Group all budget-related notifications under "Budget & Finance" category for better organisation in user preferences.

Column	Type	Description
id	UUID	Unique identifier
code	TEXT	Unique code for the category (e.g., system, project, team)
title	TEXT	Display title for the category
description	TEXT	Description of the category to show in UI
display_order	INTEGER	Order for displaying categories in UI
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Unique constraint on code
• Used to organize notification preferences UI
• Seeded categories, in display order: approval (50), forms (60), project_documents (70), processing (80), exports (100), sana (999)
• RLS Policies: Authenticated read-only. Notification categories managed by service_role

Notification Templates (notification_templates)

Purpose: Templates for different types of notifications with multi-channel support

Use Case Example: Create a "budget-threshold-reached" template that sends email alerts, WhatsApp messages, and in-app notifications when budget exceeds 80%.

Column	Type	Description
id	UUID	Unique identifier
template_key	TEXT	Unique key used in code (e.g., 'invoice-created')
name	TEXT	Human-readable name for admin UI
notification_template_category_id	UUID	Reference to notification_template_categories
description	TEXT	Description for admin UI
is_active	BOOLEAN	Whether this template is currently active and can be used
email_available	BOOLEAN	Whether this notification can be sent via email
whatsapp_available	BOOLEAN	Whether this notification can be sent via WhatsApp
in_app_available	BOOLEAN	Whether this notification can be shown in-app
subject_template	TEXT	Email subject line with ${variables}.
body_template	TEXT	HTML for email, plain text for in-app
body_template_text	TEXT	Plain text version for email clients
whatsapp_config	JSONB	WhatsApp configuration including template: {name, language}, flow: { id, name, first_screen} and components
metadata	JSONB	Additional metadata including media information (media_type, media_url) and other template-specific data
default_email_sender_id	UUID	Reference to default email sender
default_phone_sender_id	UUID	Reference to default phone sender
show_in_preferences	BOOLEAN	Whether this notification template should be shown in user preferences UI
mandatory_channels	TEXT[]	Array of channels that are mandatory for this notification template (cannot be disabled by users). Valid values: in_app, email, whatsapp
default_email_enabled	BOOLEAN	Whether email is enabled by default for this template (user can override in preferences). Default: false
default_whatsapp_enabled	BOOLEAN	Whether WhatsApp is enabled by default for this template (user can override in preferences). Default: false
default_in_app_enabled	BOOLEAN	Whether in-app is enabled by default for this template (user can override in preferences). Default: true
required_permissions	TEXT[]	Array of permission keys required to see this template in preferences. NULL or empty = visible to all users
email_template	TEXT	HTML template specifically for email channel. If NULL, body_template is used for email. Supports rich HTML with styled action buttons
digest_category_parent_code	TEXT	Parent category code for digest grouping (e.g., 'transactions'). Links templates to category-based digest preferences
digest_category_group_code	TEXT	Category group code for digest grouping (e.g., 'approval_types'). Used with parent code to identify category hierarchy for digests
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Templates control which channels are available via *_available flags
• Templates define default enabled state via default_*_enabled flags
• Supports variable substitution with ${variables}
• WhatsApp configuration consolidated in single JSONB field
• Mandatory notifications cannot be disabled by users
• Category relationship for better organisation
• Mandatory channels array for granular control per template
• Permission-based visibility: templates can require specific permissions to be shown in user preferences

processing_stalled:

Seeded template for the stalled-AI-processing alert. Raised when the stall-recovery sweeper exhausts its retry budget for a job (see AI_PROCESSING.md → Stall recovery), telling the team that automatic document processing could not complete and can be retried from the transaction screen.

• In-app plus opt-in WhatsApp (in_app_available and whatsapp_available true, email_available false) — the alert is actionable in-app, not correspondence, so email stays off; WhatsApp is available but off by default (default_whatsapp_enabled false) for users who want it pushed
• in_app is a mandatory channel, so users cannot silence the one signal that a job needs a manual retry; show_in_preferences is true so it is still visible in the preferences UI
• Carries the ${transaction_reference} variable, in the standard ${...} substitution syntax
• Grouped under the processing category (notification_template_category_id), alongside transaction_processing_not_started
• Seeded idempotently via ON CONFLICT (template_key) DO NOTHING; shape pinned by assertions in packages/database/supabase/tests/01_tables_and_pks.sql

transaction_processing_not_started:

Seeded template for the dispatch-loss escalation. Raised by the unprocessed-transaction sweeper when a received transaction's initial processing job was never created and the sweep's own re-dispatch attempts keep failing past a 2-hour threshold (see PROCESSING_ARCHITECTURE.md → The unprocessed-transaction sweeper). Recipients are every ACTIVE holder of the project:owner role on the transaction's project.

• In-app ONLY (in_app_available true; both email_available and whatsapp_available false) — the condition is an operational anomaly for project owners to chase in the app, so neither channel can be enabled from the preference screen
• in_app is a mandatory channel; show_in_preferences is true so the template is still visible in the preferences UI
• Carries the ${transaction_reference} variable, in the standard ${...} substitution syntax
• Grouped under the processing category (notification_template_category_id), like processing_stalled
• Deliberately a notification and NOT a processing issue: pre-pipeline transactions are hidden from every list surface, so an issue row on one would reach nobody
• Seeded idempotently via ON CONFLICT (template_key) DO NOTHING; shape pinned by assertions in packages/database/supabase/tests/01_tables_and_pks.sql

Channel Priority:

When determining which channels to use for a notification:

1. Check if channel is available on template (*_available = true)
2. Check if channel is mandatory (mandatory_channels array) - user cannot disable
3. Check if user has template-specific override in preferences
4. Otherwise use template default (default_*_enabled)

Category-Aware Templates:

Several templates support dynamic content based on project category flags (is_media, is_accounting, is_personal_accounting):

• known_person - Uses ${project_type_description} variable:
• Media: "You are talking to {person_name}, who {person_relationship} for the project {project_title}, as {role_name}..."
• Personal Accounting: "You are talking to {person_name}, who {person_relationship} for the {project_title} personal accounting project..."

• Company Accounting: "You are talking to {person_name}, who {person_relationship} for the {project_title} company accounting project..."

• switch_project (renamed from switch_film) - Generic project switching instructions

• Template key: switch_project

• Body: "Please choose a project or onboard to a new one using the button."

• switch_success - Uses ${project_type_description} variable:

• Media: "You are now working on the project {project_title}, as {role_name}, please just let me know if you need to switch context."
• Personal Accounting: "You are now working on the {project_title} personal accounting project, please just let me know if you need to switch context."
• Company Accounting: "You are now working on the {project_title} company accounting project, please just let me know if you need to switch context."

Approval Templates (Category: approval):

Templates for approval workflow notifications:

Template Key	Name	Description	Variables
approval_query	Approval Query	Notification sent to submitter when an approver queries a submission	${name}, ${reference}
approval_rejection	Approval Rejection	Notification sent to submitter when their submission is rejected	${name}, ${reference}, ${reason}
approval_reply_received	Approval Reply Received	Notification sent to approver when submitter replies to their query	${name}, ${submitter_name}, ${reference}
transaction_approval	Transaction Approval Request	Notification sent to approvers when a transaction is submitted	Email/In-App: ${transaction_type}, ${transaction_identifier}, ${reference}, ${submitter_name}, ${description}, ${total}. WhatsApp body: ${attachmentId}, ${typeAndProject}, ${typeAndReference}, ${reference}, ${submitter_name}, ${description}. WhatsApp flowData: ${heading}, ${subheading}, ${flowDescription}, ${minDate}, ${scheduledPaymentDate}, ${scheduledPaymentAmount}, ${queryHistory}, ${options}

All approval templates have:

• in_app_available: true
• whatsapp_available: true
• email_available: true
• mandatory_channels: ['in_app']

Notifications (notifications)

Purpose: Central log of all notifications sent to users

Use Case Example: When budget reaches 80%, system creates notification record with rendered content and tracks which channels were used to deliver it.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Project this notification relates to (optional)
recipient_user_id	UUID	User who received the notification
created_by_user_id	UUID	User who created/triggered this notification (NULL for system-generated notifications)
template_id	UUID	Template used (optional for custom notifications)
subject	TEXT	Rendered email subject
content_html	TEXT	HTML version for email
content_text	TEXT	Plain text for in-app/WhatsApp
data	JSONB	Original template variables for re-rendering/audit
whatsapp_data	JSONB	Rendered WhatsApp component data
channels_sent	JSONB	Which channels were used {"email": true, "whatsapp": false}
allow_mark_as_unread	BOOLEAN	Whether this notification can be marked as unread by the recipient (disabled by default)
created_at	TIMESTAMPTZ	When notification was created

Key Features:

• Immutable audit log of all notifications
• Stores rendered content for display
• Tracks which channels were actually used
• Enabled for Realtime to power in-app notifications

Notification Deliveries (notification_deliveries)

Purpose: Tracks delivery status for each channel

Use Case Example: Email sent via Resend at 2 PM, delivered at 2:01 PM, read at 3 PM. WhatsApp message failed due to invalid number.

Column	Type	Description
id	UUID	Unique identifier
notification_id	UUID	Reference to parent notification
channel	TEXT	Delivery channel: email/whatsapp/in_app
recipient_address	TEXT	Email, phone number, or user_id
sender_address	TEXT	Email or phone used to send
sender_id	UUID	Reference to notification sender
provider	TEXT	Service provider used (resend, twilio, supabase-realtime)
status	TEXT	pending/sent/delivered/failed/read
error_message	TEXT	Error details if failed
sent_at	TIMESTAMPTZ	When notification was sent
delivered_at	TIMESTAMPTZ	When reached recipient device/inbox
read_at	TIMESTAMPTZ	When recipient read/opened
retry_count	INTEGER	Number of retry attempts
created_at	TIMESTAMPTZ	Creation timestamp
notification_action_token_id	UUID	FK to notification_action_tokens, nullable - for EMAIL and WHATSAPP deliveries with action links

Key Features:

• One record per channel per notification
• Tracks complete delivery lifecycle
• Supports retry logic with attempt counting
• WhatsApp-specific tracking moved to message.whatsapp_metadata
• Links to email action tokens for traceability of email action links
• Digest notifications use live-query pattern: cron jobs query pending items directly at digest time rather than storing stale delivery records

Notification Attachments (notification_attachments)

Purpose: Links notifications to file attachments

Use Case Example: Invoice notification includes PDF attachment sent via email, but not via WhatsApp.

Column	Type	Description
id	UUID	Unique identifier
notification_id	UUID	Reference to notification
attachment_id	UUID	Reference to attachment
channel	TEXT	Which channel this attachment is for (email/whatsapp)
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

• Supports channel-specific attachments
• Links to existing attachment system
• Allows different attachments per channel

Notification Action Tokens (notification_action_tokens)

Purpose: Generic secure token system for notification action links (approvals, query responses, exports, etc.). Supports both EMAIL and WHATSAPP channels. Uses metadata.record_ids array for flexible single/bulk record references.

Use Case Example: When sending an approval notification via email or WhatsApp, a token is generated that allows the approver to take action (approve/reject/query) directly from the notification without logging in. Digest notifications can include bulk tokens referencing multiple pending items.

Column	Type	Description
id	UUID	Unique identifier
token_hash	TEXT	Lowercase-hex SHA-256 of the plaintext token — sole at-rest representation and lookup key (NOT NULL)
action_type	TEXT	Type of action: 'approval_review', 'approval_review_bulk', 'query_response', 'export_download', etc.
user_id	UUID	User the token was issued to (optional, for validation)
metadata	JSONB	Action context including record_ids array: [{approval_instance_id, approval_request_id}, ...]
expires_at	TIMESTAMPTZ	When the token expires (typically 7 days from creation)
used_at	TIMESTAMPTZ	When the token was consumed (NULL if unused)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who triggered the token creation (system notifications = NULL)

Constraints:

• UNIQUE index on token_hash (the lookup key; NOT NULL, so a plain — not partial — unique index)
• CHECK token_hash ~ '^[0-9a-f]{64}$' (hash shape)
• FK on user_id references users(id) ON DELETE CASCADE
• FK on created_by_user_id references users(id) ON DELETE SET NULL (the column existed before the forms migration but had no FK or index)

Indexes:

• idx_notification_action_tokens_token_hash UNIQUE on token_hash - Hash lookup (the only lookup path)
• idx_notification_action_tokens_metadata_record_ids GIN index on (metadata->'record_ids') - Find tokens by record references
• idx_notification_action_tokens_expires on expires_at WHERE used_at IS NULL - Cleanup of unused expired tokens
• idx_notification_action_tokens_created_by_user_id on created_by_user_id

Token hash-at-rest (final state):

The token is a bearer credential, so the usable secret is never stored at rest. token_hash (SHA-256 of the plaintext, encode(digest(convert_to(<plaintext>,'UTF8'),'sha256'),'hex')) is the sole at-rest representation and the lookup key. The server layer mints a CSPRNG token, writes only token_hash, and returns the plaintext in-memory to the caller for building the action URL — it is discarded after and never persisted. Validation hashes the incoming token string and looks up by token_hash; there is no plaintext column and no plaintext fallback path.

This shipped as a single-release cutover (the originally-planned expand/contract two-phase rollout was collapsed): the migration backfilled token_hash for every existing row (keeping still-live approval/query links resolvable by hash), then invalidated every outstanding unused token of every action type by EXPIRY (UPDATE … SET expires_at = now() WHERE used_at IS NULL AND expires_at > now()), dropped the plaintext token column, and made token_hash NOT NULL + uniquely indexed. Invalidation is by expiry rather than consumption (used_at) so a never-filled pre-cutover form-fill link renders the accurate "Link Expired" state — validation maps a used_at token to the terminal "Already Submitted" replay UX, which would be wrong for a link that was never actually submitted. There is no dual-write phase and no compatibility trigger.

Operational consequence: every notification-action link (approval review, query response, form-fill, export download, …) sent before the deploy stops working and must be re-sent / regenerated from the app. New links minted post-deploy are CSPRNG + hash-at-rest from birth. Deploy-window behaviour (accepted): between the CI migration apply and traffic switching to new instances, old-code token mints/validations reference the dropped token column and error — acceptable because all outstanding links were invalidated above and are re-sent afterward; the window is minutes (instance drain).

Security:

• Tokens are one-time use (marked as used after successful action)
• Default expiration: 7 days (168 hours)
• No authentication required - token acts as the auth credential
• Service-role only. The authenticated role holds NO grant on this table (all four privileges revoked). RLS stays ENABLED with four explicit deny-all policies ("Service role only - no direct {select,insert,update,delete} access", USING/WITH CHECK false) — these document default-deny and silence the Supabase "RLS enabled, no policies" advisor; they grant nothing (service_role bypasses RLS). Every token operation (mint, validate, consume) runs service-role in the server layer. This closes the previous forgeable-mint / IDOR surface where authenticated could INSERT/UPDATE/SELECT token rows. See PERMISSIONS.md.

Action Types:

Action Type	Metadata.record_ids Structure	Purpose
approval_review	[{approval_instance_id, approval_request_id}]	Approver reviews a single submission
approval_review_bulk	[{approval_instance_id, approval_request_id}, ...]	Approver reviews multiple items from digest
query_response	[{approval_request_id}]	Submitter responds to a query