Skip to content

Functions and transaction boundaries

A database transaction groups changes that must commit or roll back together. Toolora uses database functions, constraints and triggers for consequential multi-record operations. Ordinary browser writes also run in database transactions, but several HTTP calls do not become one transaction because they belong to the same screen or FastAPI handler.

The record model identifies the owning facts; row-level security explains execution identity and access. This reference identifies selected integrity boundaries. Business prerequisites and user journeys remain in their owning workflow articles.

A Data API write or RPC executes within its request transaction. Nested SQL function calls and triggers participate in that transaction; returning from an inner function is not a separate commit. A later uncaught database error rolls back its changes. A handled exception can have narrower savepoint behaviour, so inspect the actual handler rather than assuming every error escapes. PostgreSQL transactions and PostgREST request transactions define those general mechanisms.

An API service making two Supabase requests has two database transactions. The service-role client does not supply a transaction across those calls. A Python exception after the first successful call does not undo that committed request. A browser cache refresh, response acknowledgement, Storage upload, provider call or user review also has its own boundary.

Authorization and integrity remain separate requirements. save_invoice_draft is an invoker function and keeps the caller’s RLS/grants. The selected service-only definer RPCs below require scoped API entry points and explicit company/resource arguments. Their execute grants, fixed search paths and internal checks differ. An atomic write does not authorize its caller. API identity/capability lookup itself uses privileged reads; route policy governs the ordering before business work.

The names below identify current functions or handler paths. The source details include their definitions and later replacements. Earlier migrations can contain obsolete signatures or an earlier implementation of the same name.

Operation and entry Execution and scope Atomic records and concurrency control Replay and outer boundary
Quote editor → save_quote_draft_api Scoped API; service-only definer wrapper and draft function Quote row lock, expected revision, grouped fields/lines/recipient allocation and current VAT calculation Stale draft is refused. Invitation delivery and acceptance are separate operations.
Named response → respond_to_quote_approver Public invitation API validates the token; service-only definer RPC validates response/round Quote and approval-round locks; response, selected options and final acceptance/handoff when the rule is satisfied Matching response can acknowledge existing evidence. A partial response remains pending; notification enqueue is not delivery.
Accepted quote → create_project_from_accepted_quote Scoped API or acceptance path; service-only definer Lock quote, then existing project; create/reuse project and accepted billing basis, with source-key uniqueness and integrity checks Same quote reuses its live project; new overrides are not reapplied on replay. Historical inconsistencies can refuse recovery.
Work-order line → source-allocation trigger The caller’s write plus trigger-enforced company/source rules A transaction advisory lock on the source basis ID serializes whole-line allocation; reject another live order using it This reserves execution instructions, not invoice consumption or performed time. The surrounding screen can make several requests.
Principal award → award_ue_job_application Scoped principal-staff API; service-only definer Lock listing and application; claim winner, create/reuse commitment and supplier relationship, enqueue notifications and audit Same winner returns the live commitment. Another winner conflicts. Work-room use and actual notification delivery happen later.
Project invoice → create_project_invoice_from_basis Scoped API; service-only definer wrapper and internal creation function Basis locks in UUID order plus inner project/basis checks; header, lines, deductions and consumption commit together No general caller-key replay. Another partial invoice may be valid while basis remains. Reload after an uncertain response.
Invoice editor → save_invoice_draft Browser caller under RLS; authenticated/service execute grants Lock invoice, compare updated_at, save supplied/deleted lines and then header; triggers continue to enforce immutability Supplied line IDs are not an acknowledgement key. Stale revision refuses the grouped save.
Accounting handoff/send request → enqueue_fortnox_invoice_export / request_invoice_customer_send Scoped API; service-only definer operations Invoice/export locks; validated frozen source and export intent. Send request binds company key, invoice and expected document hash Worker processing, provider effects and their receipts are later transactions. Reusing a key for different content conflicts.
Credit → create_credit_note Scoped API; service-only definer, with current credit triggers Lock original invoice and existing request; create credit header/lines/reversal evidence together Company request key and normalized payload identify replay. Full ROT/RUT reversal, locking and replacement-draft rules remain separate checks.
Payment page → apply_accounting_payment_event_page Privileged feed service; service-only definer with binding check Lock selected connection/feed state; validate expected revision; upsert payment rows and advance checkpoint together Natural source keys handle repeated events; a stale page is refused. Register coverage and settlement are separate observations.

Quote acceptance, subcontractor awards and invoicing and settlement own the business rules behind these operations. The matrix is not a promise that every related direct write acquires every listed parent lock. Check the path actually used.

A row lock coordinates transactions that contend for that row. It does not lock all related records or stop a plain read of a committed version. A transaction advisory lock coordinates participants using the same application key; PostgreSQL does not automatically apply that convention to unrelated writers. Transaction locks end at commit/rollback. Different lock orders can still deadlock; the selected invoice wrapper sorts basis IDs, but that does not prove a single lock order across the whole system. See PostgreSQL locking.

Revision checks answer a different question: whether the caller edited the version it read. save_invoice_draft first locks the invoice and compares its updated_at to p_expected_updated_at. It rejects invoice_revision_changed before saving a stale batch. Quote draft conflict handling maps the known quote_draft_conflict to P0001, preserving a deliberate editor conflict rather than inviting an automatic serialization retry. Revisions are operation contracts, not hashes of every related row. A 40001 from a named stale-basis or feed-page check can mean that its business input must be reloaded; the SQLSTATE alone is not an instruction to repeat every request automatically.

Constraints and triggers enforce additional rules beneath callers. The accepted project handoff uses the (fk_project, fk_quote_line_item) source key when materializing basis. The allocation trigger verifies company, acceptance, selected optional rows and the source relationship before rejecting reuse by another non-deleted order. Cancelled and deleted orders have different effects. Invoice line and deduction triggers retain issue/export immutability and source integrity. None of these checks converts a source-only quote into accepted work. Commercial authority owns that distinction.

next_tenant_number(uuid, text) is a separate allocator. It maps known sequence names to capabilities, checks company access and atomically inserts or advances tenant_number_sequences using its company/name key. The commitments allocator also catches up to persisted commitment numbers. It is not a browser-side MAX()+1 calculation. Called inside a failing handoff transaction, its table update rolls back with the project. Called in a separate successful RPC before another request, its result has already committed. Do not infer gapless business numbering from atomic allocation.

Property Question it answers Toolora example and limit
Atomicity Do the grouped database changes succeed together? A failed invoice batch leaves neither its first new line nor its header change. It says nothing about later email delivery.
Concurrency control Can competing operations both claim an incompatible state? Listing/application locks produce one UE award. Invoice revision checks reject an editor working from old data.
Idempotency Can the same identified request acknowledge the existing result? Same-winner award or matching credit key can return stored work. A new project-invoice ID has no equivalent caller-key guarantee.
Outcome evidence What is known after a request or provider response is lost? Stored invoice/send/export state and receipts support reconciliation. A timeout alone establishes neither rollback nor delivery.

These properties must be checked independently. In particular, a transaction that successfully creates an invoice can commit before its HTTP response is lost. Repeating a partial-basis request can create another invoice if enough basis remains. A source-row key identifies the source; it does not necessarily identify the user’s request to consume a particular portion.

A synthetic award illustrates actual lock contention. Staff A awards application A while staff B awards B for the same listing. A locks the listing before B can claim it. After A commits, B sees the award pointer and is refused. Replaying A returns its commitment while current API authorization still applies. If A’s transaction fails while creating the commitment, its winner/application changes roll back too. The current RPC includes commitment creation; the earlier claim-only function is not the current contract.

sequenceDiagram
participant A as Staff request A
participant D as Database
participant B as Staff request B
A->>D: Award application A, lock listing
B->>D: Award application B, wait for listing
A->>D: Accept, create commitment, queue notification
D-->>A: Commit result
D-->>B: Resume, see existing winner, reject
A->>D: Replay same winner after uncertain response
D-->>A: Existing commitment and replay flag
Competing award transactions

Worker completion uses a narrower conditional update. The API verifies current worker/assignment/publication access, reads the order and checklist evidence, then updates the order only if the observed status still matches. An already completed order acknowledges its original timestamp/note. A competing status change yields a conflict or the stored completion. Checklist and assignment reads are separate database requests: this is not an atomic barrier against every concurrent evidence or assignment change. Execution and review records the same limit for staff project readiness and UE closure.

Similarly, separate contact membership/profile HTTP updates can partially succeed. Customers and contacts identifies their exact person, customer and company scope. Do not describe a successful first update as a transaction covering the second request.

Accounting and delivery separate durable intent from external action. The export worker claims local work, calls the provider and records a result under its claim. The invoice delivery service freezes document bytes/recipient and conditionally claims the delivery before calling email. A later database rollback cannot retract a provider invoice or delivered message. A stuck submission can become submission_unknown; its recovery must reconcile the outcome rather than blindly resend. A unique local key alone cannot prove exactly-once external execution. External outcomes and the settlement workflow own those contracts.

flowchart TD
U[Caller and reviewed input] --> A[API authorization and resource scope]
A --> T[RPC transaction: locks, checks, grouped records and durable intent]
T --> C[Commit database state]
C --> R[HTTP acknowledgement: can be lost]
C --> W[Later worker claim]
W --> E[Provider effect outside that transaction]
E --> P[Separate receipt or outcome reconciliation]
P --> O[Observed result]
R --> Q[Uncertain caller: inspect stored result before retry]
Q --> O
Database transaction and external effect boundaries

Payment reads have another atomic unit. A validated page updates natural-key projection rows and the feed cursor/revision in one transaction. The current function checks the selected read binding, including its explicit read-company override. A stale revision fails with accounting_payment_feed_stale_page. Repeated events can update one source row while a new page advances the state. The opaque cursor is not an ordered timestamp. Register parity/coverage is a separate, expiring proof tied to a revision; neither an empty page nor an atomic checkpoint establishes that an invoice is settled.

Result or failure Persisted boundary Caller recovery
invoice_revision_changed / quote_draft_conflict (P0001) The rejected grouped save does not replace the newer edit Retain the local draft; load and reconcile current values before saving again.
invoice_line_not_on_invoice (22023) after an earlier line in the batch The batch rolls back, including that earlier line and header changes Correct the line identity/scope. Do not split the batch to force partial success.
ue_job_listing_already_awarded The committed winner and its commitment remain Reload the award; a different winner is a new business decision, not a retry.
project_invoice_basis_stale (40001) This failed invoice transaction leaves no partial new invoice/consumption Reload basis and linked invoices. A lost response from an earlier successful call is a different case.
Reused credit/send key with another payload Existing request remains authoritative; conflicting use is rejected Recover the original request or create a deliberate new request under the owning workflow.
Feed revision mismatch or invalid event Projection/checkpoint changes in that page roll back Reload feed state and fetch the appropriate page; preserve binding and coverage checks.
Timeout after commit or unknown provider submission Database work or an external effect may already exist Inspect stored identity, status, hash and receipts; use the operation’s reconciliation path before retrying.

Existing SQL tests cover a successful invoice save, stale editor rejection and a batch whose first valid insertion is followed by a foreign-invoice line. They assert unchanged header/lines after failure under an authenticated caller. Other tests use separate connections to demonstrate one concurrent award, one accepted project handoff, whole-source allocation and full invoice consumption. Those are specific interleavings, not exhaustive proof of every race.

Some suites execute as database owner or install historical function bodies. Their results have that scope. Mocked API/provider tests can verify call ordering and recovery decisions without executing current SQL or a provider. The delivery PR distinguishes executed current-candidate checks from reused results with proved identical application inputs. No provider write or shared financial attest is needed to explain this reference. Existing API/SQL resource-scope differences, portal and UE projection limits, MCP limits and financial acceptance gaps remain with their owning articles.