Payments and Reconciliation
The model separates expected amounts from actual bank movements. Expected rent and expenses are scheduled in dedicated entities. Actual movements are imported into bank_transactions and normalized through bank_transaction_splits.
bank_transaction_splits
Purpose
Normalized accounting and fiscal representation of actual transaction lines. This is the main source for reporting.
Suggested attributes
| Field | Description |
|---|---|
id | Primary identifier. |
bank_transaction_id | Source bank transaction. |
property_id | Property attribution for the split line. |
contract_id | Optional contract attribution. |
expected_expense_id | Optional expected expense attribution. |
rent_installment_id | Optional rent installment attribution. |
category_id | Mandatory transaction category. |
amount | Split amount. |
description | Split description. |
created_at | Creation timestamp. |
Relationships
| Relationship | Description |
|---|---|
bank_transaction_splits.bank_transaction_id to bank_transactions.id | One transaction can have many splits. |
bank_transaction_splits.category_id to transaction_categories.id | Every split has one governed category. |
bank_transaction_splits.property_id to properties.id | Split-level property attribution. |
bank_transaction_splits.contract_id to contracts.id | Optional contract attribution. |
bank_transaction_splits.expected_expense_id to expected_expenses.id | Optional expense attribution. |
bank_transaction_splits.rent_installment_id to rent_installments.id | Optional rent attribution. |
Notes and design rationale
A EUR 1,200 incoming transaction can be split into EUR 900 rent income, EUR 200 condominium reimbursement, and EUR 100 utilities. Reports should aggregate split lines, not raw bank rows.
rent_installments
Purpose
Expected rent payment schedule generated from contracts.
Suggested attributes
| Field | Description |
|---|---|
id | Primary identifier. |
contract_id | Source contract. |
due_date | Expected payment due date. |
period_start | Rental period start. |
period_end | Rental period end. |
total_amount | Expected total installment amount. |
currency | Currency. |
status | EXPECTED, PAID, PARTIALLY_PAID, OVERDUE, or CANCELLED. |
notes | Free-form notes. |
created_at | Creation timestamp. |
updated_at | Last update timestamp. |
Relationships
| Relationship | Description |
|---|---|
rent_installments.contract_id to contracts.id | Each installment belongs to one contract. |
rent_installments to rent_installment_lines | One installment has many expected lines. |
rent_installments to rent_payment_matches | Installments can be reconciled with bank transactions. |
Notes and design rationale
Installments represent what should happen. They are not proof of payment until matched with bank movements.
rent_installment_lines
Purpose
Expected rent payment line items.
Suggested attributes
| Field | Description |
|---|---|
id | Primary identifier. |
rent_installment_id | Parent rent installment. |
category_id | Expected category. |
description | Line description. |
amount | Expected amount. |
currency | Currency. |
Relationships
| Relationship | Description |
|---|---|
rent_installment_lines.rent_installment_id to rent_installments.id | One installment can have many lines. |
rent_installment_lines.category_id to transaction_categories.id | Each expected line has one category. |
Notes and design rationale
Example for a EUR 1,200 installment: EUR 900 rent, EUR 200 condominium expenses, EUR 100 utilities.
rent_payment_matches
Purpose
Reconciliation between expected rent installments and actual bank transactions.
Suggested attributes
| Field | Description |
|---|---|
id | Primary identifier. |
rent_installment_id | Expected installment. |
bank_transaction_id | Actual bank transaction. |
matched_amount | Amount reconciled by this match. |
match_type | AUTOMATIC or MANUAL. |
matched_at | Match timestamp. |
notes | Free-form notes. |
Relationships
| Relationship | Description |
|---|---|
rent_payment_matches.rent_installment_id to rent_installments.id | One installment can have many matches. |
rent_payment_matches.bank_transaction_id to bank_transactions.id | One transaction can match one or more installments. |
Notes and design rationale
Separate match records preserve auditability and support partial payments, overpayments, and manual corrections.
expected_expenses
Purpose
Expected expenses for forecasting, alerting, anomaly detection, and reconciliation.
Suggested attributes
| Field | Description |
|---|---|
id | Primary identifier. |
property_id | Linked property. |
property_unit_id | Optional linked unit. |
property_service_id | Optional linked service. |
provider_party_id | Expected provider party. |
category_id | Expected expense category. |
due_date | Expected due date. |
period_start | Expense period start. |
period_end | Expense period end. |
expected_amount | Expected amount. |
currency | Currency. |
status | EXPECTED, PAID, PARTIALLY_PAID, OVERDUE, or CANCELLED. |
recurrence_rule | Recurrence rule for future generation. |
tolerance_amount | Absolute tolerance for matching/anomaly checks. |
tolerance_percent | Percentage tolerance for matching/anomaly checks. |
notes | Free-form notes. |
created_at | Creation timestamp. |
updated_at | Last update timestamp. |
Relationships
| Relationship | Description |
|---|---|
expected_expenses.property_id to properties.id | Each expected expense belongs to a property. |
expected_expenses.property_unit_id to property_units.id | Optional unit-level expense. |
expected_expenses.property_service_id to property_services.id | Optional service-level expense. |
expected_expenses.provider_party_id to parties.id | Provider party. |
expected_expenses.category_id to transaction_categories.id | Mandatory category. |
expected_expenses to expense_payment_matches | Expected expenses can be matched to actual transactions. |
Notes and design rationale
This table is the base for cash-flow forecasting, overdue alerts, and expected-versus-actual analysis.
expense_payment_matches
Purpose
Reconciliation between expected expenses and actual bank transactions.
Suggested attributes
| Field | Description |
|---|---|
id | Primary identifier. |
expected_expense_id | Expected expense. |
bank_transaction_id | Actual bank transaction. |
matched_amount | Amount reconciled by this match. |
match_type | AUTOMATIC or MANUAL. |
matched_at | Match timestamp. |
notes | Free-form notes. |
Relationships
| Relationship | Description |
|---|---|
expense_payment_matches.expected_expense_id to expected_expenses.id | One expected expense can have many matches. |
expense_payment_matches.bank_transaction_id to bank_transactions.id | One transaction can match one or more expected expenses. |
Notes and design rationale
This enables partial payment handling and keeps matching decisions separate from raw bank data.