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, polymorphic

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.
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.

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 intra-community purchase of goods 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.

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)

analytic_axes

Analytic dimensions: cost centre, project, activity.

Constraints:

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

analytic_values

Values of an axis, optionally hierarchical.

Constraints:

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

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.

Constraints:

• CHECK (((expires_at IS NULL) OR (expires_at > created_at)))
• CHECK ((cardinality(capabilities) > 0))
• PRIMARY KEY (id)
• UNIQUE (key_hash)

attachments

Files attached to any record. entity_type is constrained rather than free text.

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; no update and no delete, for anyone.

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)

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)

companies

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

Constraints:

• 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 (((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)

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 (((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 ( 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 (((fiscal_year_default IS NULL) OR (fiscal_year_default = ANY (ARRAY['calendar'::text, 'april'::text, 'july'::text, 'october'::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)

document_lines

Document lines in a table, not JSON: EN 16931 needs a VAT category per line and the FEC needs the detail.

Constraints:

• 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 <> '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)

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)

fiscal_years

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

Constraints:

• CHECK ((end_date > start_date))
• 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 ((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 ((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))
• 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)

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 ((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 ((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)

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)

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 (((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 row per send of a declaration, with what came back: the deposit number, the outcome, the administration's own words, and the two files — what was sent and the receipt. A rejected declaration is sent again, so a declaration has as many deposits as it took.

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 ((kind = ANY (ARRAY['base'::text, 'tax'::text, 'total'::text])))
• 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 (((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 ((((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 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

VAT and similar taxes, with temporal validity and a legal reference.

Constraints:

• CHECK (((country IS NULL) OR (country ~ '^[A-Z]{2}$'::text)))
• CHECK (((NOT price_include) OR (amount_type = 'percent'::tax_amount_type)))
• CHECK (((valid_to IS NULL) OR (valid_to >= valid_from)))
• CHECK (((vat_category IS NULL) OR (vat_category ~ '^[A-Z]{1,2}$'::text)))
• PRIMARY KEY (id)
• UNIQUE (company_id, code)

territories

The territories of the common system of value added tax: the Member States with the day each became bound, the United Kingdom with the day it stopped being, Northern Ireland, and the territories articles 6 and 7 of Directive 2006/112/EC take out of the system or put into it. Framework data filled by the release, like currencies — no company owns a row and no country pack writes one. A territory this table does not carry is outside the common system, which is the answer for every third country.

Constraints:

• CHECK ((code ~ '^[A-Z]{2}(-[A-Z0-9]{1,12})?$'::text))
• CHECK (((parent_code IS NULL) OR (parent_code <> code)))
• CHECK (((NOT outside_parent_tax) OR (parent_code IS NOT NULL)))
• CHECK (((vat_prefix IS NULL) OR (vat_prefix ~ '^[A-Z]{2}$'::text)))
• CHECK (((eu_vat_scope = 'none'::eu_vat_scope) = (eu_vat_from IS NULL)))
• CHECK (((eu_vat_to IS NULL) OR ((eu_vat_from IS NOT NULL) AND (eu_vat_from <= eu_vat_to))))
• PRIMARY KEY (code)

user_preferences

What one person prefers, across every company they are a member of. Every column is nullable and none has a default: null means "take the company's answer, then the pack's".

Constraints:

• CHECK (((language IS NULL) OR (language ~ '^[a-z]{2}(-[A-Za-z0-9]{2,8})?$'::text)))
• PRIMARY KEY (user_id)

Functions

Modules

One Postgres schema each, beside the socle. A module depends on public by foreign key and reaches the ledger only through post_module_entry(). It is enabled per company, and PostgREST serves its schema only once the project exposes it.

assets — Fixed assets

Fixed assets, their depreciation schedule and their disposal. Durations, declining coefficients and the prorata convention are country pack data.

Tables

assets

One fixed asset: what it cost, how it is depreciated, and the three accounts that carry it. The schedule is assets.depreciation_lines.

Constraints:

• CHECK ((cost > (0)::numeric))
• CHECK (((method <> 'declining_balance'::assets.depreciation_method) OR (coefficient IS NOT NULL)))
• CHECK ((duration_months > 0))
• CHECK (((in_service_date IS NULL) OR (in_service_date >= acquisition_date)))
• CHECK (((residual_value >= (0)::numeric) AND (residual_value < cost)))
• PRIMARY KEY (id)
• UNIQUE (company_id, code)