Records, relationships and projections
Toolora stores identity, accepted work, execution and financial evidence in separate records. A displayed fact must be interpreted through its owning record, company, source version and observation time. A valid identifier or a populated view does not by itself establish acceptance, authorization or complete payment evidence.
The examples below connect three representative parts of the model. The diagrams show selected relationships and derived lookups, not every foreign key. The domain model owns business meaning; capability and resource access owns operation authority. The database’s row-level security and transaction boundaries remain separate detailed subjects.
Identify the record behind the fact
Section titled “Identify the record behind the fact”| Displayed fact | Owning record or evidence | Interpretation boundary |
|---|---|---|
| Signed-in account and selected company | Auth identity plus live company/person membership and selected context | Login identity is not the customer or a contact relationship. |
| Customer/contact name and role | Global party identity, company profiles, customer relationship and person/customer membership | A label or email match does not grant portal access or merge identities. |
| Accepted commercial work | Exact quote version and its acceptance evidence, then accepted project billing basis | An imported source-offer link or project value is not acceptance. |
| Work instruction and source estimate | Work order, its line copy/source link and creation-time hour provenance | Current quote hours cannot reconstruct a missing historical copy. |
| Amount available to invoice | Accepted basis amount minus retained consumption, with release/restore rules | Work-order allocation, booking and approved time are separate facts. |
| Accounting document identity | Toolora invoice plus company/provider-bound external reference and operation receipt | A provider number alone is not a globally unique invoice or proof of booking. |
| Payment/settlement result | Payment projection, current coverage proof and applicable invoice observations | Feed catch-up or an empty row set alone does not establish completeness or zero. |
The schema is defined by the canonical baseline and subsequent active migrations. A baseline definition can have a later replacement. Generated TypeScript types mirror the checked schema’s fields, nullability and relationships; they do not encode every trigger, RLS policy or business precondition. Neither a type file nor a migration receipt proves the current state of a running database. Source review, release and observed behaviour retain their different evidence meanings.
Identity has global and company-specific records
Section titled “Identity has global and company-specific records”auth.users.id identifies a login account. people.pk_person identifies a person
record in one company. Its Auth link is nullable; the person-party and global
persons link are required. The same Auth account can have person records in
several companies. The unique (fk_tenant, fk_auth_user) index permits at most one
linked row per company/account, including soft-deleted rows. It does not establish
that the row is active or that it has a particular capability.
A parties row is the shared identity hub for a person, organization or household.
persons has a unique party reference for the global person subtype.
people.fk_person_global is synchronized from its person-party when the party link
is written; it supports direct embedding of the same global identity. It is not a
second company membership. The party index on people is not a universal unique
person-party/company constraint; callers must apply their actual eligibility and
ambiguity checks.
Organization identity is shared through organizations. Its party reference is
unique, as is the country/organization-number pair when a number is present. The
company’s tenants row supplies the application namespace and can link its legal
organization. The current company-identity save synchronizes company presentation
with that legal identity. Shared storage does not mean every caller may read or
edit the global record.
person_tenant_profiles, organization_tenant_profiles and
household_tenant_profiles retain company-specific presentation or customer data.
A household is represented through its party and company profile, not a second
Auth account. A customer relationship is a party_tenant_relationships row, unique
for party/company. New person-party customer relationships are refused by the
person-PTR trigger; a private customer uses a household and a person contact.
A contact relation lives in party_memberships, unique for company, person-party
and customer-party. That key permits the same person to relate to several
customers, while its membership type/title belong to the chosen relation. It is
not an Auth membership, employee role or portal grant. The explicit existing-person
household action checks the selected live company person; it does not identify a
person by a shared inbox address. Some older organization-contact creation paths
still use company-local email matching. Their behaviour is not a general identity
merge rule.
flowchart TD A[Auth account] -->|optional link on company person| P[Company person: people] T[Company: tenants] -->|one required company per person row| P P -->|required person-party| I[Person identity: parties and persons] I -->|company presentation| F[Person tenant profile] I -->|person side| M[Contact membership] C[Customer party] -->|customer side| M T -->|company scope| M
Authentication and company context explain how the API reconstructs identity and selects the active membership. Customers and contacts owns profile, contact and recipient behaviour. A customer’s global party ID, a company person ID and an Auth user ID must not be substituted for one another.
Accepted work retains source and snapshot relationships
Section titled “Accepted work retains source and snapshot relationships”A project belongs to one company. Its quote links are optional and have different
meanings. fk_source_quote records the imported offer that originated the project;
its composite foreign key checks quote/company identity and its companion method
records how the link was found. It does not assert acceptance.
fk_quote is the accepted billing quote. The current project reference trigger
checks company, live source, acceptance fields/status and the relevant customer,
currency and source context. It also has the documented limited work-site
confirmation path; the complete project contract owns
that exception. A bare quote foreign key would establish only that the referenced
row exists. The trigger supplies additional business checks.
project_billing_basis_lines references the project, quote and exact quote line.
Its project/quote-line pair is unique. The source guard checks matching company,
accepted quote, selected optional rows and copied commercial/account provenance.
Accepted source fields and mutable invoiced/reserved balances have different rules.
The commercial authority concept owns acceptance and
historical-evidence interpretation. In particular, the source-reconciliation label
accepted_native follows accepted_at; it is not independent proof of a native
signer’s identity.
A work order requires a project and can have manual instruction lines. An accepted source allocation links a work-order line to a basis row while copying its relevant instructions and quoted hours. The source-allocation trigger uses an advisory transaction lock and rejects reuse on another non-deleted order. This is not a unique index declaring that every basis row has exactly one order: unallocated rows are valid, and the lifecycle of the referencing order matters. A historical linked hourly line can retain an unknown creation-time hour value. Today’s source quantity must not be used to fill that history silently.
Invoice lines can optionally reference accepted basis rows and original quote lines. The project-invoice transaction enforces exact source consumption; the optional foreign key alone does not. Invoicing and settlement owns reservation, release, correction and replay. Allocating the same source to work instructions does not consume its invoice amount.
flowchart TD S[Imported source offer] -. provenance only .-> P[Company project] Q[Accepted quote version and evidence] -->|billing quote| P Q -->|exact accepted line snapshot| B[Project billing basis] P -->|project scope| B B -->|source link and creation-time copy| W[Work-order instruction line] B -->|explicit amount consumption| L[Invoice line]
A projection can deliberately combine these records for another purpose.
v_project_with_customer.value_ex_vat uses the imported expected-invoiced value when
known, otherwise the selected quote subtotal. Its source timestamp and quote choice
help explain the display. The result is a project value projection, not a new
accepted basis row or proof that the remaining amount may be invoiced. Likewise,
a filtered customer payload is an audience projection: it does not create the
underlying acceptance or grant.
Financial identity and observations remain separate
Section titled “Financial identity and observations remain separate”invoices.pk_invoice is Toolora’s document identity. An invoice may have no project,
and an invoice line may have no basis link. Reviewed historical
invoice_project_allocations can attribute one original invoice to several projects
without rewriting its header or creating a new accounting document. Their composite
company/invoice and company/project foreign keys, amount/evidence rules and reviewer
checks protect a different relationship from native basis consumption.
accounting_entity_refs maps company, accounting system, entity type and local UUID
to an external reference. That local tuple is unique. local_entity_pk is a typed
polymorphic lookup, not a foreign key to all supported business tables. A provider
number is scoped by its provider company; the external-reference lookup index is
not a universal uniqueness guarantee. A reader must resolve the expected company,
entity type and provider binding before attaching external evidence.
Invoice/purchase-invoice references carry the connector binding observed when they were written. Historical null bindings do not safely associate themselves with a newly selected provider company. The configured payment-read source can also differ from the write target. The current payment reader selects the read binding and requires a matching bound local reference; matching a displayed invoice number is insufficient. This model does not authorize changing the write company or adding a new provider call.
The payment projection has its own Toolora row ID and a unique
(fk_tenant, connector_tenant_id, entity_type, entity_id) source key. Its composite
foreign key points to feed state for that company/connector. Applying a page and
advancing its opaque cursor commit together. The retained lifecycle distinguishes
known rows, tombstones and unresolved resurrection. A source deletion observation
is not a request to delete the Toolora invoice.
A caught-up feed records progress. A separate expiring coverage_proof establishes
observed register parity at a specific feed revision; a revision change invalidates
that proof. Source observation, local receipt time and proof expiry answer different
questions. Complete settlement also needs the applicable current invoice/credit
observations. These are the settlement workflow’s
checks, not properties of any one payment row. The shared connector’s read projection
is not a completed customer-invoice write cutover.
flowchart TD I[Toolora invoice UUID] -->|typed local identity| R[Company and provider-bound reference] R -. verified lookup .-> D[Provider company and document] D -->|observed source events| E[Connector payment feed] E -->|atomic page and cursor| P[Toolora payment projection and feed state] P -->|separate expiring parity proof| C[Coverage at a named revision]
Frozen customer PDFs, commercial acceptance evidence and provider mutation receipts have their own owning operations and retention/immutability rules. A field named snapshot does not make every column immutable, and a processing receipt is not a customer signature. Publication explains audience releases and snapshots; the accounting integration boundary explains external ownership.
Read the actual enforcement boundary
Section titled “Read the actual enforcement boundary”| Rule or relationship | Representative enforcement | Limit |
|---|---|---|
| One linked company person per Auth account | Partial unique index on company/Auth for non-null Auth links | It includes soft-deleted rows and does not prove activity/capability. |
| One contact relation per company/person/customer | Composite unique key; foreign keys to parties/company; caller/type rules | A valid contact relation still is not a portal grant. |
| Source offer belongs to project company | Composite source-quote/company foreign key and link-method check | Acceptance is a separate billing-quote rule. |
| Accepted basis has valid source/scope | Source/acceptance triggers plus project-invoice transaction checks | A generated type or a single-column FK cannot replace them. |
| Whole source row allocated once to live instructions | Source-allocation trigger and advisory lock, with order deletion predicate | Cancelled and soft-deleted orders have different allocation effects. |
| Payment page belongs to selected source | Bound-company RPC checks, unique source key, feed-state FK and atomic page/checkpoint | Catch-up does not prove register completeness or settlement. |
Row-level security (RLS) constrains the caller’s database path; service-role access needs its own API checks. Neither is a substitute for referential and transactional integrity. The permission reference records current path-specific API/SQL resource predicates rather than treating all paths as one uniform authorization rule.
History, deletion and diagnosis
Section titled “History, deletion and diagnosis”Soft deletion changes a row’s state; it does not remove its foreign-key references.
An ordinary FK does not require deleted_at IS NULL, current publication or active
membership. Those conditions need their actual trigger, query, RPC or route checks.
Partial unique indexes can also have different predicates: live company email and
import-source keys exclude soft-deleted people, while the Auth-link key does not.
Hard-delete actions differ by relationship. Deleting an Auth user sets the nullable
person Auth link to null. The global-person link on people uses RESTRICT.
Other references use cascade, set-null or restrict with their own column constraints.
These examples are not a universal safe-cleanup procedure. Historical evidence,
retention rules and operator authorization must be checked before deletion or
repair. Restoring a row can re-enter uniqueness, source-consumption and publication
gates.
For a synthetic project in company A, a source-only offer for 80,000 can explain its imported value without authorizing an invoice. An accepted version in that same company can create explicit basis rows. A copied ten-hour instruction and a 20,000 partial invoice then represent different allocations of that source. A payment with a matching provider number in company B cannot be joined to company A’s invoice. A company contact with no Auth link can still be a contact; that fact does not create a login or portal grant.
| Symptom | Inspect before changing data |
|---|---|
| Person/contact appears under the wrong identity | Compare Auth ID, company person ID, person-party, selected customer and company profile separately. Do not merge by email. |
| Project value exists but billing basis is absent | Read source-only versus accepted quote links and acceptance evidence; investigate provenance rather than inventing acceptance. |
| Cross-company reference is refused | Inspect the composite FK or scope trigger and exact company IDs. A valid UUID is not permission to move the relationship. |
| View and owning row appear inconsistent | Read the view expression, source timestamp, scope/filter and version. Determine whether it is a current projection or retained evidence. |
| Deleted row remains referenced or restore conflicts | Inspect deletion predicates, FK action and uniqueness/allocation rules; retaining history is not corruption by itself. |
| No payments or stale coverage | Resolve the selected read binding, source key, feed revision and proof expiry. Missing data remains unknown until coverage permits the claim. |
The source details pin representative definitions and callers. The delivery PR separates inspected SQL, executed synthetic tests and current candidate CI. Some schema tests intentionally install historical function bodies for preflight or compatibility scenarios; their result does not certify every current replacement. No independent shared-data repair or new provider operation is implied by this record-model review.