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.
Transaction ownership and access
Section titled “Transaction ownership and access”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.
Selected operations
Section titled “Selected operations”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.
Locks, revisions and constraints
Section titled “Locks, revisions and constraints”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.
Atomicity, concurrency and replay
Section titled “Atomicity, concurrency and replay”| 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
Where the transaction stops
Section titled “Where the transaction stops”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
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.
Failure and recovery reference
Section titled “Failure and recovery reference”| 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.