1.31M contract records, illustrated

flagged record
6 indicators
append-only log

Ledgerlight

An audit platform that flags risky Government of Canada contracts and writes LLM briefs that can't make numbers up.

TypeScript
Node.js
Express
React 19
MySQL
Gemini API
JWT
RBAC

The problem

1.31M Government of Canada contract records are too many to review by hand, so the risky ones have to be surfaced systematically.

An LLM can draft the brief, but in an audit a single invented number is a liability.

What I built

Detection

Six SQL red-flag indicators

Parameterized rules over 1.31M contract records: non-competitive awards, single-bid competitions, amendment inflation, threshold splitting, repeat awards and buyer concentration.

MySQL · CTE · date-windowed self-join

Grounding

Briefs that can't make numbers up

Gemini drafts each brief. Every number must resolve through a JSON path into the evidence pack and match its value, and every red flag must cite an indicator that actually fired. A failing draft is re-prompted with the exact reasons, up to 3 attempts, then replaced by a deterministic brief built only from the evidence.

Gemini API · Zod · JSON paths · provenance tags

Data layer

Least privilege, enforced by MySQL grants

Three separated database identities: admin for migrations, a SELECT-only identity for everything the model touches, and an app identity that can only append to the audit log. An integration test connects as the app identity and is denied UPDATE and DELETE.

MySQL grants · integration test

Workflow

Human review board

Analysts move cases through new, under review, substantiated or dismissed. Illegal transitions are rejected with a 409, every decision needs a written justification, and each move is audit-logged.

Express · JWT · RBAC · React 19

How it works

Contract records

1.31M Government of Canada records

streaming two-pass ETL, batched multi-row inserts

MySQL

3 separated identities · append-only audit log enforced by grants

6 parameterized SQL red-flag indicators

Evidence pack

JSON: every number in a brief must come from here

Gemini drafts the brief

Grounding validator

numbers → JSON paths · red flags → fired indicators

pass

Model brief

every number traced to evidence

fail

Re-prompt, then fallback

exact reasons fed back, up to 3 attempts; then a deterministic brief

App layer

React 19front end

Express APIJWT · RBAC

MySQLapp identity

Review board: illegal status transitions are rejected, and every decision needs a written justification.

Grounding check

Simulated demo

Made-up evidence pack and brief, checked in your browser. Validate tries to resolve each highlighted number to a JSON path.

Evidence packJSON

{
  "vendor": {
    "canonical_name": "Vendor A"
  },
  "profile": {
    "contract_count": 7,
    "total_value": 212500,
    "buyer_count": 2
  }
}

BriefModel draft

Vendor A holds 7 contracts in the loaded data.

unchecked
Its prices ran 38% above comparable vendors.

unchecked

2 numbers to check

Proof

1.31MGovernment of Canada contract records

6SQL red-flag indicators

66unit tests gate CI on every push to main

16red-team attacks, all rejected in CI

3separated DB identities

Red team: 16 seeded attack briefs, all rejected on every CI run, including invented or wrong figures, evidence-path tricks, scale-shorthand abuse, target laundering, and citations of rules that didn't fire or law that wasn't retrieved. 5 well-formed controls must still pass.
Validator: 32 of the 66 unit tests exercise the grounding validator directly.
Grants: An integration test, run against a live MySQL, connects as the app identity and is denied UPDATE and DELETE on the audit log.