Views package queries, materialized views persist query results, functions return values or sets, procedures support call-oriented operations, and triggers react to row or statement events. These tools centralize important behavior, but hidden writes, unsafe privileges, refresh gaps, recursion, and migration coupling can make them harder to operate than explicit application workflows.
Put logic closest to the invariant but keep behavior discoverable. Database logic is shared by every writer; application logic is easier to version with a service. Neither is automatically superior.
A view can narrow columns and stabilize a read contract, but permissions and row security must still be understood. A materialized view stores results and must be refreshed; concurrent refresh has prerequisites and does not make freshness automatic.
Functions can encapsulate an atomic transition. Prefer invoker rights. If SECURITY DEFINER is truly required, set a safe search_path, schema-qualify objects, minimize owner privileges, revoke default execution as needed, validate caller context, and audit.
Triggers are useful for cross-writer audit rows or maintaining a value that must never diverge. Avoid them for remote calls, complex orchestration, or behavior the team cannot observe. Trigger work adds to the original statement’s latency and transaction.
Expose safe semantic views/functions to model tools instead of base tables: get_ticket_context(tenant, ticket) is more governable than arbitrary SQL. The function must still derive tenant identity from authenticated context, bound output, avoid SECURITY DEFINER escalation, and record the tool call separately.
Create a rollbackable view that excludes message bodies and verify its columns through information_schema. Test access with a limited role. For any trigger, demonstrate one-row, multi-row, failed-statement, and bulk behavior and show how to disable/rebuild safely in an isolated environment.
Design a read-only ticket_ai_context view with only authorized prompt fields and no customer email. Decide how tenant scope is applied.
Acceptance criterion: limited role cannot read base tables or another tenant and the view never includes excluded sensitive columns.