Treasury & Payroll — DB Tables & UI Pages

PK — primary key

FK — foreign key

computed — never stored

Treasury module — 5 tables

treasury_accounts

Treasury

id	uuid PK	System-generated
school_id	uuid FK	Scoped to school
name	varchar	e.g. "BK Main Account"
type	enum	BANK \| CASH \| MOMO
account_number	varchar?	Optional bank/MoMo ref
bank_name	varchar?	For BANK type only
gl_account_id	uuid FK	→ COA asset account
opening_balance	numeric(15,2)	Balance at creation
opening_date	date	When OB applies from
status	enum	ACTIVE \| INACTIVE
notes	text?	Internal notes
current_balance	computed	OB + money_in − money_out
created_at	timestamptz	UTC
updated_at	timestamptz	UTC

treasury_transactions

Treasury

id	uuid PK	System-generated
school_id	uuid FK	Scoped to school
reference_number	varchar	TXN-YYYY-NNNNNN
account_id	uuid FK	→ treasury_accounts
direction	enum	MONEY_IN \| MONEY_OUT
amount	numeric(15,2)	Must be > 0
transaction_date	date	Within open period
gl_account_id	uuid FK	Contra COA account
description	varchar	Required — reason
status	enum	DRAFT \| POSTED \| REVERSED
posted_at	timestamptz?	Set on posting
posted_by	uuid FK?	→ users
notes	text?
attachments	jsonb?	File refs array
created_at	timestamptz
updated_at	timestamptz

treasury_transfers

Treasury

id	uuid PK
school_id	uuid FK
reference_number	varchar	TRF-YYYY-NNNNNN
from_account_id	uuid FK	→ treasury_accounts
to_account_id	uuid FK	→ treasury_accounts
amount	numeric(15,2)	≤ from_account balance
transfer_date	date	Within open period
description	varchar	Required
status	enum	DRAFT \| POSTED
posted_at	timestamptz?
posted_by	uuid FK?	→ users
notes	text?
created_at	timestamptz

bank_reconciliation_sessions

Treasury

id	uuid PK
school_id	uuid FK
account_id	uuid FK	→ treasury_accounts
period_start	date	Statement period start
period_end	date	Statement period end
statement_closing_balance	numeric(15,2)	From bank statement
status	enum	OPEN \| CLOSED
closed_at	timestamptz?	Set when closed
closed_by	uuid FK?	→ users
created_at	timestamptz

bank_statement_lines

Treasury

id	uuid PK
reconciliation_id	uuid FK	→ recon sessions
date	date	Date on statement
description	varchar	Statement narration
amount	numeric(15,2)	+ in / − out
match_status	enum	UNMATCHED \| MATCHED \| EXCLUDED
matched_txn_id	uuid FK?	→ txn or transfer
matched_txn_type	enum?	TRANSACTION \| TRANSFER
created_at	timestamptz

Payroll module — 6 tables

employee_payroll_profiles

Payroll

id	uuid PK
school_id	uuid FK
employee_id	uuid FK	→ HR employees table
employee_number	varchar	EMP-NNNNNN
department	varchar	Teaching / Admin / Support
employment_type	enum	PERMANENT \| CONTRACT
salary_type	enum	FIXED_MONTHLY \| CONTRACT_ONEOFF
base_salary	numeric(15,2)?	Required if FIXED_MONTHLY
gl_salary_expense_id	uuid FK	→ COA expense account
allowances	jsonb?	Array of allowance objects
bank_account_number	varchar?	For bank transfer
bank_name	varchar?
status	enum	ACTIVE \| INACTIVE
effective_from	date	Profile active from
notes	text?
created_at	timestamptz
updated_at	timestamptz

payroll_runs

Payroll

id	uuid PK
school_id	uuid FK
reference_number	varchar	PAY-YYYY-NNNNNN
period_start	date	e.g. 2025-11-01
period_end	date	e.g. 2025-11-30
payroll_type	enum	MONTHLY \| CONTRACT
status	enum	DRAFT \| SUBMITTED \| APPROVED \| POSTED \| PARTIALLY_PAID \| PAID \| VOIDED
total_gross	numeric(15,2)	Sum of line gross_pay
total_paye	numeric(15,2)	Sum of line paye
total_emp_rssb	numeric(15,2)	Pension + maternity
total_net_pay	numeric(15,2)	Sum of line net_pay
total_er_rssb_pension	numeric(15,2)	Employer pension
total_er_rssb_maternity	numeric(15,2)	Employer maternity
total_er_occ_hazard	numeric(15,2)	Employer occ. hazard
total_employer_cost	numeric(15,2)	Gross + employer contribs
notes	text?
created_by	uuid FK	→ users
approved_by	uuid FK?	→ users
posted_at	timestamptz?
created_at	timestamptz

payroll_lines

Payroll

id	uuid PK
payroll_run_id	uuid FK	→ payroll_runs
employee_id	uuid FK	→ HR employees
base_salary	numeric(15,2)	Snapshot from profile
allowances_breakdown	jsonb	Snapshot at run creation
gross_pay	numeric(15,2)	base + sum(allowances)
rssb_pension_base	numeric(15,2)	= gross (incl. transport)
maternity_base	numeric(15,2)	gross − transport allowances
employee_pension	numeric(15,2)	pension_base × 6%
employee_maternity	numeric(15,2)	maternity_base × 0.3%
total_emp_rssb	numeric(15,2)	pension + maternity
taxable_income	numeric(15,2)	gross − total_emp_rssb
paye	numeric(15,2)	Progressive calc, round up
total_deductions	numeric(15,2)	rssb + paye
net_pay	numeric(15,2)	gross − total_deductions
employer_pension	numeric(15,2)	pension_base × 6%
employer_maternity	numeric(15,2)	maternity_base × 0.3%
employer_occ_hazard	numeric(15,2)	gross × 2%
total_er_contributions	numeric(15,2)	Sum employer side
employer_cost	numeric(15,2)	gross + er_contributions
rate_snapshot	jsonb	Rates used at calc time
is_manual_override	boolean	Was any amount adjusted?
override_note	text?	Audit trail if overridden
created_at	timestamptz

payroll_disbursements

Payroll

id	uuid PK
school_id	uuid FK
payroll_run_id	uuid FK	→ payroll_runs
disbursement_type	enum	BANK_TRANSFER \| MANUAL
treasury_account_id	uuid FK?	→ treasury_accounts ★
amount	numeric(15,2)	≤ undisbursed net pay
disbursement_date	date	Within open period
reference	varchar?	Bank batch ref
notes	text?
status	enum	DRAFT \| POSTED
posted_at	timestamptz?
posted_by	uuid FK?	→ users
created_at	timestamptz

payroll_remittances

Payroll

id	uuid PK
school_id	uuid FK
payroll_run_id	uuid FK	→ payroll_runs
remittance_type	enum	PAYE \| RSSB_PENSION \| RSSB_MATERNITY \| RSSB_OCC_HAZARD
treasury_account_id	uuid FK	→ treasury_accounts ★
amount	numeric(15,2)
remittance_date	date
reference	varchar?	RRA / RSSB payment ref
status	enum	DRAFT \| POSTED
posted_at	timestamptz?
posted_by	uuid FK?	→ users
created_at	timestamptz

statutory_rate_configs

Payrollconfig

id	uuid PK
school_id	uuid FK
rate_type	enum	EMP_RSSB_PENSION \| ER_RSSB_PENSION \| EMP_MATERNITY \| ER_MATERNITY \| ER_OCC_HAZARD
rate_value	numeric(6,4)	e.g. 0.0600 = 6%
effective_date	date	When this rate applies from
notes	text?	e.g. "RSSB increase 2027"
created_at	timestamptz

Companion table: paye_tax_bands — lower, upper, rate, effective_date

Treasury module — 8 pages

Accounts overview

accounting/treasury/

List

Cards grouped by type: BANK / CASH / MOMO

Each card: name, account number, current balance (RWF), last transaction date, status badge

Subtotals per group + grand total liquid assets

+ New Account button → modal/form

Click card → Account detail page

Account detail

accounting/treasury/accounts/[id]

Detail

Header: name, type badge, current balance (large), status

Tab — Transactions: list with running balance column, filters (date range, direction)

Tab — Transfers: all transfers in/out of this account

Tab — Reconciliation: list of all sessions for this account

Actions: + New Transaction, + New Transfer, + Start Reconciliation

Transactions list

accounting/treasury/transactions/

List

Filters: account, direction, status, date range

Columns: date, reference, account, description, direction badge, amount, status

DRAFT rows: edit + post + delete actions

POSTED rows: view + reverse action

+ New Transaction button

New / edit transaction

accounting/treasury/transactions/new

Form

Fields: Account (dropdown), Direction toggle (In / Out), Amount, Date, Contra GL account (COA picker), Description, Notes, Attachments

Actions: Save Draft, Save & Post

Post triggers GL entry — correctable only by reversal

Transfers list + new

accounting/treasury/transfers/

List + Form

List: date, reference, from account, to account, amount, status

New Transfer form: From account, To account (must differ), Amount, Date, Description

Validation: from_account balance must cover amount before posting

Actions: Save Draft, Save & Post

Reconciliation — session list

accounting/treasury/reconciliation/

List

Filters: account, status (OPEN / CLOSED), date range

Columns: account, period, statement closing balance, status, matched count, unmatched count

+ Start Reconciliation button → new session form

Click row → session workspace

Reconciliation — workspace

accounting/treasury/reconciliation/[id]

Workspace

Header: account, period, statement closing balance, system reconciled balance, difference (must hit 0 to close)

Left panel: unmatched statement lines

Right panel: unmatched system transactions for the period

Match action: select one from each side → Match button

Unresolved statement line → "Create Transaction" inline

Import: upload CSV/Excel or enter lines manually

Close Session button (enabled only when difference = 0)

Treasury reports

accounting/treasury/reports/

Reports

Account Balance Summary — all accounts, grouped, totalled

Transaction History — per account, date range, running balance

Cash Flow Summary — money in vs out by GL account

Reconciliation Report — per session, matched/unmatched, difference

All exportable: Excel, PDF, CSV

Payroll module — 10 pages

Payroll dashboard

accounting/payroll/

Dashboard

Metric cards: current month total gross, total net pay, pending runs, outstanding disbursements

Recent payroll runs table: ref, period, status badge, total net pay

Upcoming remittance deadlines (PAYE / RSSB due 15th of next month)

Quick actions: + New Monthly Run, + New Contract Payment

Employee profiles list

accounting/payroll/employees/

List

Filters: department, employment type, status

Columns: employee number, name, department, salary type, base salary, status

Search by name or employee number

+ New Profile button

Employee profile detail / edit

accounting/payroll/employees/[id]

Detail + Form

Employee info header (name, department, type, status, effective_from)

Salary section: salary type, base salary, GL expense account

Allowances: repeatable rows — name, amount, is_transport toggle, is_taxable toggle, GL account

Payment details: bank name, account number

Payslip history tab: list of all past payslips (view / PDF export per row)

Payroll runs list

accounting/payroll/runs/

List

Filters: status, payroll type, period (month picker)

Columns: reference, period, type, status badge, total gross, total net, total employer cost

Status badges: DRAFT (gray) → SUBMITTED (blue) → APPROVED (amber) → POSTED (green) → PAID (green) → VOIDED (red)

+ New Monthly Run, + New Contract Run buttons

Payroll run detail

accounting/payroll/runs/[id]

Detail + Workflow

Header: reference, period, status, summary totals (gross / deductions / net / employer cost)

Tab — Lines: one row per employee, edit if DRAFT, inline manual override with note field

Tab — Disbursements: list + Record Disbursement button (if POSTED+)

Tab — Remittances: PAYE and RSSB records + Record Remittance button

Tab — Payslips: all payslips, export all as ZIP

Action bar: Submit → Approve → Post (role-gated each step)

Record disbursement

accounting/payroll/runs/[id]/disburse

Form

Run summary (read-only): total net pay, already disbursed, remaining

Fields: Type (Bank Transfer / Manual), Treasury account picker, Amount, Date, Reference, Notes

Validation: amount ≤ remaining undisbursed net pay

Post creates GL: DR Net Salaries Payable / CR Bank

Record remittance

accounting/payroll/runs/[id]/remit

Form

Remittance type selector: PAYE / RSSB Pension / RSSB Maternity / RSSB Occ. Hazard

Shows outstanding amount for selected type (computed from run)

Fields: Treasury account, Amount, Date, Reference, Notes

Post creates GL: DR [relevant payable] / CR Bank

Payslip viewer

accounting/payroll/payslips/[employee_id]/[run_id]

View only

School header: name, logo

Employee: name, number, department, type, period

Earnings table: base salary + each allowance → Gross Pay

Deductions table: RSSB pension, maternity, PAYE → Total Deductions

Net Pay (highlighted) + employer contributions section

Export to PDF button

Payroll reports

accounting/payroll/reports/

Reports

Payroll Summary — per run, all lines, full breakdown

PAYE Report — per employee, taxable income, PAYE withheld (for RRA submission)

RSSB Report — per employee, all contributions (for RSSB submission)

Cost by Department — aggregated gross + employer cost

Remittance History — all PAYE + RSSB payments made

All exportable: Excel, PDF, CSV (payslips: PDF only)

Statutory rates config

accounting/payroll/settings/rates

Settings

Admin-only page

PAYE bands table: lower bound, upper bound, rate %, effective date

RSSB rates table: rate type, rate %, effective date — one row per type per period

History: all past rate configurations preserved

Warning banner if a rate change is scheduled within 30 days

Treasury — foreign keys

treasury module FK map

treasury_accounts.school_id	→	schools.id
treasury_accounts.gl_account_id	→	chart_of_accounts.id (asset type)
treasury_transactions.account_id	→	treasury_accounts.id
treasury_transactions.gl_account_id	→	chart_of_accounts.id (income/expense)
treasury_transactions.posted_by	→	users.id
treasury_transfers.from_account_id	→	treasury_accounts.id (must ≠ to_account_id)
treasury_transfers.to_account_id	→	treasury_accounts.id
treasury_transfers.posted_by	→	users.id
bank_reconciliation_sessions.account_id	→	treasury_accounts.id
bank_reconciliation_sessions.closed_by	→	users.id
bank_statement_lines.reconciliation_id	→	bank_reconciliation_sessions.id
bank_statement_lines.matched_txn_id	→	treasury_transactions.id OR treasury_transfers.id (polymorphic — use matched_txn_type)

Payroll — foreign keys

payroll module FK map

employee_payroll_profiles.school_id	→	schools.id
employee_payroll_profiles.employee_id	→	hr_employees.id (HR module)
employee_payroll_profiles.gl_salary_expense_id	→	chart_of_accounts.id (expense type)
payroll_runs.school_id	→	schools.id
payroll_runs.created_by	→	users.id
payroll_runs.approved_by	→	users.id
payroll_lines.payroll_run_id	→	payroll_runs.id (cascade delete if voided)
payroll_lines.employee_id	→	hr_employees.id
payroll_disbursements.payroll_run_id	→	payroll_runs.id (must be POSTED)
payroll_disbursements.treasury_account_id	→	treasury_accounts.id ★ cross-module
payroll_disbursements.posted_by	→	users.id
payroll_remittances.payroll_run_id	→	payroll_runs.id (must be POSTED)
payroll_remittances.treasury_account_id	→	treasury_accounts.id ★ cross-module
statutory_rate_configs.school_id	→	schools.id

Cross-module bridges — Payroll ↔ Treasury

The only two places where Payroll reaches into Treasury:

payroll_disbursements.treasury_account_id → treasury_accounts.id
Salary payment goes out of this account. This FK is what makes money actually leave when net salaries are disbursed.

payroll_remittances.treasury_account_id → treasury_accounts.id
PAYE (to RRA) and RSSB contributions also exit via a Treasury account. Same bridge, different purpose.

Treasury never reaches back into Payroll. Purchases & Sales and School Fees also reference treasury_accounts for their payments — Treasury owns the accounts, other modules consume them via FK.

Table count summary

Treasury — 5 tables

treasury_accounts

treasury_transactions

treasury_transfers

bank_reconciliation_sessions

bank_statement_lines

Payroll — 6 tables

employee_payroll_profiles

payroll_runs

payroll_lines

payroll_disbursements

payroll_remittances

statutory_rate_configs

Treasury & Payroll — Database & UI Reference

treasury_accounts

treasury_transactions

treasury_transfers

bank_reconciliation_sessions

bank_statement_lines

employee_payroll_profiles

payroll_runs

payroll_lines

payroll_disbursements

payroll_remittances

statutory_rate_configs

Accounts overview

Account detail

Transactions list

New / edit transaction

Transfers list + new

Reconciliation — session list

Reconciliation — workspace

Treasury reports

Payroll dashboard

Employee profiles list

Employee profile detail / edit

Payroll runs list

Payroll run detail

Record disbursement

Record remittance

Payslip viewer

Payroll reports

Statutory rates config