Guide: Ecommerce (Postgres)

A small but end-to-end canonic project: a Postgres connection, a four-source star schema (two facts, three dimensions), three canonical metrics, three guardrail contracts (a refund filter plus a finality-backed board-reporting restriction), and a companion dbt manifest demonstrating the definition-connector class. This is the broadest walkthrough of the full loop: bootstrap, serve, evidence connectors, accuracy tracking, and observability all in one place.

Full source: examples/ecommerce/

Schema

customers ──< orders >── channels
                 │
              order_items >── products

orders_rt: intraday provisional mirror of orders (same grain, no order_items)

Table	Rows	Description
`customers`	5	Customer accounts (`country` dimension)
`channels`	3	Sales channels
`products`	5	Product catalogue (`category` dimension)
`orders`	10	Final orders, join target for `revenue`/`order_count`
`orders_rt`	(intraday)	Provisional same-day order estimates. `finality-revenue` coalesces this with `orders`
`order_items`	17	Line items, join target for `units_sold`

Setup

export CANONIC_PG_PASSWORD=postgres
psql "postgres://postgres:${CANONIC_PG_PASSWORD}@localhost:5432/postgres" < setup.sql

cd examples/ecommerce   # canonic commands must run from here
canonic status
# Canonic project: ecommerce-demo (version 1)
# Root: /path/to/examples/ecommerce
# Connection: warehouse_pg (postgres)

setup.sql is idempotent: re-running it drops and recreates all tables in the correct order.

Quickstart

canonic ingest --bootstrap                  # bootstrap: introspect Postgres → write semantics/*.yaml
canonic query --metrics revenue --dimensions order_date   # guardrail fires automatically
canonic mcp start                           # serve: agents call query() + search_knowledge() together
canonic eval baseline \                     # (optional) track: measure grain-inference accuracy
  --candidates candidates.yaml \
  --dataset eval/grain_cases.jsonl

Each step proves one Phase 1 exit criterion:

Step	Criterion
`canonic ingest --bootstrap`	Bootstraps context from a real stack
`query()` + `search_knowledge()`	Agents get both executable definitions and business meaning
`canonic eval baseline`	Accuracy is tracked

canonic status, canonic ingest --bootstrap, canonic query, and canonic mcp start never call the LLM: every table here has a declared primary key, so grain is inferred deterministically. CANONIC_LLM_API_KEY only matters for canonic eval baseline, which explicitly benchmarks the configured model.

Metrics

Metric	Source · measure	Aliases
`revenue`	`orders.total_revenue`	”net revenue”, “rev”
`order_count`	`orders.order_count`	”orders”, “number of orders”
`units_sold`	`order_items.units_sold`	”units”, “quantity sold”

Guardrails

contracts/guardrails/ ships 3 contracts, not just the one refund filter:

revenue-excludes-refunds (mandatory_filter): AND-s status != 'refunded' into every query touching orders.total_revenue. Expected revenue after the guardrail: 3790.50 (7 completed + 1 pending order. The seed data’s two refunded orders, totaling 260.00, never appear).
board-final-only (restrict_source): in a context: board_reporting query, confines revenue to the final orders source only, excluding the intraday orders_rt estimates.
finality-revenue: not a guardrail kind itself, but the paired finality rule board-final-only enforces: it declares orders as the final realization (watermark business_day - 1 day) and orders_rt as provisional, with coalescing: "window <= watermark ? final : provisional".

// Board reporting confines revenue to the final orders source, dropping today's provisional orders_rt rows
query({"metrics": ["revenue"], "dimensions": ["order_date"], "context": "board_reporting"})

Example queries

query({"metrics": ["revenue"], "dimensions": ["order_date"]})
→ {
    "result": { "columns": [...], "rows": [["2025-01-10T00:00:00", 500.0], ...] },
    "compiled": { "sql": "SELECT … WHERE \"orders\".\"status\" <> 'refunded' …", "dialect": "postgres" },
    "metadata": {
      "resolved": {"metrics": {"revenue": "orders.total_revenue"}},
      "guardrails_fired": [{"id": "revenue-excludes-refunds", "kind": "mandatory_filter"}]
    }
  }

// revenue joined to customers, order_count in the same call
query({"metrics": ["revenue", "order_count"], "dimensions": ["country"]})

// business meaning alongside the executable SQL
search_knowledge("revenue reporting policy")
→ {
    "hits": [{"page": "revenue-reporting-policy", "usage_mode": "policy", ...}],
    "caveats": [{"page": "revenue-excludes-refunds-caveat", "triggered_by": [...]}]
  }

A typical agent pattern is query() for executable SQL + search_knowledge() for business context: both calls together, one decision. See Knowledge for how caveats auto-surface.

MCP server: stdio vs. HTTP

Stdio (the MCP client owns the process: Claude Code, Cursor):

canonic mcp start

{ "mcpServers": { "canonic": { "command": "canonic", "args": ["mcp", "start"], "cwd": "/absolute/path/to/examples/ecommerce" } } }

HTTP daemon (background process, multiple clients):

canonic mcp start --transport http --port 7474 --token-ref env:CANONIC_MCP_TOKEN
canonic mcp status
canonic mcp stop

{ "mcpServers": { "canonic": { "transport": "streamable-http", "url": "http://127.0.0.1:7474/mcp", "headers": { "Authorization": "Bearer your-token-here" } } } }

The server uses FastMCP’s Streamable HTTP transport at /mcp (SSE at /sse for clients that only support that). Only --transport http writes .canonic/mcp.json: in stdio mode the MCP client owns the process and no state file is created. --transport http is network-reachable, so it requires at least one auth mechanism: a bearer token (mcp.auth.tokens in canonic.yaml or --token-ref) and/or OAuth 2.1 (mcp.auth.oauth, for SSO-integrated organizations). See Connecting your agent for both.

Ingestion: keep semantics current

canonic ingest --dry-run     # see what would change, write nothing
canonic ingest --bootstrap   # fresh project: introspect and draft from scratch
canonic ingest               # full run: propose diffs for review

Headless / CI: deterministic pipeline + auto-PR:

canonic --json ingest --headless --strict
# exit 0  → clean run (PR opened if diffs, or no-op)
# exit 13 → CONNECTION_ERROR, Postgres unreachable
# exit 14 → CONTRADICTION, --strict flagged a drift that conflicts with a curated fact

Example CI job:

- name: canonic ingest
  run: canonic --json ingest --headless --strict
  working-directory: examples/ecommerce
  env:
    CI: "true"
    CANONIC_PG_PASSWORD: ${{ secrets.CANONIC_PG_PASSWORD }}

If the live schema drifts from a human_curated file (e.g. a column type changes), ingest flags a contradiction but keeps the curated file untouched. See Ingestion & reconciliation.

Evidence connectors beyond Postgres

Postgres introspection tells canonic what tables exist. connectors tell it what those tables mean:

connections:
  - id: warehouse_dbt
    type: dbt
    params:
      manifest_path: dbt/manifest.json   # no credentials: a manifest is a local file

  - id: handbook_notion
    type: notion
    credentials_ref: env:NOTION_TOKEN

  - id: bi_metabase
    type: metabase
    params: { base_url: https://metabase.internal }
    credentials_ref: env:METABASE_API_KEY

This demo ships a compiled dbt manifest modeling the same star schema. canonic ingest --connection warehouse_dbt --dry-run reconciles it into semantic proposals with no Postgres and no LLM: modeling-tier evidence outranks raw introspection where they overlap, and a genuine disagreement (e.g. conflicting column types) surfaces as a contradiction, never a silent merge. To make the Notion evidence flow concrete without a live workspace, the demo ships five sample page sources at docs/notion-pages/, the format the Notion connector expects, including the two page properties it reads (Canonic Type → usage_mode, Canonic Topics → candidate topic refs).

Accuracy tracking

canonic eval baseline \
  --candidates candidates.yaml \
  --dataset eval/grain_cases.jsonl
# gemma-4-e2b-it-4bit: accuracy 80%, structured-output 100%, p50 310 ms ✓ recommended

The five cases in eval/grain_cases.jsonl exercise the shape of the live schema: a single surrogate key, a descriptive surrogate key, and a line-item fact where the grain is a single column rather than a composite key. See Instrumentation & evaluation.

CLI usage

canonic --json query --metrics revenue --dimensions order_date    # byte-identical to the MCP query tool

canonic sql "SELECT status, sum(amount) FROM analytics.fct_orders GROUP BY status"
canonic sql "DROP TABLE analytics.fct_orders"
# error: read_only_violation …
# echo $? → 11

Observability

canonic audit
# canonic audit  (telemetry: off)
# answers:        42  (2026-06-01T08:00:00Z → 2026-06-19T16:45:12Z)
# latency:        p50 310ms  p95 1240ms  ...

Every query served appends a served_answer event to the local, git-ignored event log. Every canonic ingest run appends reconcile_decision events to the same log. See Instrumentation & evaluation for exactly what is (and isn’t) recorded.

Air-gapped mode

runtime:
  air_gapped: true   # blocks telemetry.enabled: true at load time
  allow_cidrs:
    - 10.0.0.0/8      # optional: an on-prem inference host outside loopback

The daemon never starts misconfigured: a public LLM endpoint or a remote secret ref under air_gapped: true is a hard error at load. See LLM & embeddings runtime.

Knowledge pages

knowledge/global/ adds searchable business context on top of the semantic layer:

Page	`usage_mode`	Effect
`revenue-definition`	`definition`	Canonical prose definition, surfaced by search
`units-sold-definition`	`definition`	Canonical prose definition for `units_sold`
`revenue-excludes-refunds-caveat`	`caveat`	Auto-surfaced whenever a result references `total_revenue`
`revenue-reporting-policy`	`policy`	Month-end cutoff rules
`order-items-fanout-caveat`	`caveat`	Auto-surfaced whenever a result references `units_sold`/`line_revenue`

from pathlib import Path
from canonic.knowledge import KnowledgeSearch, EntityIndex, load_knowledge_page
from canonic.semantic.loader import list_semantic_sources

root = Path(".")
sources = list_semantic_sources(root)
entity_index = EntityIndex.from_sources(sources)
pages = [load_knowledge_page(p) for p in (root / "knowledge" / "global").glob("*.md")]

engine = KnowledgeSearch(pages)
result = engine.search("month-end cutoff", requesting_user="alice")
print([h.page for h in result.hits])       # ['revenue-reporting-policy']
print([(c.page, c.triggered_by) for c in result.caveats])
# [('revenue-excludes-refunds-caveat', ['warehouse_pg.orders.total_revenue'])]

See Knowledge for the full retrieval and drift-detection model.

Getting Started

Concepts

CLI Reference

MCP / Agent Integration

Guides

Reference

Guide: Ecommerce (Postgres)

Schema

Setup

Quickstart

Metrics

Guardrails

Example queries

MCP server: stdio vs. HTTP

Ingestion: keep semantics current

Evidence connectors beyond Postgres

Accuracy tracking

CLI usage

Observability

Air-gapped mode

Knowledge pages

​Schema

​Setup

​Quickstart

​Metrics

​Guardrails

​Example queries

​MCP server: stdio vs. HTTP

​Ingestion: keep semantics current

​Evidence connectors beyond Postgres

​Accuracy tracking

​CLI usage

​Observability

​Air-gapped mode

​Knowledge pages

Schema

Setup

Quickstart

Metrics

Guardrails

Example queries

MCP server: stdio vs. HTTP

Ingestion: keep semantics current

Evidence connectors beyond Postgres

Accuracy tracking

CLI usage

Observability

Air-gapped mode

Knowledge pages