Currently SQL-Ledger stores all accounting data in the table “acc_trans”, short for Account Transactions. This table stores all our debit and credit line data. Hence this table includes data from “General Ledger”, “Account receivables” as well as from “Account Payable”. The table structure is as followed:
Column | Type | Collation | Nullable | Default
----------------+------------------+-----------+----------+---------------------------------------------
trans_id | integer | | |
chart_id | integer | | |
amount | double precision | | |
transdate | date | | | 'now'::text::date
source | text | | |
approved | boolean | | | true
fx_transaction | boolean | | | false
project_id | integer | | |
memo | text | | |
id | integer | | |
cleared | date | | |
vr_id | integer | | |
tax_chart_id | integer | | |
entry_id | integer | | not null | nextval('acc_trans_entry_id_seq'::regclass)
tax | text | | |
taxamount | numeric | | |
Indexes:
"acc_trans_chart_id_key" btree (chart_id)
"acc_trans_chart_id_transdate_approved_trans_id" btree (chart_id, transdate, approved, trans_id, amount)
"acc_trans_source_key" btree (lower(source))
"acc_trans_trans_id_key" btree (trans_id)
"acc_trans_transdate_key" btree (transdate)
Let’s break this down:
trans_id: ID of what transaction, this transaction line is for. This is primarily the foreign the key for this table. The ID can refer to a general ledger transaction, an accounts payable transaction or an account receivable transaction. The way SQL-Ledger is structured, respective tables (gl, ar, ap) for the different types of transaction store the “header” data while acc_trans store the transaction line/account data.
chart_id: foreign key passed from the “chart” table. Refers to what account number the transaction line debits or credits.
amount: amount for this particular transaction. a (-) amount means the entry is a debit entry while a (+) makes it a credit entry.
transdate: date the transaction line was created
source: source of transaction, stored for user reference (text field, can be anything such as cash, cheque etc).
approved: 1 by default. Used if the transaction has a voucher assigned.
fx_transaction: if the transaction is a foreign transaction or not
project_id: foreign key passed from the ‘project’ table. Used only if the transaction is part of a project
memo: Individual line memo that can be entered by user at the time of creation or update
entry_id: primary key for the acc_trans table
cleared: user for bank reconciliation
vr_id: Voucher ID, used if the transaction relates to a voucher
tax_chart_id: Stores chart_id from ‘chart’ if a tax is being applied
taxamount: The amount of tax applied to the transaction line. If a tax is applicable, the tax_chart_id
will point to the relevant account in the chart
table, and taxamount
will reflect the tax’s monetary value. The system handles taxes by creating additional transaction lines that record the tax amount as either a debit or a credit, depending on whether the original transaction line is a debit or credit.