Accounting — Data model

20 tables · auto-generated from migrations-v2/*.sql — do not edit by hand.

ER diagram

%%{init: {"theme":"base","themeVariables":{"darkMode":true,"background":"#1e1e1e","primaryColor":"#2d2d30","primaryBorderColor":"#569cd6","primaryTextColor":"#d4d4d4","lineColor":"#6796c6","secondaryColor":"#3a3d41","secondaryBorderColor":"#dcdcaa","secondaryTextColor":"#d4d4d4","tertiaryColor":"#252526","tertiaryBorderColor":"#569cd6","noteBkgColor":"#3a3d41","noteTextColor":"#dcdcaa","clusterBkg":"#252526","clusterBorder":"#569cd6","titleColor":"#569cd6","attributeBackgroundColorOdd":"#252526","attributeBackgroundColorEven":"#2d2d30"},"er":{"useMaxWidth":false}}}%%
erDiagram
  ACCOUNTING_BANK_ACCOUNT {
    integer id PK
    text company_id FK
    text code
    text name
    text currency_code
    text gl_transaction_kind
    text payload
    integer created_at
  }
  ACCOUNTING_BANK_STATEMENT {
    integer id PK
    text company_id FK
    integer bank_account_id FK
    text statement_ref
    text start_date
    text end_date
    numeric opening_balance
    numeric closing_balance
  }
  ACCOUNTING_BANK_STATEMENT_LINE {
    integer id PK
    text company_id FK
    integer statement_id FK
    integer bank_account_id FK
    integer line_no
    text value_date
    numeric amount
    text bank_reference
  }
  ACCOUNTING_EVENT {
    integer id PK
    text company_id FK
    text event_name
    text aggregate_type
    text aggregate_id
    text payload
    integer created_at
  }
  ACCOUNTING_FX_REVAL_ITEM {
    integer id PK
    text company_id FK
    integer run_id FK
    integer party_id FK
    text origin_doctype
    text origin_doc_id
    text account_kind
    text account
  }
  ACCOUNTING_FX_REVAL_RUN {
    integer id PK
    text company_id FK
    integer kernel_document_id FK
    integer kernel_journal_entry_id FK
    text period_code
    integer run_no
    text rate_source
    text status
  }
  ACCOUNTING_OPEN_ITEM_SETTLEMENT {
    text company_id
    text against_doctype
    text against_doc_id
    real total_open
    real settled_amount
  }
  ACCOUNTING_PAYMENT {
    integer id PK
    text company_id FK
    integer party_id FK
    integer kernel_document_id FK
    integer kernel_journal_entry_id FK
    text doc_no
    text direction
    text posting_date
  }
  ACCOUNTING_PAYMENT_ALLOCATION {
    integer id PK
    text company_id FK
    integer payment_id FK
    integer party_id FK
    text direction
    text against_doctype
    text against_doc_id
    numeric allocated_amount
  }
  ACCOUNTING_PAYMENT_VOID {
    text company_id
    integer payment_id
    integer voided_at
    text voided_by
    integer wht_certificate_voided
  }
  ACCOUNTING_PERIOD {
    integer id PK
    text company_id FK
    text period_code
    text start_date
    text end_date
    text status
    integer created_at
  }
  ACCOUNTING_PERIOD_CLOSE_RUN {
    integer id PK
    text company_id FK
    integer kernel_posting_lock_id FK
    text period_code
    text start_date
    text end_date
    integer attempt
    text state
  }
  ACCOUNTING_RECONCILIATION_ACTIVE {
    text company_id FK
    integer statement_line_id FK
    integer match_id FK
    integer journal_entry_line_id FK
    text created_by
    integer created_at
  }
  ACCOUNTING_RECONCILIATION_EVENT {
    integer id PK
    text company_id FK
    integer statement_line_id FK
    integer journal_entry_line_id FK
    integer match_id FK
    text action
    text reason
    text payload
  }
  ACCOUNTING_RECONCILIATION_MATCH {
    integer id PK
    text company_id FK
    integer statement_line_id FK
    integer journal_entry_line_id FK
    text state
    numeric statement_amount
    numeric journal_amount
    numeric variance_amount
  }
  ACCOUNTING_RECURRING_JOURNAL_GENERATION {
    integer id PK
    text company_id FK
    integer template_id FK
    integer kernel_document_id FK
    integer kernel_journal_entry_id FK
    text run_date
    text payload
    integer created_at
  }
  ACCOUNTING_RECURRING_JOURNAL_TEMPLATE {
    integer id PK
    text company_id FK
    text code
    text description
    text schedule_rule
    text next_run_date
    text journal_code
    text currency_code
  }
  ACCOUNTING_TAX_RETURN_EXPORT {
    integer id PK
    text company_id FK
    text form_kind
    text period_code
    integer revision
    text totals
    text status
    integer created_at
  }
  ACCOUNTING_VAT_RETURN_PP30 {
    integer id PK
    text company_id FK
    text period_code
    text start_date
    text end_date
    numeric output_vat
    numeric input_vat
    numeric net_vat
  }
  ACCOUNTING_WHT_CERTIFICATE {
    integer id PK
    text company_id FK
    integer payment_id FK
    integer party_id FK
    integer kernel_document_id FK
    text cert_no
    text direction
    text wht_code
  }
  ACCOUNTING_BANK_ACCOUNT ||--o{ ACCOUNTING_BANK_STATEMENT : bank_account
  ACCOUNTING_BANK_STATEMENT ||--o{ ACCOUNTING_BANK_STATEMENT_LINE : statement
  ACCOUNTING_BANK_ACCOUNT ||--o{ ACCOUNTING_BANK_STATEMENT_LINE : bank_account
  ACCOUNTING_FX_REVAL_RUN ||--o{ ACCOUNTING_FX_REVAL_ITEM : run
  ACCOUNTING_PAYMENT ||--o{ ACCOUNTING_PAYMENT_ALLOCATION : payment
  ACCOUNTING_BANK_STATEMENT_LINE ||--o{ ACCOUNTING_RECONCILIATION_ACTIVE : statement_line
  ACCOUNTING_RECONCILIATION_MATCH ||--o{ ACCOUNTING_RECONCILIATION_ACTIVE : match
  ACCOUNTING_BANK_STATEMENT_LINE ||--o{ ACCOUNTING_RECONCILIATION_EVENT : statement_line
  ACCOUNTING_RECONCILIATION_MATCH ||--o{ ACCOUNTING_RECONCILIATION_EVENT : match
  ACCOUNTING_BANK_STATEMENT_LINE ||--o{ ACCOUNTING_RECONCILIATION_MATCH : statement_line
  ACCOUNTING_RECURRING_JOURNAL_TEMPLATE ||--o{ ACCOUNTING_RECURRING_JOURNAL_GENERATION : template
  ACCOUNTING_PAYMENT ||--o{ ACCOUNTING_WHT_CERTIFICATE : payment

Cross-module references

• ACCOUNTING_BANK_ACCOUNT.company_id → COMPANY (core)
• ACCOUNTING_BANK_STATEMENT.company_id → COMPANY (core)
• ACCOUNTING_BANK_STATEMENT_LINE.company_id → COMPANY (core)
• ACCOUNTING_EVENT.company_id → COMPANY (core)
• ACCOUNTING_FX_REVAL_ITEM.company_id → COMPANY (core)
• ACCOUNTING_FX_REVAL_ITEM.party_id → PARTY (core)
• ACCOUNTING_FX_REVAL_RUN.company_id → COMPANY (core)
• ACCOUNTING_FX_REVAL_RUN.kernel_document_id → DOCUMENT (core)
• ACCOUNTING_FX_REVAL_RUN.kernel_journal_entry_id → JOURNAL_ENTRY (core)
• ACCOUNTING_PAYMENT.company_id → COMPANY (core)
• ACCOUNTING_PAYMENT.party_id → PARTY (core)
• ACCOUNTING_PAYMENT.kernel_document_id → DOCUMENT (core)
• ACCOUNTING_PAYMENT.kernel_journal_entry_id → JOURNAL_ENTRY (core)
• ACCOUNTING_PAYMENT_ALLOCATION.company_id → COMPANY (core)
• ACCOUNTING_PAYMENT_ALLOCATION.party_id → PARTY (core)
• ACCOUNTING_PERIOD.company_id → COMPANY (core)
• ACCOUNTING_PERIOD_CLOSE_RUN.company_id → COMPANY (core)
• ACCOUNTING_PERIOD_CLOSE_RUN.kernel_posting_lock_id → POSTING_LOCK (core)
• ACCOUNTING_RECONCILIATION_ACTIVE.company_id → COMPANY (core)
• ACCOUNTING_RECONCILIATION_ACTIVE.journal_entry_line_id → JOURNAL_ENTRY_LINE (core)
• ACCOUNTING_RECONCILIATION_EVENT.company_id → COMPANY (core)
• ACCOUNTING_RECONCILIATION_EVENT.journal_entry_line_id → JOURNAL_ENTRY_LINE (core)
• ACCOUNTING_RECONCILIATION_MATCH.company_id → COMPANY (core)
• ACCOUNTING_RECONCILIATION_MATCH.journal_entry_line_id → JOURNAL_ENTRY_LINE (core)
• ACCOUNTING_RECURRING_JOURNAL_GENERATION.company_id → COMPANY (core)
• ACCOUNTING_RECURRING_JOURNAL_GENERATION.kernel_document_id → DOCUMENT (core)
• ACCOUNTING_RECURRING_JOURNAL_GENERATION.kernel_journal_entry_id → JOURNAL_ENTRY (core)
• ACCOUNTING_RECURRING_JOURNAL_TEMPLATE.company_id → COMPANY (core)
• ACCOUNTING_TAX_RETURN_EXPORT.company_id → COMPANY (core)
• ACCOUNTING_VAT_RETURN_PP30.company_id → COMPANY (core)
• ACCOUNTING_WHT_CERTIFICATE.company_id → COMPANY (core)
• ACCOUNTING_WHT_CERTIFICATE.party_id → PARTY (core)
• ACCOUNTING_WHT_CERTIFICATE.kernel_document_id → DOCUMENT (core)

Data dictionary

erp_accounting_bank_account · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
name	text
currency_code	text
gl_transaction_kind	text
payload	text
created_at	integer

erp_accounting_bank_statement · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
bank_account_id	integer	FK	erp_accounting_bank_account
statement_ref	text
start_date	text
end_date	text
opening_balance	numeric
closing_balance	numeric
status	text
payload	text
created_at	integer

erp_accounting_bank_statement_line · 13 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
statement_id	integer	FK	erp_accounting_bank_statement
bank_account_id	integer	FK	erp_accounting_bank_account
line_no	integer
value_date	text
amount	numeric
bank_reference	text
counterparty_text	text
reconciliation_state	text
reconciliation_match_id	integer
payload	text
created_at	integer

erp_accounting_event · 7 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
event_name	text
aggregate_type	text
aggregate_id	text
payload	text
created_at	integer

erp_accounting_fx_reval_item · 17 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
run_id	integer	FK	erp_accounting_fx_reval_run
origin_doctype	text
origin_doc_id	text
party_id	integer	FK	erp_party
account_kind	text
account	text
txn_currency	text
txn_amount	numeric
old_rate	numeric
closing_rate	numeric
functional_balance	numeric
revalued_balance	numeric
delta_amount	numeric
payload	text
created_at	integer

erp_accounting_fx_reval_run · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
period_code	text
run_no	integer
rate_source	text
status	text
kernel_document_id	integer	FK	erp_document
kernel_journal_entry_id	integer	FK	erp_journal_entry
payload	text
created_by	text
created_at	integer

erp_accounting_open_item_settlement · 5 cols

column	type	key	references
company_id	text
against_doctype	text
against_doc_id	text
total_open	real
settled_amount	real

erp_accounting_payment · 19 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
doc_no	text
direction	text
party_id	integer	FK	erp_party
posting_date	text
currency_code	text
gross_amount	numeric
cash_amount	numeric
wht_amount	numeric
wht_code	text
kernel_document_id	integer	FK	erp_document
kernel_journal_entry_id	integer	FK	erp_journal_entry
status	text
idempotency_key	text
source_ref	text
payload	text
created_by	text
created_at	integer

erp_accounting_payment_allocation · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
payment_id	integer	FK	erp_accounting_payment
direction	text
party_id	integer	FK	erp_party
against_doctype	text
against_doc_id	text
allocated_amount	numeric
currency_code	text
posting_date	text
created_at	integer

erp_accounting_payment_void · 5 cols

column	type	key	references
company_id	text
payment_id	integer
voided_at	integer
voided_by	text
wht_certificate_voided	integer

erp_accounting_period · 7 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
period_code	text
start_date	text
end_date	text
status	text
created_at	integer

erp_accounting_period_close_run · 13 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
period_code	text
start_date	text
end_date	text
attempt	integer
state	text
open_draft_count	integer
kernel_posting_lock_id	integer	FK	erp_posting_lock
reason	text
payload	text
created_by	text
created_at	integer

erp_accounting_reconciliation_active · 6 cols

column	type	key	references
company_id	text	FK	erp_company
statement_line_id	integer	FK	erp_accounting_bank_statement_line
match_id	integer	FK	erp_accounting_reconciliation_match
journal_entry_line_id	integer	FK	erp_journal_entry_line
created_by	text
created_at	integer

erp_accounting_reconciliation_event · 10 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
statement_line_id	integer	FK	erp_accounting_bank_statement_line
journal_entry_line_id	integer	FK	erp_journal_entry_line
match_id	integer	FK	erp_accounting_reconciliation_match
action	text
reason	text
payload	text
created_by	text
created_at	integer

erp_accounting_reconciliation_match · 11 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
statement_line_id	integer	FK	erp_accounting_bank_statement_line
journal_entry_line_id	integer	FK	erp_journal_entry_line
state	text
statement_amount	numeric
journal_amount	numeric
variance_amount	numeric
payload	text
created_by	text
created_at	integer

erp_accounting_recurring_journal_generation · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
template_id	integer	FK	erp_accounting_recurring_journal_template
run_date	text
kernel_document_id	integer	FK	erp_document
kernel_journal_entry_id	integer	FK	erp_journal_entry
payload	text
created_at	integer

erp_accounting_recurring_journal_template · 12 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
code	text
description	text
schedule_rule	text
next_run_date	text
journal_code	text
currency_code	text
lines	text
payload	text
created_by	text
created_at	integer

erp_accounting_tax_return_export · 8 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
form_kind	text
period_code	text
revision	integer
totals	text
status	text
created_at	integer

erp_accounting_vat_return_pp30 · 13 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
period_code	text
start_date	text
end_date	text
output_vat	numeric
input_vat	numeric
net_vat	numeric
source	text
status	text
revision	integer
payload	text
created_at	integer

erp_accounting_wht_certificate · 15 cols

column	type	key	references
id	integer	PK
company_id	text	FK	erp_company
cert_no	text
direction	text
payment_id	integer	FK	erp_accounting_payment
party_id	integer	FK	erp_party
wht_code	text
income_type	text
base_amount	numeric
wht_amount	numeric
posting_date	text
period_code	text
kernel_document_id	integer	FK	erp_document
payload	text
created_at	integer