Reporting and Forecasting

Reporting should initially be based on views over normalized operational data, not duplicated reporting tables.

Core reporting sources

Source	Role
bank_transaction_splits	Actual classified income and expense lines.
rent_installment_lines	Expected rent composition.
expected_expenses	Expected cost schedule and forecast base.
utility_bills	Received utility bills.
utility_consumptions	Consumption trends and anomaly detection base.
transaction_categories	Accounting and fiscal grouping.

Recommended views

View	Purpose
v_property_income_expense_monthly	Monthly actual income and expenses by property and category.
v_property_cashflow_forecast	Future rent and expense cash-flow forecast.
v_tax_report_italy_yearly	Yearly Italian tax reporting source.
v_contract_payment_status	Contract-level rent status, paid amounts, overdue installments, and partial payments.
v_expense_expected_vs_actual	Expected expenses compared with actual matched bank movements.
v_utility_consumption_trends	Consumption trends by property, service, and period.
v_unclassified_bank_transactions	Imported transactions requiring classification.

Forecasting model

Forecasting should combine expected future rent from rent_installments and expected future costs from expected_expenses.

Forecast input	Description
Contract schedule	Rent frequency, due day, start/end dates, and active status.
Rent installment lines	Expected income/reimbursement composition.
Expected expenses	Recurring or one-off cost forecasts.
Category metadata	Tax relevance, deductibility, and report groups.
Historical actuals	Split-level historical amounts used for averages and anomaly thresholds.

Alerting scenarios

Scenario	Detection source
Rent installment overdue	rent_installments.status, due_date, and rent_payment_matches.
Expected expense overdue	expected_expenses.status, due_date, and expense_payment_matches.
Bank transaction not classified	bank_transactions.classification_status.
Bank transaction not reconciled	Missing rent or expense match records.
Utility bill higher than expected tolerance	utility_bills.total_amount compared with expected_expenses tolerance fields.
Utility consumption anomaly	utility_consumptions compared with historical trend.
Expense category exceeds budget	Aggregated bank_transaction_splits by category and period.
Contract expiration approaching	contracts.end_date and contracts.status.
Missing document for active contract	Active contracts without linked documents.
Tax payment due soon	expected_expenses with tax categories and due dates.

Future optimization

Start with SQL views because they keep reporting close to source data and avoid synchronization bugs. If views become slow, introduce report snapshots or materialized summaries with explicit refresh rules.

Materialized reporting should not replace operational tables. It should be treated as a performance layer built from bank_transaction_splits, expected schedules, utility data, and category metadata.