Reports, scheduled jobs and the audit log

Three things that tell you whether the system is doing what it should: what the numbers say, what runs overnight, and what can be proved.

The built-in reports

Reports carries seven, each a question somebody actually asks:

• Income by service — what each thing earned, and how many schools bought it.
• Take-up by school — who buys from you and, more usefully, who buys nothing.
• Purchase history — every line, on which order, with its purchase order number.
• Delegate history — who has been on what, and whether they turned up.
• Assigned e-learning — where schools have asked their own staff to do something and not got it done.
• Outstanding renewals — what has not been decided yet.
• Sold but not yet billed — anything that has not reached a finance batch.

Beside them, a responsiveness view: how quickly each team answers what it is asked. All of them filter, and all export as CSV and PDF.

Building an eighth

Built reports is for the question the seven do not answer. Choose a subject — order lines, contracts, delegates, learning, or schools — then the columns, the conditions, the grouping and the sort.

Nothing you type becomes a query. A report holds the names of fields from a fixed catalogue, and a name that is not in it selects nothing, filters on nothing and groups by nothing. Every value is a bound parameter.

It refuses reports that would mislead

Nobody checks a figure that arrived in a table, so the builder refuses the definitions that would be wrong rather than rendering them: grouping by an amount, a grouped report where nothing is summarised, a chart on an ungrouped report, a condition with no value, summing something that is not a number.

Money conditions are read in pounds, because the form is in pounds. "More than 5000" means five thousand pounds.

Scheduling one

A built report can be sent on a cycle, with the figures in the body of the message rather than attached, so it reads on a phone without opening anything. Retiring the report stops it being sent.

Jobs

Jobs lists everything that runs on its own: the renewal finalising, the waiting lists advancing, the e-learning chase, the training requirements being kept met, the finance deliveries, the return imports, the alert summaries, the help-chat chase, the backup, and the tidying of expired sessions.

Each shows when it last ran and what it did. You can run one now, which is the right way to check a change rather than waiting until tomorrow.

The audit log

Audit is every consequential action in the system. Each entry carries the hash of the one before it, the database refuses any update or delete against the table, and the head of the chain is kept separately so that truncation is detectable too.

Verifying walks the whole chain and names the first entry that does not check out. Run it after a restore, and run it if anybody ever asks whether the log can be trusted; being able to answer with a check rather than an assurance is the whole reason it is built this way.

Passwords and secrets are recorded as having changed, never as their value.

Backups

A backup runs nightly and is kept for thirty days. Test a restore occasionally: a backup nobody has restored is a hypothesis.