dodil/erp-brick-gl
DODIL ERP brick: general ledger
src/brick_gl/infrastructure/tables.py
"""The tables of the books. Every table is prefixed `gl_`, has a natural primary key (never SERIAL) and money
NUMERIC(18,2), an exchange rate NUMERIC(19,6), a statistical quantity NUMERIC(19,4). schema.sql and the first migration are
WRITTEN from this column model by scripts/gen_schema.py; tests/test_schema.py keeps them equal.
A column is declared "name:type" with a trailing * for a primary-key part:
s text i integer b bigint d date t timestamp m money r rate q quantity B boolean x long text
Rows that are facts (a journal and its lines, a split line, an fx rate, a period event, an audit row) are only ever
INSERTED: a conflict on the primary key IS the replay or the race lost, never an overwrite. Rows that are claims
(gl_number_series, gl_chain_claim) are written with a plain INSERT so that two writers cannot both hold one key.
"""
from sqlalchemy import TIMESTAMP, BigInteger, Boolean, Column, Date, Integer, MetaData, Numeric, String, Table, Text
md = MetaData()
# What a release after the first ADDED (scripts/gen_schema.py writes 00001_init without these and a migration with them):
# {release: {table: [columns]}}. Additive only.
ADDED = {2: {"gl_migration_run": ["reconciled_at"]}}
MONEY, RATE, QTY = Numeric(18, 2), Numeric(19, 6), Numeric(19, 4)
_TYPES = {"s": String, "i": Integer, "b": BigInteger, "d": Date, "t": TIMESTAMP, "m": MONEY, "r": RATE, "q": QTY,
"B": Boolean, "x": Text}
def _col(spec: str) -> Column:
name, _, kind = spec.partition(":")
pk = kind.endswith("*")
kind = kind.rstrip("*") or "s"
return Column(name, _TYPES[kind], primary_key=pk, autoincrement=False if pk else None)
def T(name: str, *cols: str) -> Table:
return Table(name, md, *[_col(c) for c in cols], Column("created_at", TIMESTAMP), Column("updated_at", TIMESTAMP))
# ── masters ────────────────────────────────────────────────────────────────────────────────────────────────────────
gl_company = T("gl_company", "company_code:s*", "name", "country", "functional_currency", "chart_id", "financial_year_end_month:i",
"bp_id:b", "default_profit_centre", "splitting_from_fiscal_year", "is_default:B", "active_until:d")
gl_chart = T("gl_chart", "chart_id:s*", "name", "kind", "country")
gl_account = T("gl_account", "account_id:b*", "chart_id", "code", "name", "type", "subtype", "normal_balance", "currency",
"parent_account_id:b", "monetary:B", "source_only:B", "fs_cost_centre", "fs_profit_centre", "fs_bp_id",
"fs_tax_code", "default_profit_centre", "movement_tracked:B", "active_until:d")
gl_account_edge = T("gl_account_edge", "parent_id:b*", "child_id:b*")
gl_group_account_map = T("gl_group_account_map", "account_id:b*", "valid_from:d*", "group_account_id:b", "set_by", "set_at:t")
gl_alt_account = T("gl_alt_account", "company_code:s*", "account_id:b*", "valid_from:d*", "alt_code", "alt_name")
gl_ledger = T("gl_ledger", "ledger_id:s*", "name", "kind", "accounting_principle", "reval_method")
gl_company_ledger = T("gl_company_ledger", "company_code:s*", "ledger_id:s*", "from_fiscal_year", "until_fiscal_year")
gl_account_map = T("gl_account_map", "chart_id:s*", "purpose:s*", "qualifier:s*", "valid_from:d*", "account_id:b", "set_by", "set_at:t")
gl_segment = T("gl_segment", "segment:s*", "name", "active_until:d")
gl_profit_centre = T("gl_profit_centre", "profit_centre:s*", "name", "parent_profit_centre", "company_codes", "valid_from:d",
"valid_until:d")
gl_profit_centre_segment = T("gl_profit_centre_segment", "profit_centre:s*", "valid_from:d*", "segment")
gl_cost_centre = T("gl_cost_centre", "cost_centre:s*", "name", "company_code", "active_until:d")
gl_cost_centre_assignment = T("gl_cost_centre_assignment", "cost_centre:s*", "valid_from:d*", "profit_centre")
gl_fx_rate = T("gl_fx_rate", "from_ccy:s*", "to_ccy:s*", "rate_type:s*", "rate_date:d*", "rate:r", "source", "entered_by",
"entered_sub", "entered_at:t")
# ── the books ──────────────────────────────────────────────────────────────────────────────────────────────────────
gl_journal = T("gl_journal", "journal_id:b*", "company_code", "ledger_id", "status", "journal_date:d", "period_id", "fiscal_year",
"book", "entry_no:i", "source_module", "source_document", "event", "currency", "fx_rate:r", "memo", "total:m",
"functional_total:m", "adjustment:B", "reverse_next_period:B", "reversal_status", "reversal_reason",
"ic_document_id:b", "recurring_id", "accrual_id", "allocation_run_id", "reval_run_id", "proposed_by",
"proposed_sub", "approved_by", "approved_sub", "rejected_by", "reject_reason", "posted_at:t",
"reverses_journal_id:b", "body_digest", "chain_seq:i", "prev_hash", "row_hash", "migration_run_id")
gl_journal_line = T("gl_journal_line", "journal_id:b*", "line_no:i*", "account_id:b", "purpose", "qualifier", "debit:m", "credit:m",
"functional_debit:m", "functional_credit:m", "currency", "amount_currency:m", "bp_id:b", "tax_code",
"cost_centre", "profit_centre", "partner_company", "movement_type", "against_document", "ic_reference",
"asset_id", "line_memo", "odoo_line_id")
gl_split_line = T("gl_split_line", "journal_id:b*", "line_no:i*", "split_no:i*", "company_code", "ledger_id", "account_id:b",
"period_id", "journal_date:d", "debit:m", "credit:m", "functional_debit:m", "functional_credit:m", "currency",
"profit_centre", "segment", "cost_centre", "partner_company", "movement_type", "bp_id:b", "asset_id",
"is_clearing:B", "adjustment:B")
gl_balance = T("gl_balance", "company_code:s*", "ledger_id:s*", "account_id:b*", "period_id:s*", "profit_centre:s*", "segment:s*",
"partner_company:s*", "debit:m", "credit:m", "closing:m", "closing_currency:m", "adjustment_debit:m",
"adjustment_credit:m", "derived_at:t")
gl_carry_forward = T("gl_carry_forward", "company_code:s*", "ledger_id:s*", "fiscal_year:s*", "account_id:b*", "profit_centre:s*",
"segment:s*", "partner_company:s*", "opening:m", "opening_currency:m", "derived_at:t", "source_digest")
gl_fiscal_year = T("gl_fiscal_year", "company_code:s*", "fiscal_year:s*", "starts_on:d", "ends_on:d", "status", "closed_by", "closed_at:t")
gl_period = T("gl_period", "company_code:s*", "period_id:s*", "fiscal_year", "starts_on:d", "ends_on:d", "status", "closed_by",
"closed_sub", "closed_at:t", "last_posted_at:t", "statements_stale:B")
gl_period_event = T("gl_period_event", "event_id:s*", "company_code", "period_id", "event", "reason", "by_email", "by_sub", "event_at:t")
gl_number_series = T("gl_number_series", "company_code:s*", "book:s*", "fiscal_year:s*", "entry_no:i*", "journal_id:b", "claimed_at:t")
gl_chain_claim = T("gl_chain_claim", "company_code:s*", "chain_seq:i*", "journal_id:b", "claimed_at:t")
# ── runs: revaluation, reconciliation, intercompany ────────────────────────────────────────────────────────────────
gl_fx_reval_run = T("gl_fx_reval_run", "run_id:s*", "company_code", "ledger_id", "key_date:d", "kind", "journal_id:b",
"reversal_journal_id:b", "method", "run_by", "run_at:t")
gl_fx_reval_item = T("gl_fx_reval_item", "run_id:s*", "account_id:b*", "currency:s*", "document:s*", "amount_currency:m",
"booked_functional:m", "rate:r", "revalued_functional:m", "gain_loss:m", "profit_centre")
gl_reconciliation = T("gl_reconciliation", "recon_id:s*", "company_code", "control_account_id:b", "as_at:d", "kind", "gl_balance:m",
"outstanding_sum:m", "variance:m", "breaks_json:x", "accepted_by", "accepted_reason")
gl_ic_document = T("gl_ic_document", "ic_document_id:b*", "companies", "journal_ids", "proposed_by", "proposed_sub", "approved_by",
"approved_sub", "status", "header_json:x", "lines_json:x", "reason")
gl_ic_match = T("gl_ic_match", "match_id:s*", "company_code", "partner_company", "as_at:d", "matched_json:x", "breaks_json:x",
"within_tolerance:B", "accepted_by", "accepted_sub", "accepted_reason", "run_at:t")
# ── scheduled work: recurring, accruals, allocations ───────────────────────────────────────────────────────────────
gl_recurring_journal = T("gl_recurring_journal", "recurring_id:s*", "version:i*", "company_code", "ledger_id", "lines_json:x", "memo",
"frequency", "first_period", "last_period", "posting_day", "status", "proposed_by", "proposed_sub",
"approved_by", "approved_sub", "ended_at:t", "end_reason")
gl_recurring_posting = T("gl_recurring_posting", "recurring_id:s*", "version:i*", "period_id:s*", "journal_id:b", "skipped_reason")
gl_accrual = T("gl_accrual", "company_code:s*", "accrual_code:s*", "kind", "ledger_id", "amount:m", "currency", "pl_account_id:b",
"balance_account_id:b", "cost_centre", "profit_centre", "start_period", "end_period", "method",
"source_journal_id:b", "status", "registered_by", "registered_sub", "approved_by", "approved_sub",
"terminated_period", "reject_reason")
gl_accrual_release = T("gl_accrual_release", "company_code:s*", "accrual_code:s*", "period_id:s*", "amount:m", "journal_id:b")
gl_allocation_cycle = T("gl_allocation_cycle", "cycle_id:s*", "version:i*", "company_code", "ledger_id", "valid_from_period",
"valid_to_period", "segments_json:x", "status", "author", "author_sub", "approved_by", "approved_sub",
"retired_reason")
gl_allocation_run = T("gl_allocation_run", "run_id:s*", "cycle_id", "cycle_version:i", "company_code", "ledger_id", "period_id",
"run_no:i", "status", "sender_digest", "results_json:x", "journal_ids", "simulated_by", "posted_by",
"reversed_by", "reason")
gl_key_figure_value = T("gl_key_figure_value", "key_figure:s*", "period_id:s*", "receiver:s*", "value:q", "entered_by", "entered_at:t",
"frozen_by_run")
# ── statements, budgets ────────────────────────────────────────────────────────────────────────────────────────────
gl_statement_version = T("gl_statement_version", "fsv_id:s*", "version_no:i*", "name", "kind", "chart_id", "status", "drafted_by",
"published_by", "published_at:t")
gl_statement_node = T("gl_statement_node", "fsv_id:s*", "version_no:i*", "node_id:s*", "parent_node_id", "seq:i", "label",
"sign:i", "cash_flow_class")
gl_statement_assignment = T("gl_statement_assignment", "fsv_id:s*", "version_no:i*", "from_code:s*", "to_code:s*", "node_id",
"dc_rule", "other_node_id", "basis")
gl_budget = T("gl_budget", "budget_id:s*", "version:i*", "company_code", "ledger_id", "fiscal_year", "status", "submitted_by",
"submitted_sub", "approved_by", "approved_sub", "return_reason")
gl_budget_line = T("gl_budget_line", "budget_id:s*", "version:i*", "account_id:b*", "profit_centre:s*", "cost_centre:s*", "period_id:s*",
"amount:m")
gl_statement = T("gl_statement", "report:s*", "company_code:s*", "ledger_id:s*", "period_id:s*", "account_id:b*", "debit:m", "credit:m",
"closing:m", "produced_at:t")
# ── the closing cockpit ────────────────────────────────────────────────────────────────────────────────────────────
gl_close_task_list = T("gl_close_task_list", "task_list_id:s*", "task_id:s*", "company_code", "kind", "title", "kind_of_task", "gate",
"owner_role", "due_business_day:i", "depends_on", "required:B")
gl_close_run = T("gl_close_run", "run_id:s*", "company_code", "period_id", "kind", "status", "task_list_id", "started_by", "started_sub",
"tb_digest", "signed_by", "signed_sub", "signed_at:t", "stale_reason")
gl_close_task = T("gl_close_task", "run_id:s*", "task_id:s*", "title", "kind_of_task", "gate", "owner_role", "due_on:d", "depends_on",
"status", "done_by", "done_sub", "done_at:t", "note", "evidence_key", "reason")
# ── trail ──────────────────────────────────────────────────────────────────────────────────────────────────────────
gl_journal_audit = T("gl_journal_audit", "audit_id:s*", "journal_id:b", "event", "by_email", "by_sub", "event_at:t")
gl_master_audit = T("gl_master_audit", "audit_id:s*", "entity", "natural_key", "before_json:x", "after_json:x", "by_email", "by_sub",
"event_at:t")
gl_audit_export = T("gl_audit_export", "export_id:s*", "kind", "company_code", "fiscal_year", "object_key", "sha256", "row_count:i",
"produced_by", "produced_sub", "produced_at:t")
gl_company_access = T("gl_company_access", "principal_sub:s*", "company_code:s*", "valid_from:d*", "principal_email", "valid_until:d",
"set_by", "set_at:t")
gl_ai_finding = T("gl_ai_finding", "finding_id:s*", "journal_id:b", "recon_id", "model", "inputs_digest", "finding", "evidence",
"decision", "decided_by", "decided_sub", "decided_at:t")
gl_settings = T("gl_settings", "setting_key:s*", "scope:s*", "valid_from:d*", "value", "set_by", "set_at:t")
gl_object = T("gl_object", "object_key:s*", "content_type", "body:x", "sha256")
# ── migration ──────────────────────────────────────────────────────────────────────────────────────────────────────
gl_migration_run = T("gl_migration_run", "run_id:s*", "odoo_db", "cutover_date:d", "status", "doors_json:x", "checks_json:x",
"counts_json:x", "checksums", "loaded_at:t", "signed_by", "signed_at:t", "reconciled_at:t")
gl_migration_map = T("gl_migration_map", "odoo_db:s*", "odoo_model:s*", "odoo_id:s*", "natural_key", "run_id")
class Repository:
"""The facade the scenario harness and the tests look for by name (`tables.Repository(conn)`): every repository of the
brick on one connection, defined in repositories.py (which reads these tables)."""
def __new__(cls, conn):
from brick_gl.infrastructure.repositories import Repository as Real
return Real(conn)