AI & Processing System Tables

AI Integration Tables

AI Functions (ai_functions)

Purpose: Stores AI functions available for WhatsApp bot and other AI services

Use Case Example: Define "DisplayMenu" function that allows Sana to send WhatsApp interactive menus, or "SpendQuery" that analyzes spending by supplier.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Unique function name (e.g., DisplayMenu, GetBudgetInformation)
description	TEXT	Detailed description of what the function does
system_prompt	TEXT	System prompt that defines function behavior
inputschemajson	JSONB	JSON Schema defining the function input schema
required_permissions	TEXT[]	Array of permission keys user must have to use this function
need_location	BOOLEAN	Whether function requires user location data
always_use_latest	BOOLEAN	If true, only use most recent result in conversation
is_active	BOOLEAN	Whether function is currently available for use
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Defines available AI capabilities
• JSON Schema validation for parameters
• Permission-based access control
• Support for external API endpoints
• Function behavior flags (location, latest results, agent triggers)

AI Prompts (ai_prompts)

Purpose: Stores AI prompt templates for WhatsApp bot and other AI services

Use Case Example: "GatewayPrompt" defines Sana's personality and behavior, while "SummaryPrompt" creates conversation summaries.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Unique prompt name (e.g., GatewayPrompt, SummaryPrompt)
description	TEXT	Description of the prompt purpose
system_prompt	TEXT	System prompt that sets AI behavior and context
user_prompt	TEXT	User prompt template with {!variables} for substitution
model_config	JSONB	Model config: {model, max_tokens, config: {...params}}
outputschemajson	JSONB	JSON schema defining the expected output format for the AI prompt
need_menu	BOOLEAN	Whether to include menu options in response
is_active	BOOLEAN	Whether prompt is currently available for use
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Template-based prompt management
• Model configuration per prompt
• Variable substitution support
• Menu integration flags

AI Prompt Functions (ai_prompt_functions)

Purpose: Junction table linking AI prompts to available functions

Use Case Example: GatewayPrompt has access to all 8 functions (DisplayMenu, GetLoggingInformation, etc.) in a specific order.

Column	Type	Description
id	UUID	Unique identifier
ai_prompt_id	UUID	Reference to AI prompt
ai_function_id	UUID	Reference to AI function
display_order	INTEGER	Order in which functions are presented to the AI
created_at	TIMESTAMPTZ	Creation timestamp

Key Features:

• Many-to-many relationship between prompts and functions
• Ordered function presentation
• Unique constraint on (ai_prompt_id, ai_function_id)

Processing System Tables

Process Templates (process_templates)

Purpose: Define processing workflows (both AI and manual validation) for different entity types

Use Case Example: Create "Transaction Import Processing" template with OCR, classification, extraction, validation steps, and manual checks like total verification.

Column	Type	Description
id	UUID	Unique identifier
name	TEXT	Template name (e.g., "Transaction Import Processing")
description	TEXT	Description of what this template processes
processable_type	TEXT	Entity type: transactions, budgets, or schedules
service_name	TEXT	Name of the service that handles processing
is_active	BOOLEAN	Whether this template is currently available
metadata	JSONB	Additional template configuration (e.g., {"notification_on_completion": true})
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• One active template per processable type
• Defines multi-step processing workflows (both AI and manual)
• Metadata for template-specific configuration
• Example metadata: {"notification_on_completion": true} to notify users when processing completes

Process Template Steps (process_template_steps)

Purpose: Define individual steps within a processing template (AI or manual)

Use Case Example: AI steps like OCR extraction, manual validation steps like checkTransactionTotals, or transformation steps.

Column	Type	Description
id	UUID	Unique identifier
process_template_id	UUID	Reference to parent template
step_key	TEXT	Unique key for this step (e.g., "ocr", "check_totals")
display_name	TEXT	User-friendly name for status display
description	TEXT	Detailed description of what this step does
type	process_step_type	Type of step: 'ai' (AI Hub) or 'manual' (custom validation/processing)
display_order	INTEGER	Display order for steps in the UI (lower numbers appear first)
display_in_ui	BOOLEAN	Whether this step should be displayed in UI progress indicators (default: true)
ai_hub_step_type	TEXT	Type of AI processing step (PascalCase) - for AI steps only
ai_prompt_id	UUID	Optional reference to AI prompt - for AI steps only
processor_method	TEXT	Method name in the service to call when processing this step
execution_group	INTEGER	Steps with same group can run in parallel
depends_on_steps	TEXT[]	Array of step_keys this step depends on
is_active	BOOLEAN	Whether this step is active or not
can_run_parallel	BOOLEAN	Whether this step can run in parallel with others in its group
expected_duration_seconds	INTEGER	Expected duration for progress estimation
max_retries	INTEGER	Maximum number of retry attempts if step fails
timeout_seconds	INTEGER	Maximum time allowed for step execution before timeout
condition_expression	TEXT	SQL-like expression evaluated in app to determine if step should run
condition_description	TEXT	Human-readable description of when this step runs
on_failure_action	TEXT	What to do if this step fails: fail_job, skip_dependents, or continue
on_retry_action	JSONB	Retry configuration: {switch_models: [model1, model2], raise_max_tokens_by: 2000}
metadata	JSONB	Step-specific configuration
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Supports both AI and manual processing steps via type field
• AI steps send requests to AI Hub, manual steps run custom validation logic
• Supports parallel and sequential execution
• Conditional execution based on previous results
• Optional steps that don't block on failure
• Display control via display_in_ui flag (useful for internal processing steps)
• Manual steps can create issues for user resolution
• Retry configuration via on_retry_action:
• switch_models: Array of models to cycle through on retries (cycles with original model)
• raise_max_tokens_by: Amount to increase max output tokens per retry (no cap, increases linearly)

Execution groups must be homogeneous — a trigger_validate_execution_group_homogeneity trigger rejects any group holding both ai and manual steps. Because it fires per-row, a bulk execution_group + 1 shift to insert a step mid-pipeline transiently collides; park the affected rows far above every real group first (+ 1000) then bring them down (- 999), all in one transaction. display_order is a separate UI-ordering field (independent of execution_group); set it explicitly for a new step.

Currency-rate check (Multi-Currency Conversions V1): the "Transactions Processing" template runs transaction_currency_rate_check (manual) at execution group 3 — right after transaction_ocr_extraction (which extracts the document currency) and before any entity/type/budget matching. When the transaction currency differs from the project currency and the project has no project_currency_rates row for it, the step puts the transaction On Hold and processing stops (all downstream matching is skipped) until a rate is set.

Line-item processing mode: the "Transactions Processing" template runs transaction_line_item_summarisation (manual, processLineItemSummarisation) at execution group 7 — after transaction_type_discovery (the mode is resolved per transaction type) and before every step that consumes the line-item set. It applies the project's per-type mode (projects.metadata.transaction.<type>.line_item_processing), replacing the item set through replace_transaction_line_items and stamping transactions.line_item_mode. Its insertion shifted the previously-group-7-and-above steps up by one, so the template now holds 16 steps across 12 groups, and transaction_budget_item_matching, transaction_justification_analysis and transaction_chart_of_accounts_matching each depend on it — they must never match against a line-item set the mode has not yet settled. on_failure_action is fail_job: nothing downstream is meaningful without a final item set.

Transaction Regenerate Line Items template: a targeted (processable_type = 'transactions', service_name = 'transaction') reprocessing pipeline that re-derives a transaction's line items under a chosen mode and re-runs only the matching that consumes them. Six steps: transaction_line_item_regeneration (manual, processLineItemRegeneration, group 1, fail_job) → transaction_check_totals_regenerate (manual, processTransactionTotalCheck, group 2, continue) → transaction_budget_item_matching_regenerate (ai, group 3, gated on entity.project.current_budget_id IS NOT NULL) → group 4 in parallel: transaction_chart_of_accounts_matching_regenerate (ai, gated on entity.has_chart_of_accounts = 'true') and transaction_budget_daily_allocation_regenerate (ai, gated on the current budget having enable_daily_allocations) → transaction_finish_processing_regenerate (manual, group 5, depends on {all}). step_key is globally unique, hence the _regenerate suffixes; the AI steps reuse the main template's prompts. Every group is homogeneous (all-manual or all-ai) — the per-row trigger on process_template_steps rejects a mixed group, so inserting a step mid-template requires the park-high-then-bring-down shift rather than a plain execution_group + 1.

The regeneration step also dismisses the pending issues its replacement invalidates: after the replace RPC commits, processLineItemRegeneration marks PENDING processing_issues on the transaction as ignored for exactly the keys the re-run steps raise (transaction_totals_mismatch, transaction_unmatched_budget_items, transaction_unmatched_chart_of_accounts, transaction_unmatched_budget_daily_allocation_dates, transaction_no_budget_daily_allocation_dates). Without it the dedup in createProcessingIssues would collapse a fresh raise into the stale pending row (leaving context data describing items that no longer exist), and a problem the rebuild actually fixed would leave its issue dangling forever. The dismissal is post-replacement hygiene, not a required side effect — a failure is recorded in the step's superseded_issues audit payload and never fails the step.

Processing Jobs (processing_jobs)

Purpose: Track processing job instances (AI and manual)

Use Case Example: When a user uploads a receipt, create a job to process it through OCR, classification, data extraction, and validation checks.

Column	Type	Description
id	UUID	Unique identifier
processable_id	UUID	ID of the entity being processed
processable_type	TEXT	Type of entity: transactions, budgets, or schedules
process_template_id	UUID	Reference to the template used
overall_status	TEXT	Status: pending, in_progress, completed, failed, partial_success
progress_percentage	INTEGER	Overall progress (0-100)
error_message	TEXT	Error message if job failed
metadata	JSONB	Job-specific metadata
project_id	UUID	Reference to the project this job belongs to (nullable)
current_processing_group	INTEGER	Tracks which execution group is being processed (prevents race conditions, nullable)
restart_count	INTEGER	Counter incremented each time job is restarted, used to detect stale callbacks (default: 0)
current_processing_group_claim_id	UUID	Opaque owner token for the current execution-group claim; a fenced release/re-dispatch must present a matching value (nullable)
current_processing_group_claimed_at	TIMESTAMPTZ	When the current execution-group claim was taken (nullable)
sweep_attempts	INTEGER	Stall-recovery attempts for the CURRENT stall episode; reset when progress is observed (NOT NULL, default: 0)
sweep_fingerprint	JSONB	Progress fingerprint at the last sweep: {"terminal_steps": int, "max_terminal_group": int\|null} (nullable)
last_swept_at	TIMESTAMPTZ	When the stall-recovery sweeper last claimed this job (nullable)
started_at	TIMESTAMPTZ	When processing started
completed_at	TIMESTAMPTZ	When processing completed
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp
created_by_user_id	UUID	User who initiated the job

Key Features:

• Tracks overall job progress for both AI and manual processing
• Polymorphic design for different entity types
• Metadata for job-specific data
• Can create processing issues during validation steps
• Uses current_processing_group for atomic execution group locking to prevent race conditions when multiple AI callbacks complete simultaneously
• Uses restart_count to detect and discard stale callbacks from previous job runs when a job is restarted

Row Level Security: the SELECT policy admits a row when the caller created it (created_by_user_id) OR holds transaction:view:all on the row's project — and, as a separate top-level conjunct, only when the caller can also SEE the processable record (see "Record-visibility conjunct" below). The permission half is evaluated in the set-based bounded form — project_id = ANY (SELECT unnest(caller_accessible_project_ids(ARRAY['transaction:view:all'], false))) — not the per-row caller_has_permission(…, project_id, false) probe it replaced. The two are equivalent in visible-set terms (both are DEFINER walks of v_user_permissions under identical status/expiry/relationship filters, and a NULL project_id is non-admitting in both, so a project-less job is creator-visible only), but only the = ANY (SELECT unnest(…)) shape is evaluated independently of candidate-row count: it becomes a hashed subplan built a constant number of times per statement instead of one full permission walk per row. On a 3019-job fixture the status-bar list query went from 289 ms to 1.3 ms. See .claude/rules/rls-policies.md § "Bounded Access-Primitive Evaluation" — a bare = ANY (fn(…)) silently reverts to per-row evaluation in scan plans, so the predicate is pinned by a full-predicate equality sentinel in tests/06_rls_policies.sql and a per-persona visible-id matrix in tests/07_access_primitive_semantics.sql. INSERT is self-attribution only (created_by_user_id = auth.uid()); all lifecycle writes go through the service-role-only atomic RPCs.

Record-visibility conjunct. Holding transaction:view:all on a project does not entitle you to read every record in it — sensitivity restrictions and the pre-pipeline window gate (see SENSITIVE_DATA_SYSTEM.md) hide individual transactions from cleared-for-the-project callers. So the job policy AND-s a record-visibility test on top of the project/creator test:

AND CASE processable_type
      WHEN 'transactions' THEN EXISTS (
          SELECT 1 FROM public.transactions t WHERE t.id = processable_id)
      ELSE false
    END

Three properties are load-bearing:

• It inherits, it does not restate. The public.transactions reference is RLS-filtered for the querying role, so the conjunct picks up the transactions SELECT policy's permission membership, its sensitivity restriction twin (private.caller_restricted_transaction_ids) and its window gate automatically — and keeps inheriting any arm added there later. There is no forked copy of the sensitivity logic to drift. No recursion risk: the transactions policy references user_accesses / sensitive_rules / projects and no processing table.
• The creator is NOT exempt. The conjunct sits at top level, outside the creator-OR-permission disjunction — the same fail-closed choice the window gate makes. Creating a job never entitles you to read the record it processes, which matters precisely because the window statuses are the ones jobs run against.
• Unhandled processable_types are invisible (fail-closed). processable_type is an enum (transactions, budgets, schedules, entity_attachments) and only the transactions arm resolves; everything else hits ELSE false. A type whose visibility rule has not been expressed here is unreadable rather than readable-by-default. Adding a type means adding its arm. Service-role/pipeline access is unaffected (RLS does not apply to service_role), so processing itself never breaks — this is a read-surface gate only.

Boundedness survives: the plan renders the conjunct as a hashed subplan whose transactions scan reports loops=1, i.e. once per statement, not once per candidate job row. Before this conjunct existed the browser received job metadata, progress and step counts for records the transactions policy correctly refused to show, and the UI compensated client-side by dropping jobs whose related item was missing from the payload (dropJobsWithoutRelatedItem in ProcessingStatusBar) — cosmetic only, since the data had already crossed the wire. Enforcement now happens in the database, and the client flag has been removed — a job the caller may not see never reaches the browser, so there is no unreadable row left to suppress.

Status-bar read path (get_processing_job_status_summary)

The processing status bar reads jobs through ONE SECURITY INVOKER RPC, get_processing_job_status_summary (full contract in VIEWS_AND_FUNCTIONS.md → Processing Status Summary Function). It returns, per (processable_type, project_id), page one of five status buckets plus exact per-bucket counts, or one bucket's keyset-paginated next page.

It replaced a query that fetched EVERY non-terminal job for the project plus every job completed since the client's local midnight, each with the full embed tree — process_templates, all processing_job_steps, and each step's process_template_steps row — then grouped into buckets in JS, counted the group sizes, and discarded everything past the first 12 of each. The wire payload and the RLS work both scaled with the whole window while the UI rendered at most 60 rows, and the step rows carried request_data / result_data, the full AI prompt I/O (~40KB per job, comfortably 80%+ of the bytes). "Load more" re-ran the same fat select with an OFFSET, which re-reads and discards every skipped row.

The RPC picks page ids FIRST (ordered, limit-applied, ids only), then decorates only those ids, and computes counts as counts rather than as array.length after materialising rows. Measured at 3019 jobs / 18 285 steps on one project: 5 862 buffers against the fat select's 101 374 (17.3× fewer), with exact counts and a cursor the old shape did not provide. Because it is INVOKER it inherits the SELECT policy above in full — including the record-visibility conjunct — so an RLS-hidden job is absent from both the page and the count.

Indexes serving it. idx_processing_jobs_type_project_completed_at — partial on overall_status = 'completed', ordered (processable_type, project_id, completed_at DESC, id DESC) — serves the completed bucket, resolving both the completed_at range filter and the (completed_at, id) keyset cursor as index conditions (measured 1.76 ms vs 6.32 ms without it, at 13× production scale). The four non-completed buckets have no supporting index by design: the SELECT policy's record-visibility CASE cannot become an index condition, so every candidate row must be evaluated and an ordered index can never terminate early. A (processable_type, project_id, overall_status, created_at DESC, id DESC) index was built and benchmarked — the planner never chose it under RLS, making it pure write amplification on a table the pipeline updates on every step transition. Any future attempt must be re-measured under RLS; a service_role benchmark shows the same index winning at 0.036 ms, which is a plan the INVOKER RPC can never reach.

Stall recovery

A job can stall silently: an execution group is claimed but the callback that would advance it never arrives, leaving the job in pending/in_progress forever with no user-visible signal. Claim fencing and sweep bookkeeping address the detection side; the atomic lifecycle transitions below make every recovery action indivisible.

Claim ownership + fencing. current_processing_group_claim_id is an opaque owner token minted when an execution group is claimed, stamped alongside current_processing_group_claimed_at. A release or re-dispatch must present the matching token, so a stale worker that wakes up after its claim was reclaimed cannot advance or release the group it no longer owns. current_processing_group alone is monotonic and cannot express "this specific attempt".

The claim id travels in the callback token. It is minted into the token payload at dispatch alongside {jobId, restartCount} and echoed back opaquely by AI Hub, so a callback can fence its writes on the claim it was DISPATCHED under rather than on a re-read (a re-read would adopt whatever claim the live run owns now — precisely the run a superseded callback must not write into). It is a carried field, not part of the signature binding: {jobId, restartCount} remains what the signature attests, and verification reports the claim as null for a token minted before the field existed. update_processing_step_guarded and the five fenced domain-write RPCs assert it when supplied and skip the assertion when it is NULL — a nullable that exists only for the rollout window, since token lifetime is bounded by the dispatched steps' timeouts.

Sweep bookkeeping. A reconciliation cron scans live jobs by staleness (served by the partial index idx_processing_jobs_live_updated_at on updated_at WHERE overall_status IN ('pending','in_progress')) and calls claim_job_recovery (see VIEWS_AND_FUNCTIONS.md). The RPC records sweep_attempts, sweep_fingerprint, and last_swept_at.

The fingerprint — {"terminal_steps": int, "max_terminal_group": int|null} — is what distinguishes "still stuck in the same place" from "moved on and stalled again". When the live fingerprint differs from the stored one the pipeline genuinely progressed, so the attempt counter resets to 1 (a new stall episode) rather than continuing toward exhaustion. Without it, a job that intermittently progresses would burn through its retry budget and be abandoned despite making headway.

Retry exhaustion is durable: the incremented counter and fingerprint are persisted for the exhausted outcome too, so re-running the sweeper against an unchanged pipeline keeps reporting exhausted instead of retrying forever. Exhausted jobs raise the processing_stalled notification (in-app mandatory, WhatsApp opt-in — see NOTIFICATIONS.md) so the team can retry manually. A system-ingested job (email/Sana) has no created_by_user_id and therefore no recipient; that case is logged at error level for ops alerting instead. Giving those jobs a fallback recipient (e.g. the project's processing owners) is a named follow-up, not built here.

Atomic lifecycle transitions

Every lifecycle transition of a job — restart, retry-from-a-step, resume-from-a-group, fail-out of a stall, finalize a halted run — mutates the processing_jobs row AND its processing_job_steps rows together. Issued as separate PostgREST requests those writes auto-commit independently, so a concurrent transition can interleave between the job-row compare-and-swap that authorises the change and the step writes it authorises, leaving the winner's steps mutated by the loser. Each transition is therefore ONE RPC — restart_processing_job_atomic, resume_processing_job_atomic, fail_stalled_job_atomic, finalize_halted_job_atomic (see VIEWS_AND_FUNCTIONS.md) — performing the whole transition in one transaction under the same processing_jobs row lock that update_processing_step_guarded and claim_job_recovery take. A rejected transition writes NOTHING; an accepted one is indivisible.

Generation semantics. restart_count is the job's generation counter, and restart, retry AND resume all BUMP it — a resume is not a continuation of the halted run but a fresh claim on the job, because it resets the resumed group's steps regardless of status and re-drives that group itself. The bump is what retires the previous attempt's callback tokens (which bind {jobId, restartCount}), what lets the execution-group high-water mark be rewound safely, and what makes a second concurrent resume — or a sweeper's fenced finalize — lose cleanly. Each RPC returns the generation it won, and callers dispatch under THAT value: a re-read can pick up a concurrent restart's generation and drive a group claim under someone else's run.

Terminal jobs are closed to step writes. update_processing_step_guarded refuses with job_terminal when the parent job has already reached a terminal overall_status on the same generation, so a step write arriving after a fail-out or a halted-finalize cannot reopen a settled job's step set behind its written verdict. Only an explicit restart (a new generation) revives such a job.

Processing Job Steps (processing_job_steps)

Purpose: Track individual step execution within a job

Use Case Example: Track that OCR completed successfully in 2 seconds, classification identified "Receipt", validation found total mismatch requiring resolution.

Column	Type	Description
id	UUID	Unique identifier
processing_job_id	UUID	Reference to parent job
process_template_step_id	UUID	Reference to template step definition
status	TEXT	Status: pending, ready, in_progress, completed, failed, skipped, not_needed
result_data	JSONB	Output data from this step
request_data	JSONB	Request sent to AI model (sanitized, without base64 data)
error_message	TEXT	Error details if step failed
retry_count	INTEGER	Number of times this step has been retried
duration_seconds	INTEGER	Actual execution duration in seconds
started_at	TIMESTAMPTZ	When step execution started
completed_at	TIMESTAMPTZ	When step execution completed
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Key Features:

• Tracks individual step progress for both AI and manual steps
• Stores step results for use by subsequent steps
• Error tracking for debugging
• Supports conditional execution with not_needed status
• Manual validation steps can create processing issues

Row Level Security: a step row carries no project_id of its own, so its visibility rides its parent job through an EXISTS on processing_jobs — the same creator-OR-transaction:view:all test the job policy applies, in the same set-based bounded form (pj.project_id = ANY (SELECT unnest(caller_accessible_project_ids(ARRAY['transaction:view:all'], false)))). A step whose parent job is invisible is invisible.

The job policy's record-visibility conjunct reaches steps without being restated here: Postgres injects the processing_jobs SELECT policy onto the inner pj scan, so the parent's conjunct is already applied when the EXISTS is evaluated. Restating it on pj. was measured and rejected — it adds a third hashed-subplan build of the transactions-visibility probe (the steps statement already carries it twice) for no semantic change: 83.2 ms / 561,947 buffers versus 77.5 ms / 363,939 for the parent-only shape. The behavioural proof that steps really are gated lives in tests/07_access_primitive_semantics.sql and tests/17_pre_pipeline_sensitive_window.sql, not in the steps predicate itself.

This policy was the costlier of the pair to leave per-row: the EXISTS runs once per candidate step, and Postgres injects the processing_jobs SELECT policy onto the inner pj scan, so the pre-rewrite predicate ran a full permission walk twice per step row. On an 18285-step fixture the joined read went from 4038 ms / 7.55M buffers to 19 ms / 64K buffers. The per-step EXISTS probe itself is unchanged and inherent to the shape — what changed is that each iteration now probes a pre-built hash set instead of re-deriving the caller's permissions. Pinned by the same two test files as the job policy. INSERT is limited to steps of the caller's own jobs.

Processing Issue Templates (processing_issue_templates)

Purpose: Registry of every processing-issue kind the pipeline can raise. Owns each key's default display copy, severity, resolvability and capability flags, plus its applicability and per-project configurability.

Use Case Example: The tax-number validation step raises transaction_missing_or_wrong_tax_number; the registry row supplies the title, high severity, can_send_query = true, and the fact that the check applies only to Invoice / Payroll Invoice / Unknown transactions.

Column	Type	Description
id	UUID	Unique identifier. Referenced by processing_issues.issue_template_id
key	TEXT	Machine-readable issue key, globally UNIQUE and namespace-prefixed by convention (e.g. transaction_*). The only place the key is stored
processable_type	TEXT	Table name of the record kind the issue is raised against (default transactions). TEXT, not an enum — the values are table names
display_name	TEXT	Static human-readable label for admin surfaces (settings UI). Never carries {0} placeholders, unlike title
title	TEXT	Default issue title. May carry {0}-style placeholders the raising code substitutes into title_override
description	TEXT (nullable)	Default issue description. May carry {0}-style placeholders
severity	processing_issue_severity	Default severity. Per-raise variation lives in processing_issues.severity_override
submitter_resolvable	BOOLEAN	Default for whether the submitter can resolve the issue (true) or it needs admin/approver review (false)
can_ignore	BOOLEAN	Whether the issue may be dismissed without resolution. false makes it blocking for approval
can_send_query	BOOLEAN	Whether the resolution UI offers sending an approval query for this kind
can_replace_document	BOOLEAN	Whether the resolution UI offers replacing the source document for this kind
configurable	BOOLEAN	Whether projects may enable/disable the issue per transaction type via project_issue_settings. Non-configurable templates ignore override rows
applies_to_transaction_types	transaction_type[] (nullable)	Types the issue applies to by default. NULL means every type. No direction dimension — no issue kind differs between inbound and outbound invoices
settings_hint	TEXT (nullable)	Static one-sentence explanation shown beside the toggle in the project settings UI. Required for configurable templates
is_active	BOOLEAN	false disables the template entirely: never raised, never configurable, hidden from the settings UI. Used for auto-registered legacy keys
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints and indexes:

• processing_issue_templates_configurable_needs_hint CHECK (NOT configurable OR settings_hint IS NOT NULL) — a configurable template with no hint would render a toggle with no explanation. It is a structural invariant of the row, not a closed value domain, so it is a CHECK rather than an enum.
• idx_processing_issue_templates_processable_type on (processable_type) WHERE is_active = true — the raise-time and registry-read path never wants inactive rows.
• idx_processing_issue_templates_configurable on (processable_type) WHERE configurable = true AND is_active = true — serves the project settings UI, which lists only the overridable templates.

This table is the SOLE home of the issue key. processing_issues references a template by id (ON DELETE RESTRICT — a template with live issues must not vanish), so an unregistered key has nothing to point at and cannot be raised: the app chokepoint throws on it. Adding a new issue kind therefore REQUIRES a registry seed row in a migration. Registry content is reference data maintained exclusively by migrations — authenticated holds SELECT only (RLS: any authenticated user may read), every write path is service-role.

Project Issue Settings (project_issue_settings)

Purpose: Sparse per-project overrides of issue applicability. One row per (project, issue template, transaction type, direction) that DIFFERS from the template default; an absent row means the template's own applies_to_transaction_types applies.

Use Case Example: A project that receives hand-written receipts as Unknown invoices switches off transaction_company_name_mismatch for that type; every other type keeps the registry default.

Column	Type	Description
id	UUID	Unique identifier
project_id	UUID	Project the override applies to (FK → projects, CASCADE)
issue_template_id	UUID	Template being overridden (FK → processing_issue_templates, CASCADE). Must be configurable AND active
transaction_type	transaction_type	Transaction type the override applies to
direction	transaction_direction (nullable)	Invoice direction the override applies to. NOT NULL exactly when transaction_type is Invoice
enabled	BOOLEAN	true raises the issue for this kind even if the template excludes it; false suppresses it even if the template includes it
created_by_user_id	UUID (nullable)	User who created the override (DEFAULT auth.uid())
updated_by_user_id	UUID (nullable)	User who last updated the override (DEFAULT auth.uid())
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Constraints:

• project_issue_settings_direction_matches_type CHECK ((transaction_type = 'Invoice') = (direction IS NOT NULL)) — a settings row is scoped to exactly one product-level transaction kind, and Invoice is the only type that splits by direction. Without it the same kind would be addressable by two different rows and the evaluator's lookup would be ambiguous. This is a cross-column relational invariant, not a value domain (both columns are already enums).
• project_issue_settings_unique_override UNIQUE NULLS NOT DISTINCT (project_id, issue_template_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 for the same (template, type) pair with no way to say which one the evaluator should honour.

Referenced template must be configurable and active — enforced by the project_issue_settings_assert_configurable BEFORE INSERT/UPDATE trigger, which raises naming the offending key. Defense in depth for direct PostgREST writes: an override pointing at a non-configurable or inactive template would be silently ignored by the applicability evaluator, which reads to a user as "the toggle did nothing".

Row Level Security: SELECT is ordinary project membership (v_user_accessible_projects) — reading which validations a project runs is not privileged. INSERT/UPDATE/DELETE require project:edit, the same shape the budgets/schedules write policies use, because a change here alters which validations run on real documents.

Process Template Issue Actions (process_template_issue_actions)

Purpose: What a process template does to a registered issue kind's PENDING rows when that template is dispatched.

Use Case Example: Transaction Regenerate Line Items re-derives the whole line set, invalidating every verdict derived from the previous one, so it is seeded with ignore actions for the totals-mismatch, unmatched-budget-item, unmatched-COA and both daily-allocation-date issue kinds.

Column	Type	Description
id	UUID	Unique identifier
process_template_id	UUID	Process template whose dispatch triggers the action (FK → process_templates, CASCADE)
issue_template_id	UUID	Issue kind the action applies to (FK → processing_issue_templates, CASCADE)
action	processing_issue_template_action	ignore or supersede
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Action semantics — both affect PENDING rows only:

• ignore sets status = ignored, so the row leaves the pending set but stays visible in the record's issue history.
• supersede does everything ignore does and additionally clears is_current, so the row also drops out of the default list surfaces (which filter is_current = true) and is reachable only through the history view.

Constraints: process_template_issue_actions_unique_pair UNIQUE (process_template_id, issue_template_id) — one template can express at most one action per issue kind, so there is never a pair of contradictory rows to arbitrate between.

This is migration-maintained reference data. A template that configures nothing is a no-op at dispatch. Only the STATIC lists live here; the dynamic reconciliation that depends on the old and new transaction type plus the project's overrides deliberately stays in application code. authenticated holds SELECT only.

Processing Issues (processing_issues)

Purpose: Track validation and processing issues that require human resolution

Use Case Example: When total validation fails, create an issue with context (expected: $875.06, actual: $850.00) and suggested resolution, requiring user to fix before continuing.

Column	Type	Description
id	UUID	Unique identifier
processable_type	TEXT	Type of entity with issue: transactions, budgets, schedules
processable_id	UUID	UUID of the entity with the issue
project_id	UUID	Project the issue belongs to (NOT NULL, FK → projects). Denormalised for sweep filtering
processing_job_step_id	UUID (nullable)	Reference to the step that detected the issue (CASCADE). NULL for system-raised issues not tied to a job step
issue_template_id	UUID	NOT NULL FK → processing_issue_templates (ON DELETE RESTRICT). The registry row carries the key, copy, severity and capability flags
context_data	JSON	Additional context data for the issue stored as JSON (preserves key ordering, unlike jsonb)
status	processing_issue_status	Current status: pending, resolved, ignored
resolution_steps	JSONB (array)	Array of resolution actions tracking multi-step workflows (action history)
resolution_notes	TEXT	Optional notes from the person who resolved the issue
resolved_by_user_id	UUID	User who resolved or ignored the issue
resolved_at	TIMESTAMPTZ	Timestamp when issue was resolved or ignored
title_override	TEXT (nullable)	Per-raise title. NULL means the template title applies
description_override	TEXT (nullable)	Per-raise description. NULL means the template description applies
severity_override	processing_issue_severity (nullable)	Per-raise severity. NULL means the template severity applies
submitter_resolvable_override	BOOLEAN (nullable)	Per-raise submitter-resolvability. NULL means the template default applies
is_current	BOOLEAN	False when issue is from an old document that has been replaced (default: true)
created_at	TIMESTAMPTZ	Creation timestamp
updated_at	TIMESTAMPTZ	Last update timestamp

Normalized onto the registry. issue_key, issue_title, issue_description, severity and submitter_resolvable were DROPPED from this table — all five duplicated processing_issue_templates. The key now lives in exactly one place, so there is no cross-column consistency to enforce and the FK is a plain single-column reference. Read surfaces are unaffected by name: v_pending_processing_issues projects t.key AS issue_key from the registry join and exposes effective_title / effective_description / effective_severity / effective_submitter_resolvable = COALESCE(override, template default), with the raw override columns alongside so callers can tell a per-raise value from a registry default.

Consequence for new issue kinds: a raise with an unregistered key has no template to point at and is rejected. Adding an issue kind now REQUIRES a registry seed row in a migration.

Key Features:

• Flexible issue tracking across all entity types
• Links to processing jobs and steps for traceability
• Context data stores issue-specific information (JSONB for flexibility)
• Multi-step resolution tracking with resolution_steps array (supports workflows like query → response via approval system)
• Resolution tracking with user, timestamp, and resolution details
• Severity levels for prioritization
• Status tracking through resolution lifecycle (pending → resolved/ignored)
• Justification requests now use the approval query system instead of direct status changes

Row Level Security: the SELECT and UPDATE policies gate directly on the denormalised project_id — a single indexed probe into v_user_accessible_projects — uniform for job-full and job-less rows alike. (The historical 3-table walk through processing_job_steps → processing_jobs was only an indirect route to the same project-membership answer and could never match a job-less row, so system-raised issues were invisible.) Issue existence is project-membership-scoped; sensitivity row-exclusion applies to the underlying record, not its issue rows. Issues are inserted by service-role code only (authenticated writes are limited to self-attributed resolution updates via the UPDATE policy's WITH CHECK).

Example Context Data Structures:

// Total mismatch
{
  "transaction_total": 875.06,
  "line_items_sum": 850.00,
  "difference": 25.06
}

// Vendor match issue
{
  "field": "billing_entity_name",
  "current_value": "Desert Storm",
  "similar_vendors": [
    {"id": "uuid1", "name": "Desert Storm Equipment"},
    {"id": "uuid2", "name": "Desert Rentals LLC"}
  ]
}

// Format issue
{
  "field": "tax_number",
  "current_value": "92345679",
  "expected_format": "IN followed by 8 digits",
  "suggested_value": "IN92345679"
}

Example Resolution Steps Structure:

// Multi-step workflow: query via approval system → add explanation
[
  {
    "action": "query_sent",
    "timestamp": "2024-01-15T10:30:00Z",
    "user_id": "uuid1",
    "notes": "Sent query via approval system requesting justification"
  },
  {
    "action": "resolved",
    "timestamp": "2024-01-15T14:20:00Z",
    "user_id": "uuid2",
    "data": {
      "additional_explanation": "Camera rental for shoot #42"
    },
    "notes": "Added explanation from approval query response"
  }
]

Note: For detailed processing flow including issue creation and resolution, see PROCESSING_ARCHITECTURE.md and ISSUE_RESOLUTION_SYSTEM.md.