Skip to content
All work

Regulated agriculture

Read-only AI analytics over a 361-table legacy compliance database

Live, read-only questions against a licensed operator's on-site compliance database, plus a reconciliation against the books.

Client
A state-licensed regulated-agriculture operator
System
Compliance Analytics
My role
Design and build
When
Jul to Sep 2026
Farm operations dashboard, sample data
Sample dataProduction, retail and accounting pulled nightly from the state compliance system.

01 / Problem

What wasn't working.

Compliance data lived in an old on-site PostgreSQL server with no reporting. The owner wanted answers without risking the system the state audits.

02 / Approach

What I built.

  1. 01

    A dedicated read-only database role over a private encrypted network. Nothing can write.

  2. 02

    The database registered as an MCP server so Claude can query it live. A schema catalog explains what the 361 tables mean; about 40 hold data.

  3. 03

    An hourly sync worker on the store PC feeds an owner dashboard. 18 registered queries, 16 rules, no personal data shown.

  4. 04

    A books module reconciles accounting entries against tracking data.

Lab results, sample data
Sample dataLab results by lot and product from the compliance system.
Harvest records, sample data
Sample dataGrow and harvest records by plant and strain.

03 / Architecture

How the pieces connect.

PostgreSQLMCPClaudeNext.js 16ConvexPowerShell worker
SQLanswersCompliance DBon siteRead-only roleprivate networkMCP serverClaudeHourly syncstore PCOwner dashboard16 rulesSQLanswersCompliance DBon siteRead-only roleprivate networkMCP serverClaudeHourly syncstore PCOwnerdashboard16 rules
DataEdge / networkServiceAIPeople / UI

04 / Result

361

tables mapped in a legacy compliance database, read-only. The owner asks questions in plain language and gets live answers. 27 of 38 wholesale entries matched the books exactly on the first pass.

Read-only role

The safest way to get value from a system you cannot risk.

Live queries

MCP connection, answers from current data.

Rulebook

16 rules on what the assistant may say. No personal data.

Books check

Accounting matched against tracking records.

Have a problem like this one?

Book a call

Next step

Tell me what isn't working.

A 30-minute call. You describe the problem, I tell you straight whether it's worth fixing and roughly what it costs.

Book a call