Schema

The reference half of this document is generated from the migrations, so it cannot drift from what the database actually holds. Regenerate it with node scripts/generate-schema-doc.mjs.

Shape

One installation belongs to one customer. That is why there is no tenant_id anywhere: the instance is the tenant, and instance records it in a single row written by the installer.

instance                                 one row: who installed it, where, which edition
instance_admins                          instance administrators
companies ─┬─ company_members            who may read or write, in four presets
           ├─ fiscal_years               periods, open or closed
           ├─ accounts                   chart of accounts, eighteen types
           ├─ journals ── journal_sequences
           ├─ contacts                   customers, suppliers, employees
           ├─ taxes ── tax_postings      ledger account and VAT box, per tax
           ├─ entries ── entry_lines     the ledger; the lines carry the truth
           ├─ documents ── document_lines invoices, credit notes, quotes
           ├─ payments                   money in and out
           ├─ reconciliations            bilateral matching, by amount
           ├─ bank_accounts ── bank_statements ── bank_transactions
           ├─ analytic_axes ── analytic_values ── entry_line_analytics
           └─ attachments                files; an invoice's piece on its document

Reference data sits outside any company: currencies, currency_rates, and the four template tables plus country_defaults that install_country_template() copies into a new company.

Six rules the schema enforces

1. Amounts are positive. entry_lines refuses a negative debit or credit and refuses a line carrying both. A reversal flips the side.
2. A posted entry balances. A check constraint on entries, with total_debit and total_credit maintained from the lines by trigger.
3. Totals are derived. document_lines.amount_untaxed is generated; document and entry totals are maintained by trigger; documents.amount_paid is recomputed from the matching on the third-party lines. Nothing is keyed in.
4. A locked period refuses writes. Triggers on entries and entry_lines consult companies.lock_date, companies.tax_lock_date and fiscal_years.is_closed. Matching stays allowed. A closed year is reopened by reopen_fiscal_year(); a year under a final close (fiscal_years.finally_closed_at) is never reopened or changed, by anybody.
5. A third-party account is reconcilable. A check constraint refuses an asset_receivable or liability_payable account that is not.
6. Every table has row level security. At instance level, a row in instance_admins creates companies and invites members. Per company, company_members gives viewer read, client read and the right to hand a piece over, accountant write, and owner administration of the company and its members. An instance administrator can see the list of companies and invite people into them; they cannot read a ledger they were not invited to.
7. The instance row is a singleton. A primary key of 1 and a check constraint make a second row impossible, not merely unusual.
8. A piece goes on its document. An invoice, a credit note or a receipt is attached to the document (attachments.entity_type = 'document'). An entry that a document posted refuses a file and names the document to use; an entry no document posted keeps its own piece (decision 0069).
9. A tax lands by its code, not on a line. A document line carries its base and names its tax (document_lines.tax_id); the posting writes the tax on the account the tax points to (tax_postings). A line put on such an account is refused (tax_account_on_a_line), with the taxes that land there named; the credit note of a document that already carries one copies it, so the document can still be undone (decision 0083).

Registration is opt-in

instance.contact_email and instance.registered_at are empty on a fresh install. Nothing writes them unless the operator calls register_instance(), nothing in this repository reads them, and unregister_instance() puts them back. Community works unregistered, forever. instance.edition records whether Ekwo operates the installation; it gates nothing here.

Account types

Eighteen values, grouped by the prefix before the first underscore, which is what internal_group derives:

carries_forward is generated too: true for everything except the income and expense types.

How a tax lands

A tax says how much. Its postings say where.

For each tax and each document kind (invoice or credit_note), tax_postings holds at most one base posting and any number of tax postings. Each one carries a factor_percent, a ledger account (for tax postings) and a declaration_box with its own box_factor_percent — and, beside it, declaration_boxes, every box the form prints that one amount in. It is almost always the single box declaration_box names, and it is longer on a form that shows one figure in boxes that are not sums of one another.

post_document() applies them:

• the base amount goes to the account of the document line, and picks up the box of the base posting — one line and one box, whatever the form does with the figure afterwards: vat_return() is what reads the list and sums the line into each box of it;
• for each tax posting, round(tax x |factor| / 100, 2) goes to that posting's account — on the same side as the base when factor_percent is positive, on the opposite side when it is negative;
• the declaration box receives round(tax x box_factor / 100, 2), independently of which side the ledger amount landed on.

A Belgian purchase of goods from another Member State of the European Union at 21 % is therefore four rows:

which books 604 debit 1000 / 411 debit 210 / 451 credit 210 / 440 credit 1000, fills boxes 86, 59 and 55, and leaves the supplier owed 1 000. The ledger and the return say the same thing, because they are the same rows.

Posting a document

post_document(document_id) in order:

1. refuses a document that is already posted, cancelled, empty, or a quote;
2. refuses a tax that is not in force on the accounting date, or a fixed-amount tax;
3. checks the period is open;
4. writes one base line per (account, tax) pair;
5. writes the tax lines, grouping the basis per tax and rounding once;
6. writes the third-party counterpart as the difference of everything above, with date_maturity from the due date or the contact's payment terms;
7. raises if that counterpart disagrees with the document total by more than half a cent — the ledger is right by construction, so the header is what is wrong;
8. numbers and posts the entry, and points the document at it.

Settling a statement line

settle_from_statement(transaction, line_ids, amounts, extra_lines) books a bank statement line as the payment it is and matches it against the open items named, in one entry. amounts says how much settles each item, in the order named; left out, the items are settled oldest first — in full when there are extra lines. extra_lines are what the money is net of or carries besides — the fee a payment provider kept from a payout, the charges of a bank, a small difference — as {account, amount, tax?, description?}, by code, a positive amount a charge (debited), a tax inside the amount and booked from the postings of a purchase invoice. The line balances to the cent: money in is the items less the extra lines, money out the items plus them, and settlement_unbalanced names the difference otherwise. A payout of 97.00 for an invoice of 100.00 is

with the customer line matched against the invoice. Extra lines alone settle a bank charge that pays no item.

Tables

account_templates

Reference charts of accounts, one set per country.

Constraints:

• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK (((account_type <> ALL (ARRAY['asset_receivable'::account_type, 'liability_payable'::account_type])) OR reconcilable))
• PRIMARY KEY (id)

accounts

Chart of accounts, one per company.

Constraints:

• CHECK (((account_type <> ALL (ARRAY['asset_receivable'::account_type, 'liability_payable'::account_type])) OR reconcilable))
• PRIMARY KEY (id)
• UNIQUE (company_id, code)

analysis_aggregates

The aggregates an analysis of the books is computed from, and how each is read. Vocabulary of the core, the same in every country; the accounts that make each one are a pack's, in analysis_rules.

Constraints:

• CHECK ((reading = ANY (ARRAY['movements'::text, 'closing'::text])))
• CHECK ((sign = ANY (ARRAY[1, '-1'::integer])))
• CHECK (((NOT adds_unclosed_result) OR (reading = 'closing'::text)))
• PRIMARY KEY (code)

analysis_rules

How an account of a company reaches an aggregate: the rules of a statement line, with the same columns and the same test, account_rule_catches(). An account reaches an aggregate once, whatever the number of its rules that take it; two aggregates may take the same account — cash is part of the current assets, which are part of the total.

Constraints:

• CHECK ( CASE rule_kind WHEN 'account_type'::text THEN ((account_type IS NOT NULL) AND (code_from IS NULL) AND (code_to IS NULL)) WHEN 'code_range'::text THEN ((code_from IS NOT NULL) AND (code_to IS NOT NULL) AND (account_type IS NULL)) ELSE ((code_from IS NOT NULL) AND (code_to IS NULL) AND (account_type IS NULL)) END)
• CHECK (((country IS NULL) OR (country ~ '^[A-Z]{2}$'::text)))
• CHECK (((country IS NOT NULL) OR (rule_kind = 'account_type'::text)))
• CHECK ((rule_kind = ANY (ARRAY['account_code'::text, 'code_range'::text, 'code_prefix'::text, 'account_type'::text])))
• CHECK ( CASE WHEN (country IS NULL) THEN ((chart_code IS NULL) AND (framework IS NOT NULL)) ELSE ((chart_code IS NOT NULL) AND (framework IS NULL)) END)
• CHECK ((balance_side = ANY (ARRAY['debit'::text, 'credit'::text, 'any'::text])))
• UNIQUE (framework, aggregate, sequence)
• UNIQUE (country, chart_code, aggregate, sequence)

analysis_templates

The aggregates a pack defines for one of its charts, or a framework for any chart, and where that reading comes from. Written by the seed of a pack that carries analysis.json; financial_analysis() reads the country's rows when the company's chart has any, and a framework's otherwise.

Constraints:

• CHECK (((country IS NULL) OR (country ~ '^[A-Z]{2}$'::text)))
• CHECK ( CASE WHEN (country IS NULL) THEN ((chart_code IS NULL) AND (framework IS NOT NULL)) ELSE ((chart_code IS NOT NULL) AND (framework IS NULL)) END)
• UNIQUE (framework, aggregate)
• UNIQUE (country, chart_code, aggregate)

analytic_axes

Analytic dimensions: cost centre, project, activity.

Constraints:

• CHECK ((required_on = ANY (ARRAY['none'::text, 'expense'::text, 'income'::text, 'result'::text])))
• PRIMARY KEY (id)
• UNIQUE (company_id, code)

analytic_values

Values of an axis, optionally hierarchical.

Constraints:

• PRIMARY KEY (id)
• UNIQUE (axis_id, code, company_id)

api_keys

Machine access to one company. Hashed at rest, scoped to an explicit list of capabilities, and never wider than the person who issued it — at issuance and at every use.

Constraints:

• CHECK (((expires_at IS NULL) OR (expires_at > created_at)))
• CHECK ((cardinality(capabilities) > 0))
• CHECK ((posting_mode = ANY (ARRAY['review'::text, 'autopilot'::text])))
• PRIMARY KEY (id)
• UNIQUE (key_hash)

attachments

Files attached to any record. entity_type is constrained rather than free text. An invoice or a receipt goes on its document; an entry takes a file only when no document posted it (trigger attachments_guard_document).

Constraints:

• CHECK ((entity_type = ANY (ARRAY['company'::text, 'contact'::text, 'document'::text, 'entry'::text, 'payment'::text, 'bank_statement'::text, 'bank_transaction'::text, 'fiscal_year'::text, 'tax_filing'::text])))
• CHECK (((byte_size IS NULL) OR (byte_size >= 0)))
• PRIMARY KEY (id)

audit_log

Append-only record of every change to the configuration and reference data of a company, and of the acts that change the state of a document, a payment, a financial year or a pack. Written by trigger, never by a client, and no client rewrites it: no role is granted update, delete or truncate, and triggers refuse them. The owner of the database is bound only while it leaves those triggers enabled.

Constraints:

• CHECK (((length(table_name) > 0) AND (length(record_key) > 0)))
• PRIMARY KEY (id)

bank_accounts

Bank and card accounts, each mapped to a ledger account and a journal.

Constraints:

• PRIMARY KEY (id)

bank_statement_lines

Which lines a statement lists. A line is stored once, in bank_transactions, under the first statement that brought it; a later statement that overlaps the first lists the same line here instead of duplicating it, and its closing balance is proved over the list.

Constraints:

• PRIMARY KEY (statement_id, transaction_id, company_id)

bank_statements

Imported statements. is_consistent compares the declared closing balance with the sum of the lines.

Constraints:

• PRIMARY KEY (id)

bank_transactions

Statement lines. amount is signed; raw keeps whatever the source sent.

Constraints:

• PRIMARY KEY (id)

book_imports

One row per set of books taken over by import_books(): which reader, the checksum of the files, how many entries and lines, and the numbers they were posted under. The same files twice in one company are refused by the unique checksum.

Constraints:

• CHECK ((checksum ~ '^sha256:[0-9a-f]{64}$'::text))
• CHECK (((entry_count >= 0) AND (line_count >= 0)))
• CHECK ((source ~ '^[a-z0-9][a-z0-9.-]{0,39}$'::text))
• PRIMARY KEY (id)

capabilities

Everything a member may be allowed to do, one row per code. Seeded by this migration for the core; a module adds its own with area set to the module code.

Constraints:

• CHECK ((code ~ '^[a-z][a-z0-9_]*\.[a-z][a-z0-9_]*$'::text))
• PRIMARY KEY (code)

chart_templates

Charts of accounts a country offers, from the charts list of packs//pack.json. One of them is the default ekwo init installs when nobody names one.

Constraints:

• CHECK ((country ~ '^[A-Z]{2}$'::text))
• PRIMARY KEY (country, code)

classification_schemes

The classifications of goods and services a country uses on its documents, compiled from its pack: the attribute of a line or a product that holds the code, the name of the classification, the anchored pattern of its codes and the article that prescribes it. A code held under one of these names in document_lines.attributes or products.attributes must match the pattern (decision 0078).

Constraints:

• CHECK ((attribute ~ '^[a-z][a-z0-9_]{0,39}$'::text))
• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK ((pattern ~ '^\^.*\$$'::text))
• PRIMARY KEY (country, attribute)

companies

Legal entities kept in this instance. One instance may hold several.

Constraints:

• CHECK ((agent_posting = ANY (ARRAY['review'::text, 'autopilot'::text, 'review_only'::text])))
• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK ((currency_code ~ '^[A-Z]{3}$'::text))
• CHECK (((peppol_scheme IS NULL) = (peppol_identifier IS NULL)))
• CHECK ((fiscal_country ~ '^[A-Z]{2}$'::text))
• CHECK (((jsonb_typeof(identifiers) = 'object'::text) AND (NOT (identifiers ? ''::text)) AND (NOT jsonb_path_exists(identifiers, '$.*?(@.type() != "string" || @ like_regex "^\\s*$")'::jsonpath))))
• CHECK ((invoice_accent_color ~ '^#[0-9A-Fa-f]{6}$'::text))
• CHECK ((invoice_logo_position = ANY (ARRAY['left'::text, 'right'::text])))
• CHECK ((invoice_typeface = ANY (ARRAY['classic'::text, 'elegant'::text])))
• CHECK ((btrim(number_format) <> ''::text))
• CHECK (((share_capital IS NULL) OR (share_capital_currency IS NOT NULL)))
• CHECK (((share_capital IS NULL) OR (share_capital >= (0)::numeric)))
• PRIMARY KEY (id)

company_archive_registry

What an archive of one company does with each table of the socle that belongs to a company: exported, or excluded with the reason. A table of a company that is not named here stops every export. Modules answer for their own tables through <schema>.archive_tables().

Constraints:

• CHECK ((disposition = ANY (ARRAY['exported'::text, 'excluded'::text])))
• CHECK (((disposition = 'exported'::text) OR (length(COALESCE(reason, ''::text)) >= 20)))
• CHECK (((disposition = 'excluded'::text) OR (load_order IS NOT NULL)))
• CHECK (((via_column IS NULL) = (via_table IS NULL)))
• PRIMARY KEY (table_schema, table_name)

company_filing_periods

How often this company files each declaration it is subject to: one row per form of tax_report_templates, and no row where nothing has been recorded. A company files its periodic return on one cadence and its recapitulative statement on another, and the second is not derivable from the first in any country read so far.

Constraints:

• CHECK ((length(report_code) > 0))
• PRIMARY KEY (company_id, report_code)

company_invitations

Pending and past invitations into a company. The token is handed over once and kept only as a sha256 hash.

Constraints:

• CHECK (((accepted_at IS NULL) = (accepted_by IS NULL)))
• CHECK ((email = lower(email)))
• CHECK ((email ~~ '%_@_%'::text))
• CHECK ((expires_at > created_at))
• PRIMARY KEY (id)
• UNIQUE (token_hash)

company_members

Who may read or write a company. A role is a preset resolved through role_capabilities: owner administers, accountant books, viewer reads, client reads and hands pieces over.

Constraints:

• CHECK ((role <> 'instance_admin'::member_role))
• PRIMARY KEY (company_id, user_id)

company_modules

Modules enabled on a company, and the settings that company keeps for each. Written by enable_module() and disable_module() and by nothing else: there is no write policy.

Constraints:

• PRIMARY KEY (company_id, module_code)

company_packs

Which version of which country pack a company copied. A company may hold two: a foreign VAT registration is one.

Constraints:

• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK ((version ~ '^[0-9]+\.[0-9]+\.[0-9]+$'::text))
• PRIMARY KEY (company_id, country)

company_tax_regimes

The regime of turnover_tax_regimes a company is under, from the first day of a financial year to the last day of one, or open-ended. No row: the company is under the ordinary regime of its taxes (decision 0080).

Constraints:

• CHECK (((valid_to IS NULL) OR (valid_to >= valid_from)))
• PRIMARY KEY (id)
• UNIQUE (company_id, valid_from)

company_vat_schemes

The VAT scheme a company is on, from the first day of a financial year until the next row. No row: the company has not said, and files exactly as it did before schemes existed — no default and no guess. The forms and cadences it implies are written into company_filing_periods, which stays the one place a cadence is recorded.

Constraints:

• PRIMARY KEY (company_id, valid_from)

contact_merges

Every merge of two contacts, by merge_contacts() (decision 0074): the contact absorbed, the one kept, who merged them and when, what moved and what was filled. Written by merge_contacts() and by nothing else — no role may insert — so the guards of a posted line and of an issued document read a row here, written in the same transaction, as the exemption that lets contact_id move from the absorbed contact to the kept one.

Constraints:

• CHECK ((kept_id <> absorbed_id))
• PRIMARY KEY (id)

contact_patterns

How a counterparty shows up on this company's statements: one row per learned or declared motif, of four closed kinds. Read by suggest_contacts(), written by confirm_contact(). The core still chooses no contact for anybody — it answers with candidates and says why.

Constraints:

• CHECK (((confidence >= (0)::numeric) AND (confidence <= (1)::numeric)))
• CHECK ( CASE kind WHEN 'amount_range'::contact_pattern_kind THEN ((value IS NULL) AND (exclude_words IS NULL) AND (amount_min IS NOT NULL) AND (amount_max IS NOT NULL) AND (currency_code IS NOT NULL) AND (amount_min <= amount_max)) WHEN 'description_keyword'::contact_pattern_kind THEN ((value IS NOT NULL) AND (amount_min IS NULL) AND (amount_max IS NULL) AND (amount_typical IS NULL) AND (currency_code IS NULL)) ELSE ((value IS NOT NULL) AND (exclude_words IS NULL) AND (amount_min IS NULL) AND (amount_max IS NULL) AND (amount_typical IS NULL) AND (currency_code IS NULL)) END)
• CHECK ((success_count >= 0))
• CHECK ((success_count <= usage_count))
• CHECK ((usage_count >= 0))
• PRIMARY KEY (id)

contacts

Third parties. contact_type is explicit rather than two hidden counters.

Constraints:

• CHECK (((client_ref IS NULL) OR ((client_ref = btrim(client_ref)) AND ((length(client_ref) >= 1) AND (length(client_ref) <= 200)))))
• CHECK (((country IS NULL) OR (country ~ '^[A-Z]{2}$'::text)))
• CHECK (((jsonb_typeof(identifiers) = 'object'::text) AND (NOT (identifiers ? ''::text)) AND (NOT jsonb_path_exists(identifiers, '$.*?(@.type() != "string" || @ like_regex "^\\s*$")'::jsonpath))))
• CHECK (((merged_into IS NULL) OR ((merged_into <> id) AND (NOT active))))
• CHECK (((parent_id IS NULL) OR (parent_id <> id)))
• CHECK ((payment_terms_days >= 0))
• PRIMARY KEY (id)

country_defaults

Which template account plays which role, per country.

Constraints:

• CHECK ((cash_rounding_unit >= (0)::numeric))
• CHECK (((einvoice_fiscalisation IS NULL) OR ((jsonb_typeof(einvoice_fiscalisation) = 'object'::text) AND (jsonb_typeof((einvoice_fiscalisation -> 'format'::text)) = 'string'::text) AND (COALESCE(jsonb_typeof((einvoice_fiscalisation -> 'hash'::text)), 'null'::text) = ANY (ARRAY['object'::text, 'null'::text])) AND (COALESCE(jsonb_typeof((einvoice_fiscalisation -> 'announced_from'::text)), 'null'::text) = ANY (ARRAY['string'::text, 'null'::text])) AND (COALESCE(jsonb_typeof((einvoice_fiscalisation -> 'kind'::text)), 'null'::text) = ANY (ARRAY['string'::text, 'null'::text])) AND (jsonb_typeof((einvoice_fiscalisation -> 'submission'::text)) = 'object'::text) AND (jsonb_typeof((einvoice_fiscalisation -> 'legal_reference'::text)) = 'string'::text))))
• CHECK ( CASE einvoice_obligation WHEN 'mandatory'::text THEN (einvoice_mandatory_from IS NOT NULL) WHEN 'on_request'::text THEN (einvoice_mandatory_from IS NULL) WHEN 'none'::text THEN (einvoice_mandatory_from IS NULL) ELSE true END)
• CHECK (((einvoice_obligation IS NULL) OR (einvoice_obligation = ANY (ARRAY['mandatory'::text, 'on_request'::text, 'none'::text]))))
• CHECK (((final_close_due IS NULL) OR (final_close_due = 'before_next_fiscal_year_ends'::text)))
• CHECK (((fiscal_year_default IS NULL) OR (fiscal_year_default = ANY (ARRAY['calendar'::text, 'april'::text, 'july'::text, 'october'::text]))))
• CHECK ((jsonb_typeof(invoice_labels) = 'object'::text))
• CHECK (((legal_payment_days IS NULL) OR (legal_payment_days >= 0)))
• CHECK (((party_scheme IS NULL) OR (party_scheme ~ '^[0-9]{4}$'::text)))
• CHECK (((posted_edit_policy IS NULL) OR (posted_edit_policy = ANY (ARRAY['reversal_only'::text, 'unpost_if_untouched'::text]))))
• CHECK (((tax_point_rule IS NULL) OR (tax_point_rule = ANY (ARRAY['invoice_date'::text, 'delivery_date'::text, 'payment_date'::text, 'invoice_if_issued'::text, 'earliest_of_delivery_or_payment'::text]))))
• CHECK (((vat_scheme IS NULL) OR (vat_scheme ~ '^[0-9]{4}$'::text)))
• PRIMARY KEY (country)

country_packs

Country packs loaded in this installation, with their version and certification.

Constraints:

• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK ((version ~ '^[0-9]+\.[0-9]+\.[0-9]+$'::text))
• PRIMARY KEY (country)

currencies

ISO 4217 currencies known to this instance.

Constraints:

• CHECK ((code ~ '^[A-Z]{3}$'::text))
• CHECK (((decimal_places >= 0) AND (decimal_places <= 6)))
• PRIMARY KEY (code)

currency_rates

Dated exchange rates. A document stores the rate it used; this table is the history.

Constraints:

• CHECK ((rate > (0)::numeric))
• PRIMARY KEY (id)
• UNIQUE (currency_code, rate_date)

declaration_instalments

What a company paid on account of a declaration during its period: which instalment of the scheme's schedule, the day, the amount and the entry that carried it, where there is one. Several rows for one instalment are a payment made in parts. The annual return deducts them in the box whose reads is instalments_paid or instalments_due.

Constraints:

• CHECK ((amount > (0)::numeric))
• CHECK ((period_end >= period_start))
• CHECK ((sequence >= 1))
• PRIMARY KEY (id)

document_line_analytics

Analytic split of a document line, one row per value, share in percent. Copied onto the ledger line by post_document(); after posting the ledger split is the one read.

Constraints:

• CHECK (((percentage > (0)::numeric) AND (percentage <= (100)::numeric)))
• PRIMARY KEY (id)
• UNIQUE (document_line_id, analytic_value_id, company_id)

document_lines

Lines of a document. A product line carries its base, its account and its tax (tax_id); the tax itself is written by the posting on the account the tax points to, and a line on such an account is refused (trigger document_lines_guard_tax_account).

Constraints:

• CHECK (((jsonb_typeof(attributes) = 'object'::text) AND (NOT (attributes ? ''::text)) AND (NOT jsonb_path_exists(attributes, '$.*?(@.type() != "string" || @ like_regex "^\\s*$")'::jsonpath))))
• CHECK (((NOT base_includes_tax) OR (tax_id IS NOT NULL)))
• CHECK (((discount_percent >= (0)::numeric) AND (discount_percent < (100)::numeric)))
• CHECK (((NOT unit_price_includes_tax) OR (tax_id IS NOT NULL)))
• CHECK (((line_type <> 'product'::document_line_type) OR (account_id IS NOT NULL)))
• CHECK (((vat_category IS NULL) OR (vat_category ~ '^[A-Z]{1,2}$'::text)))
• PRIMARY KEY (id)

document_shares

Public links onto a document. The token is handed over once and kept only as a sha256; the link is withdrawn by revoking it, never by editing it.

Constraints:

• CHECK (((expires_at IS NULL) OR (expires_at > created_at)))
• CHECK (((subject_kind = 'document'::share_subject_kind) = (document_id IS NOT NULL)))
• CHECK ((token_hash ~ '^[0-9a-f]{64}$'::text))
• CHECK ((view_count >= 0))
• PRIMARY KEY (id)
• UNIQUE (token_hash)

document_unpostings

Every posted document put back to draft by unpost_document(): the entry that was taken away, its number, journal and day, and whether the number went back to the counter. Written by unpost_document() and by nothing else — no role may insert — so the guards read a row here, written in the same transaction, as the one exception to "a posted document does not go back to draft" and "a posted entry is not deleted".

Constraints:

• PRIMARY KEY (id)

documents

Sales and purchase invoices, credit notes, quotes and orders. state is the document, payment_state the settlement.

Constraints:

• CHECK (((client_ref IS NULL) OR ((client_ref = btrim(client_ref)) AND ((length(client_ref) >= 1) AND (length(client_ref) <= 200)))))
• CHECK (((state = 'draft'::doc_state) OR (contact_id IS NOT NULL)))
• CHECK (((state <> 'posted'::doc_state) OR (number IS NOT NULL)))
• CHECK ((exchange_rate > (0)::numeric))
• PRIMARY KEY (id)

entries

Journal entries. A document and its entry are two layers joined by a foreign key.

Constraints:

• CHECK (((state <> 'posted'::entry_state) OR (number IS NOT NULL)))
• CHECK (((state <> 'posted'::entry_state) OR (total_debit = total_credit)))
• PRIMARY KEY (id)

entry_line_analytics

Analytic split of a ledger line. One row per value, share in percent.

Constraints:

• CHECK (((percentage > (0)::numeric) AND (percentage <= (100)::numeric)))
• PRIMARY KEY (id)
• UNIQUE (entry_line_id, analytic_value_id, company_id)

entry_lines

Ledger lines. Amounts are always positive; a reversal flips the side, it never negates.

Constraints:

• CHECK (((debit >= (0)::numeric) AND (credit >= (0)::numeric)))
• CHECK (((matched_amount >= (0)::numeric) AND (matched_amount <= abs((debit - credit)))))
• CHECK (((debit = (0)::numeric) OR (credit = (0)::numeric)))
• CHECK (((posting_type IS NULL) OR (tax_id IS NOT NULL)))
• PRIMARY KEY (id)

filing_proofs

Proofs that a file existed at a date: the sha256 of its exact bytes, committed to a public ledger, with the proof that it was. Written through record_filing_proof() and upgrade_filing_proof(); a complete proof is frozen. filing_proof(sha256) is the public way to read one.

Constraints:

• CHECK (((anchor_height IS NULL) OR (anchor_height >= 0)))
• CHECK ((((status = 'complete'::filing_proof_status) = (completed_at IS NOT NULL)) AND ((status = 'pending'::filing_proof_status) OR ((anchor_chain IS NOT NULL) AND ((anchor_height IS NOT NULL) OR (anchor_reference IS NOT NULL))))))
• CHECK ((length(proof) > 0))
• CHECK ((sha256 ~ '^[0-9a-f]{64}$'::text))
• CHECK (((num_nonnulls(tax_filing_id, fiscal_year_id, document_id) = 1) AND ((subject_kind = 'tax_filing'::filing_proof_subject) = (tax_filing_id IS NOT NULL)) AND ((subject_kind = 'fiscal_year'::filing_proof_subject) = (fiscal_year_id IS NOT NULL)) AND ((subject_kind = 'document'::filing_proof_subject) = (document_id IS NOT NULL))))
• CHECK (((values_sha256 IS NULL) OR (values_sha256 ~ '^[0-9a-f]{64}$'::text)))
• PRIMARY KEY (id)
• UNIQUE (company_id, sha256, method)

fiscal_years

Accounting periods. An exercise is an object, not two integers on the company.

Constraints:

• CHECK ((end_date > start_date))
• CHECK (((finally_closed_at IS NULL) OR is_closed))
• PRIMARY KEY (id)
• UNIQUE (company_id, start_date)

instance

The installation itself. Exactly one row. Registration with Ekwo is optional and empty by default.

Constraints:

• CHECK (((companies_per_person IS NULL) OR ((companies_per_person >= 0) AND (companies_per_person <= 1000))))
• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK (((public_base_url IS NULL) OR (public_base_url ~ '^https?://[^[:space:]]+$'::text)))
• CHECK (((registered_at IS NULL) OR (contact_email IS NOT NULL)))
• CHECK (((NOT shared) OR (companies_per_person IS NOT NULL)))
• CHECK ((id = 1))
• PRIMARY KEY (id)
• UNIQUE (instance_id)

instance_admins

Instance administrators: they create companies and invite members. One row per user, keyed on auth.users of the customer's own Supabase project.

Constraints:

• PRIMARY KEY (user_id)

journal_sequences

Counter behind next_entry_number(). One row per journal and year.

Constraints:

• CHECK ((last_number >= 0))
• PRIMARY KEY (journal_id, year)

journal_templates

Constraints:

• PRIMARY KEY (id)
• UNIQUE (country, code)

journals

Books of entry. The code is the first segment of every entry number.

Constraints:

• CHECK ((code ~ '^[A-Z0-9]{2,8}$'::text))
• CHECK (((number_prefix = btrim(number_prefix)) AND (number_prefix <> ''::text) AND (length(number_prefix) <= 20) AND (number_prefix !~ '[{}]'::text)))
• PRIMARY KEY (id)
• UNIQUE (company_id, code)

legal_mention_templates

The sentences a country requires on an invoice, and the closed condition that says when each applies. Reference data filled by a pack, never copied into a company.

Constraints:

• CHECK ((applies_when = ANY (ARRAY['always'::text, 'reverse_charge'::text, 'intra_eu_goods'::text, 'intra_eu_services'::text, 'export'::text, 'exempt'::text, 'small_business'::text, 'late_payment'::text, 'cash_basis'::text])))
• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK (((valid_to IS NULL) OR (valid_to >= valid_from)))
• PRIMARY KEY (id)
• UNIQUE (country, code)

lines_left_unnamed

Open lines on a receivable or payable account that a person decided to leave without a contact, with the reason: suggest_line_contacts() no longer proposes them. Written by leave_line_unnamed(), removed by undo_leave_line_unnamed(). The line itself does not move and stays an open item (decision 0072).

Constraints:

• CHECK (((length(btrim(reason)) >= 1) AND (length(btrim(reason)) <= 500)))
• PRIMARY KEY (line_id, company_id)

matching_sequences

Constraints:

• PRIMARY KEY (company_id)

matching_settings

What this company considers close enough. Every column is nullable and null means the shipped answer, which lives in matching_policy_of() and nowhere else — so a reader always gets a number and a company that never had an opinion has no row.

Constraints:

• CHECK ((amount_tolerance_units >= 0))
• CHECK ((date_window_days >= 0))
• CHECK ((difference_tolerance_units >= 0))
• CHECK ((minimum_word_length >= 1))
• CHECK (((name_threshold >= (0)::numeric) AND (name_threshold <= (1)::numeric)))
• CHECK ((sum_tolerance_units >= 0))
• PRIMARY KEY (company_id)

modules

One row per module this installation carries, written by the module's own first migration. The registry is a table, not code.

Constraints:

• CHECK ((code ~ '^[a-z][a-z0-9_]{1,30}$'::text))
• CHECK ((schema_name ~ '^[a-z][a-z0-9_]{1,30}$'::text))
• CHECK ((schema_name <> 'public'::text))
• PRIMARY KEY (code)
• UNIQUE (schema_name)

payments

Money in and out. Amounts are positive; direction carries the sign.

Constraints:

• CHECK ((amount > (0)::numeric))
• CHECK (((client_ref IS NULL) OR ((client_ref = btrim(client_ref)) AND ((length(client_ref) >= 1) AND (length(client_ref) <= 200)))))
• CHECK ((exchange_rate > (0)::numeric))
• PRIMARY KEY (id)

products

What a document line is filled in from: code, name, unit, price, account and tax. Not stock: no quantity on hand and no valuation.

Constraints:

• CHECK (((jsonb_typeof(attributes) = 'object'::text) AND (NOT (attributes ? ''::text)) AND (NOT jsonb_path_exists(attributes, '$.*?(@.type() != "string" || @ like_regex "^\\s*$")'::jsonpath))))
• CHECK ((length(btrim(code)) > 0))
• CHECK (((purchase_price IS NULL) OR (purchase_price >= (0)::numeric)))
• CHECK (((sale_price IS NULL) OR (sale_price >= (0)::numeric)))
• CHECK ((unit_code ~ '^[A-Z0-9]{1,3}$'::text))
• PRIMARY KEY (id)
• UNIQUE (company_id, code)

reconciliation_batches

Matchings made in one gesture: reconcile_batch() writes the row and names it on every matching it wrote (reconciliations.batch_id); unreconcile_batch() undoes those still standing and says when and by whom. Written by those two functions alone (decision 0073).

Constraints:

• CHECK ((amount > (0)::numeric))
• CHECK ((pair_count > 0))
• CHECK (((undone_by IS NULL) OR (undone_at IS NOT NULL)))
• PRIMARY KEY (id)

reconciliations

One row per pairing of a debit with a credit. Full matching is the sum of partials.

Constraints:

• CHECK ((amount > (0)::numeric))
• CHECK ((debit_line_id <> credit_line_id))
• PRIMARY KEY (id)
• UNIQUE (debit_line_id, credit_line_id, company_id)

role_capabilities

What each preset holds. A role is never tested by a policy; it is resolved here into capabilities.

Constraints:

• PRIMARY KEY (role, capability)

statement_line_rules

How an account of a company reaches a line. Presentation maps by range of the legal chart; choosing an account to post to by prefix stays forbidden, and is a different question.

Constraints:

• CHECK ( CASE rule_kind WHEN 'account_type'::text THEN ((account_type IS NOT NULL) AND (code_from IS NULL) AND (code_to IS NULL)) WHEN 'code_range'::text THEN ((code_from IS NOT NULL) AND (code_to IS NOT NULL) AND (account_type IS NULL)) ELSE ((code_from IS NOT NULL) AND (code_to IS NULL) AND (account_type IS NULL)) END)
• CHECK ((rule_kind = ANY (ARRAY['account_code'::text, 'code_range'::text, 'code_prefix'::text, 'account_type'::text])))
• CHECK ((balance_side = ANY (ARRAY['debit'::text, 'credit'::text, 'any'::text])))
• PRIMARY KEY (statement_code, line_code, sequence)

statement_line_templates

The lines of a statement, in the order it prints them, and the plus/minus lists a total is computed from.

Constraints:

• CHECK ((is_total OR ((plus_lines = '{}'::text[]) AND (minus_lines = '{}'::text[]))))
• CHECK ((sign = ANY (ARRAY[1, '-1'::integer])))
• PRIMARY KEY (statement_code, code)

statement_templates

Financial statements per framework, from packs//statements.json and packs/generic/. Reference data: never copied into a company.

Constraints:

• CHECK (((chart_code IS NULL) OR (country IS NOT NULL)))
• CHECK (((country IS NULL) OR (country ~ '^[A-Z]{2}$'::text)))
• CHECK ((kind = ANY (ARRAY['balance_sheet'::text, 'income_statement'::text, 'cash_flow'::text, 'allocation'::text])))
• CHECK ((result_in_progress = ANY (ARRAY['combined'::text, 'alone'::text])))
• CHECK (((result_in_progress IS NULL) OR (kind = 'balance_sheet'::text)))
• CHECK ( CASE WHEN (result_in_progress = 'combined'::text) THEN (result_in_progress_name IS NOT NULL) ELSE (result_in_progress_name IS NULL) END)
• CHECK (((valid_to IS NULL) OR (valid_to >= valid_from)))
• PRIMARY KEY (code)

tax_filing_boxes

The figures as they were filed, one row per box. Frozen at filing and never recomputed: this is what the administration holds.

Constraints:

• CHECK ((kind = ANY (ARRAY['base'::text, 'tax'::text, 'total'::text])))
• PRIMARY KEY (filing_id, box, kind)

tax_filing_deposits

One send of a declaration and what came back. Written only by file_filing(), record_filing_outcome() and record_filing_files(); read under filings.read.

Constraints:

• CHECK (((outcome IS NULL) OR (outcome = ANY (ARRAY['accepted'::tax_filing_state, 'rejected'::tax_filing_state, 'paid'::tax_filing_state]))))
• CHECK (((outcome IS NULL) = (outcome_at IS NULL)))
• PRIMARY KEY (id)
• UNIQUE (filing_id, sequence)

tax_filings

One row per declaration of a period: what was filed, when, under which reference, and in which state. The figures are in tax_filing_boxes and they are frozen — this table exists so that "what did we declare" is a question with an answer.

Constraints:

• CHECK ((((state = ANY (ARRAY['draft'::tax_filing_state, 'ready'::tax_filing_state])) = (filed_at IS NULL)) OR (state = 'superseded'::tax_filing_state)))
• CHECK ((period_start <= period_end))
• PRIMARY KEY (id)

tax_posting_templates

Constraints:

• CHECK ( CASE posting_type WHEN 'tax'::tax_posting_type THEN (account_code IS NOT NULL) ELSE (account_code IS NULL) END)
• CHECK ( CASE WHEN (declaration_box IS NULL) THEN (declaration_boxes IS NULL) ELSE ((declaration_boxes IS NOT NULL) AND (cardinality(declaration_boxes) >= 1) AND (declaration_boxes[1] = declaration_box)) END)
• PRIMARY KEY (id)

tax_postings

Where a tax lands: ledger account and VAT-return box, per tax and per document kind.

Constraints:

• CHECK ( CASE posting_type WHEN 'tax'::tax_posting_type THEN (account_id IS NOT NULL) ELSE (account_id IS NULL) END)
• CHECK ( CASE WHEN (declaration_box IS NULL) THEN (declaration_boxes IS NULL) ELSE ((declaration_boxes IS NOT NULL) AND (cardinality(declaration_boxes) >= 1) AND (declaration_boxes[1] = declaration_box)) END)
• PRIMARY KEY (id)

tax_report_box_templates

The boxes of a declaration form, and the plus/minus lists a total is computed from. Read by vat_return().

Constraints:

• CHECK (((kind = 'total'::text) OR ((plus_boxes = '{}'::text[]) AND (minus_boxes = '{}'::text[]))))
• CHECK (((cardinality(from_boxes) = 0) OR (reads IS NULL)))
• CHECK ((kind = ANY (ARRAY['base'::text, 'tax'::text, 'total'::text])))
• CHECK (((kind <> 'total'::text) OR ((cardinality(from_boxes) = 0) AND (reads IS NULL))))
• CHECK (((rate IS NULL) OR (kind = 'total'::text)))
• CHECK (((rate_of_box IS NULL) OR (split_part(rate_of_box, ':'::text, 1) <> box)))
• CHECK (((rate IS NULL) = (rate_of_box IS NULL)))
• CHECK (((rate IS NULL) OR ((plus_boxes = '{}'::text[]) AND (minus_boxes = '{}'::text[]))))
• CHECK ((reads = ANY (ARRAY['instalments_paid'::text, 'instalments_due'::text])))
• CHECK (((valid_to IS NULL) OR (valid_to >= valid_from)))
• PRIMARY KEY (country, report_code, box, kind)

tax_report_templates

Declaration forms per country, from packs//tax_report.json. Reference data: a form is not customisable, so it is never copied into a company.

Constraints:

• CHECK ((country ~ '^[A-Z]{2}$'::text))
• CHECK ( CASE deadline_rule WHEN 'day_of_month_after_period'::filing_deadline_rule THEN ((deadline_day >= 1) AND (deadline_day <= 31)) WHEN 'last_day_of_month_after_period'::filing_deadline_rule THEN (deadline_day IS NULL) WHEN 'depends_on_taxpayer'::filing_deadline_rule THEN ((deadline_day IS NULL) AND (deadline_plus_days IS NULL) AND (deadline_reference IS NOT NULL)) ELSE ((deadline_day IS NULL) AND (deadline_plus_days IS NULL) AND (deadline_reference IS NULL) AND (deadline_source_key IS NULL)) END)
• CHECK (((period_default IS NULL) OR (period_default = ANY (periods))))
• CHECK ((cardinality(periods) >= 1))
• CHECK (((reads_postings_of IS NULL) OR (reads_postings_of <> code)))
• CHECK ((role = ANY (ARRAY['periodic'::text, 'annual'::text])))
• CHECK (((role IS NULL) OR ((role = 'periodic'::text) = is_periodic_return)))
• CHECK ((((rounding_unit IS NULL) = (rounding_reference IS NULL)) AND ((rounding_source_key IS NULL) OR (rounding_reference IS NOT NULL))))
• CHECK (((rounding_unit IS NULL) OR ((rounding_unit > (0)::numeric) AND (rounding_unit = power((10)::numeric, round(log(rounding_unit)))))))
• CHECK (((valid_to IS NULL) OR (valid_to >= valid_from)))
• PRIMARY KEY (country, code)

tax_templates

Reference taxes per country, with their period of validity.

Constraints:

• CHECK (((NOT base_includes_tax) OR ((amount_type = 'percent'::tax_amount_type) AND (amount < (100)::numeric))))
• CHECK (((NOT price_include) OR (amount_type = 'percent'::tax_amount_type)))
• CHECK (((vat_category IS NULL) OR (vat_category ~ '^[A-Z]{1,2}$'::text)))
• PRIMARY KEY (id)
• UNIQUE (country, code)

taxes