> ## Documentation Index
> Fetch the complete documentation index at: https://docs.getcanonic.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Guide: Ecommerce (Postgres)

> A small but complete Postgres project covering the full Phase 1 loop, evidence connectors, and observability.

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.

<Info>Full source: [`examples/ecommerce/`](https://github.com/mischuh/canonic/tree/main/examples/ecommerce)</Info>

## 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

```bash theme={null}
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

```bash theme={null}
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"`.

```json theme={null}
// 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

```json theme={null}
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"}]
    }
  }
```

```json theme={null}
// 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](/concepts/knowledge-layer) for how caveats auto-surface.

## MCP server: stdio vs. HTTP

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

```bash theme={null}
canonic mcp start
```

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

**HTTP daemon** (background process, multiple clients):

```bash theme={null}
canonic mcp start --transport http --port 7474 --token-ref env:CANONIC_MCP_TOKEN
canonic mcp status
canonic mcp stop
```

```json theme={null}
{ "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](/mcp-integration/connecting-your-agent#remote-enterprise-deployment) for both.

## Ingestion: keep semantics current

```bash theme={null}
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:

```bash theme={null}
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:

```yaml theme={null}
- 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](/concepts/ingestion-and-reconciliation).

## Evidence connectors beyond Postgres

Postgres introspection tells canonic what tables *exist*. [connectors](/concepts/connectors) tell it what those tables *mean*:

```yaml theme={null}
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/`](https://github.com/mischuh/canonic/tree/main/examples/ecommerce/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

```bash theme={null}
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](/concepts/instrumentation-and-eval#model-baseline-harness-canonic-eval-baseline).

## CLI usage

```bash theme={null}
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

```bash theme={null}
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](/concepts/instrumentation-and-eval#the-event-log) for exactly what is (and isn't) recorded.

## Air-gapped mode

```yaml theme={null}
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](/concepts/llm-runtime#air-gapped-mode--enforced-not-advisory).

## 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` |

```python theme={null}
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](/concepts/knowledge-layer) for the full retrieval and drift-detection model.