Watch the Floor: Visual Retail Analytics on DODIL — Footfall, Dwell, Segmentation, Journeys, and Loss Prevention from Your Camera Feeds

What you'll build: the analytics brain behind a retail chain's camera network — on one DataK3 bucket, fronted by a FastAPI app and a React console you can download, build and deploy. Frames never pile up in a warehouse: an edge box at each branch turns them into anonymous events, and those events answer three ways over one copy of the rows — by content (SQL: footfall, dwell, queue, conversion), by meaning (vector: segmentation and appearance re-ID over a VECTOR(2048) table), and by relationship (graph: the path a shopper walks through the store). Above the branches sits a consolidated HQ layer — 15 stores in 7 countries, daily KPIs rolled up to chain and country with FX applied, a league table and an outlier band. Security rides the same event stream (after-hours intrusion, unattended bags, spills). No ETL, no Snowflake + Neo4j + Pinecone to keep in sync, no per-seat analytics licence.

What you'll learn:

• Model camera analytics on merge-keyed DataK3 tables — an agent prompt, the dodil data CLI, and a plain SQLAlchemy model, three ways at once — with idempotent upserts.
• Call the four live perception models — and get the three different image encodings they each demand right the first time.
• Roll up footfall / dwell / queue as an idempotent SQL recompute you can re-run safely.
• Consolidate 15 branches across 7 countries into a chain view: daily KPIs → country/chain rollups with FX → a ranked league table and outlier band, all recomputed idempotently.
• Stand up an appearance vector — a VECTOR(2048) column, jina-embeddings-v4, cosine KNN, a real vector index — to segment shoppers and spot returning-looking visitors without storing a single face.
• Project the store into a zone graph and traverse a shopper's journey with graph_khop.
• Keep inference cheap with a first-class sampling_policy — the dial that decides how many frames ever reach a model.
• Keep it lawful with an app-configurable jurisdiction_policy — anonymous by default; identity is opt-in, consent-gated, and off until a DPIA says otherwise.
• Ship it: git push → the forge builds the image → .dodil/deploy.yaml deploys it → a live URL with end-user login the app writes no code for.

The problem — and why it matters

A regional coffee chain has 120 branches, each already wired with IP cameras for security. The owner can tell you last quarter's revenue per store — but not the questions that actually move it:

• How many people walked in, and how many bought? The gap between the door count and the till count is the single most valuable number in retail, and nobody has it.
• Where do queues form, and when? A 4-minute wait at 8:10am is lost morning-rush revenue, store by store.
• Where do people linger — and where don't they? Dwell time at the pastry case vs the cold-brew fridge tells you what to move to eye level.
• Which branches convert, and why? Same brand, same menu — a 3× spread in walk-in→purchase, unexplained.
• What did we lose, and was anyone hurt? After-hours intrusion, an unattended bag, a spill nobody flagged for 20 minutes.
• And at HQ: which branch is actually the problem? Fifteen stores trading in three currencies, and no single screen that says "NYC Midtown did £1.16m and converts at 39.5%; Bristol did £42.7k and converts at 17.5%."

The off-the-shelf answer is a people-counting vendor (per-camera SaaS, data leaves the country), a separate heatmap product, a separate loss-prevention system, and a warehouse to glue their exports together — four bills, four copies of the footage-derived data, and customers' images sitting on someone else's US cloud. On DODIL it's one bucket on sovereign EMEA hardware, and the camera frames never leave the store.

The shape — one bucket, three pillars, a pluggable detector

The design has four moving parts, and only one of them is heavy:

1. The edge agent (one small box per branch) pulls RTSP frames, runs a cheap motion gate, and only then asks a detector "what's in this frame?" It builds short-lived tracks (a person crossing the shop), and emits anonymous events — no image, no identity — to the API. Raw frames stay in the store.
2. The detector is pluggable. Everything downstream depends on one interface — DetectionBackend — so the same app runs against DODIL's hosted vision models, an on-prem model, or a synthetic backend for tests.
3. DataK3 holds every event once and answers three ways: SQL (metrics), vector (segmentation), graph (journeys). Security events land in the same bucket.
4. Two policy engines sit in front: sampling_policy (how much inference you pay for) and jurisdiction_policy (what you're allowed to do with a face, per region).

Everything below was built and validated on one bucket, retailvis-core01, in uk-lon-1 — 21 tables carrying the whole chain: 15 branches, 7 countries, 583 detection events, 109 tracklets, 105 appearance vectors, 840 daily branch KPIs, 1,176 consolidated rollup rows and 45 benchmark rows. One bucket, no second system.

Stand up the core — the tables every branch writes to

Each table is merge-keyed, so writes are idempotent upserts — a re-sent event or a shard retry can't double-count footfall. Every data step carries a third ORM tab: the exact class from the package's models.py, the same op a third way.

export BUCKET=retailvis-core01
 
dodil data table create branches -b "$BUCKET" --merge-key branch_id \
  --columns-json '[
    {"name":"branch_id","type":"string","nullable":false},
    {"name":"name","type":"string","nullable":true},
    {"name":"region","type":"string","nullable":true},
    {"name":"tz","type":"string","nullable":true},
    {"name":"jurisdiction_code","type":"string","nullable":true}
  ]'
 
dodil data table create zones -b "$BUCKET" --merge-key zone_id \
  --columns-json '[
    {"name":"zone_id","type":"string","nullable":false},
    {"name":"branch_id","type":"string","nullable":true},
    {"name":"name","type":"string","nullable":true},
    {"name":"kind","type":"string","nullable":true}
  ]'
 
dodil data table create cameras -b "$BUCKET" --merge-key camera_id \
  --columns-json '[
    {"name":"camera_id","type":"string","nullable":false},
    {"name":"branch_id","type":"string","nullable":true},
    {"name":"zone_id","type":"string","nullable":true},
    {"name":"label","type":"string","nullable":true},
    {"name":"rtsp_ref","type":"string","nullable":true}
  ]'

# models.py — topology (plain SQLAlchemy 2.0, natural keys)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
 
class Base(DeclarativeBase): ...
 
class Branch(Base):
    __tablename__ = "branches"
    branch_id: Mapped[str] = mapped_column(primary_key=True)
    name: Mapped[str | None]
    region: Mapped[str | None]
    tz: Mapped[str | None]
    jurisdiction_code: Mapped[str | None]
 
class Zone(Base):
    __tablename__ = "zones"
    zone_id: Mapped[str] = mapped_column(primary_key=True)
    branch_id: Mapped[str | None]
    name: Mapped[str | None]
    kind: Mapped[str | None]          # entrance | counter | queue | seating | exit
 
class Camera(Base):
    __tablename__ = "cameras"
    camera_id: Mapped[str] = mapped_column(primary_key=True)
    branch_id: Mapped[str | None]
    zone_id: Mapped[str | None]
    label: Mapped[str | None]
    rtsp_ref: Mapped[str | None]      # a reference, not a credential — the stream stays in-store

Next the event spine — the anonymous detections the edge emits, the tracks it stitches them into, and the appearance vector that powers segmentation. A track is one visit; events are the raw boxes (they carry the branch_id, so a tracklet is scoped by the events that reference it); appearance holds one VECTOR(2048) per track, embedded once from a representative crop — never per frame.

dodil data table create events -b "$BUCKET" --merge-key event_id \
  --columns-json '[
    {"name":"event_id","type":"string","nullable":false},
    {"name":"branch_id","type":"string","nullable":true},
    {"name":"camera_id","type":"string","nullable":true},
    {"name":"zone_id","type":"string","nullable":true},
    {"name":"event_at","type":"timestamp","nullable":true},
    {"name":"cls","type":"string","nullable":true},
    {"name":"confidence","type":"double","nullable":true},
    {"name":"bbox","type":"json","nullable":true},
    {"name":"track_id","type":"string","nullable":true}
  ]'
 
dodil data table create tracks -b "$BUCKET" --merge-key track_id \
  --columns-json '[
    {"name":"track_id","type":"string","nullable":false},
    {"name":"entered_at","type":"timestamp","nullable":true},
    {"name":"exited_at","type":"timestamp","nullable":true},
    {"name":"dwell_secs","type":"long","nullable":true},
    {"name":"zone_path","type":"json","nullable":true},
    {"name":"member_id","type":"string","nullable":true}
  ]'
 
dodil data table create appearance -b "$BUCKET" --merge-key track_id \
  --columns-json '[
    {"name":"track_id","type":"string","nullable":false},
    {"name":"descriptor","type":"string","nullable":true},
    {"name":"embedding","type":"VECTOR(2048)","nullable":true}
  ]'

# models.py — the event spine
from datetime import datetime
from sqlalchemy import JSON
from pgvector.sqlalchemy import Vector
 
class Event(Base):
    __tablename__ = "events"
    event_id: Mapped[str] = mapped_column(primary_key=True)
    branch_id: Mapped[str | None]
    camera_id: Mapped[str | None]
    zone_id: Mapped[str | None]
    event_at: Mapped[datetime | None]
    cls: Mapped[str | None]                 # person | cup | bag | spill | ...
    confidence: Mapped[float | None]
    bbox: Mapped[dict | None] = mapped_column(JSON)
    track_id: Mapped[str | None]
 
class Track(Base):
    __tablename__ = "tracks"
    track_id: Mapped[str] = mapped_column(primary_key=True)
    entered_at: Mapped[datetime | None]
    exited_at: Mapped[datetime | None]
    dwell_secs: Mapped[int | None]
    zone_path: Mapped[list | None] = mapped_column(JSON)
    member_id: Mapped[str | None]           # '' for anonymous — identity is opt-in only
 
class Appearance(Base):
    __tablename__ = "appearance"
    track_id: Mapped[str] = mapped_column(primary_key=True)
    descriptor: Mapped[str | None]          # human-readable caption of the crop
    embedding: Mapped[list[float] | None] = mapped_column(Vector(2048))

The remaining tables — zone_transitions (the edges the journey graph is built from), zone_nodes / zone_edges (its integer-keyed projection), footfall_15m (the SQL rollup), security_events, the two policy tables sampling_policy / jurisdiction_policy, and the six enterprise tables (countries, regions, fx_rates, kpi_branch_daily, kpi_rollup, branch_benchmark) — are created the same way; they're all in models.py in the package. The two identity tables (loyalty_members, face_templates with a VECTOR(512) ArcFace column) exist too, but they stay empty until you deliberately turn identity on — see Consent & jurisdiction.

The edge agent and the pluggable detector

The edge is where cost and privacy are decided. The agent reads its branch's sampling_policy, pulls frames at the configured rate, runs a free motion gate, and only sends surviving frames to a detector. Everything talks to one interface:

# detection.py — the seam the whole system pivots on
from abc import ABC, abstractmethod
 
class DetectionBackend(ABC):
    @abstractmethod
    def detect_objects(self, frame) -> list[dict]: ...      # [{cls, confidence, bbox}, ...]
    @abstractmethod
    def detect_faces(self, frame) -> list[dict]: ...        # opt-in path only
    @abstractmethod
    def embed_appearance(self, crop) -> list[float]: ...    # -> VECTOR(2048), anonymous
    @abstractmethod
    def embed_face(self, face) -> list[float]: ...          # -> VECTOR(512), consent-gated
 
class DodilBackend(DetectionBackend):
    """Real DODIL Models. Object detection = mm-gdino-large; face detection = scrfd-34g;
    face template = arcface-r100; appearance = jina-embeddings-v4. All four are LIVE.
    Cold-start aware: bounded exponential backoff on 504 / RunPod blips."""
 
class SyntheticBackend(DetectionBackend):
    """Deterministic boxes + vectors — offline dev, CI, no token, no network."""

Because the app depends only on DetectionBackend, you pick the detector per deployment and nothing downstream changes.

The four models — and the three image encodings that will bite you

All four perception models are live and verified. The trap nobody warns you about: they belong to three different model families, and each family takes a different image encoding. Get it wrong and you don't always get an error — gdino fed bare base64 deserialises happily and returns an empty detection list.

Open-vocabulary object detection is the workhorse — you hand it the classes you care about per camera, so "person, cup, bag, spill" is configuration, not a retrained model:

# mm-gdino-large: `input` MUST be a data: URI string. A bare base64 string deserialises
# but silently returns detections:[] with a 0x0 image — the single nastiest gotcha here.
IMG="data:image/jpeg;base64,$(base64 -i street.jpg)"
jq -n --arg m mm-gdino-large --arg i "$IMG" \
  '{model:$m, input:$i, labels:["person","bag","cup"]}' > body.json
 
curl -s -X POST https://api.dodil.io/v1/infer \
  -H "Authorization: Bearer $SA_TOKEN" -H 'Content-Type: application/json' \
  -d @body.json | jq '.data[0].detections | group_by(.label) | map({label: .[0].label, n: length})'
# -> [ {"label":"bag","n":3}, {"label":"cup","n":1}, {"label":"person","n":12} ]
# each detection: {"box":[x1,y1,x2,y2], "label":"person", "score":0.70}   <- ABSOLUTE PIXELS

Face detection is the mirror image of that call — same /infer route, opposite encoding. It is also the only model on this page that is not part of the default path: it runs solely where jurisdiction_policy permits enrolment (see Consent & jurisdiction).

# scrfd-34g: an OBJECT whose `image` is RAW base64 — a data: URI here fails to base64-decode.
# The exact inverse of the gdino rule above.
jq -n --arg m scrfd-34g --arg b "$(base64 -i street.jpg)" \
  '{model:$m, input:{type:"image", image:$b}}' > face_body.json
 
curl -s -X POST https://api.dodil.io/v1/infer \
  -H "Authorization: Bearer $SA_TOKEN" -H 'Content-Type: application/json' \
  -d @face_body.json | jq '{faces: (.detections|length), first: .detections[0]}'
# -> {"faces": 14,
#     "first": {"bbox":[0.31,0.28,0.35,0.36],      <- NORMALISED 0..1
#               "label":"face","score":0.83,"landmarks":[[x,y],[x,y],[x,y],[x,y],[x,y]]}}

The two embedding models share one shape — the OpenAI multimodal content object — and differ only in the dimension they return: 2048 for jina (the anonymous appearance vector, appearance.embedding) and 512 for ArcFace (the consent-gated face template, face_templates.embedding). The image path cold-starts, so the first hit or two after idle can time out; DodilBackend absorbs that with bounded backoff.

# jina-embeddings-v4 (2048-dim) and arcface-r100 (512-dim) take the SAME content-object form.
# The detection-style {"type":"image", ...} is rejected here with "Unknown input type".
CROP="data:image/jpeg;base64,$(base64 -i crop.jpg)"
jq -n --arg m jina-embeddings-v4 --arg u "$CROP" \
  '{model:$m, input:[{type:"image_url", image_url:{url:$u}}]}' > emb_body.json
 
curl -s -X POST https://api.dodil.io/v1/embeddings \
  -H "Authorization: Bearer $SA_TOKEN" -H 'Content-Type: application/json' \
  -d @emb_body.json | jq '.data[0].embedding | length'
# -> 2048          (swap the model for arcface-r100 on a face crop -> 512)

$SA_TOKEN is an OIDC client_credentials access token for a service account holding ignite-authorization-service ignite.model-user — the same identity the deployed app uses. Nothing here is a placeholder; detection.py in the package makes exactly these three calls.

Sampling — the dial that keeps inference cheap

You never run a heavy detector on 30 frames a second. sampling_policy is a row per camera the edge reads live — change it and cost changes with no redeploy:

# one row per camera — the edge honours it every tick
{
  "policy_id": "lon-soho:cam-counter",
  "fps": 2.0,                    # sample 2 frames/sec off a 25fps stream
  "motion_gate": true,           # skip frames with no motion — a quiet aisle costs nothing
  "models": ["objects"],         # which model tiers run here (faces only where enrolment happens)
  "active_hours": {"open": "06:30", "close": "20:00"},   # security models flip on when analytics flips off
  "budget_calls_per_min": 120    # hard ceiling; the edge buffers + drops-to-sample above it
}

With fps: 2 + a motion gate that idles ~70% of the time, a busy counter camera is well under one detector call per second — dozens per minute, not thousands. Because the cheap gates run on the edge box for free, only the surviving frames ever reach DODIL's billed models.

Footfall, dwell, and queue — the SQL pillar

The metrics are a plain, idempotent recompute over the event spine. Re-running it never double-counts, because the rollup is an INSERT … ON CONFLICT (bucket_id) DO UPDATE SET … = EXCLUDED.…:

-- footfall_15m rollup (idempotent): re-run safe, shard-retry safe
INSERT INTO footfall_15m (bucket_id, branch_id, zone_id, bucket_start, entries, unique_tracks, avg_dwell_secs)
SELECT
  branch_id || '|' || zone_id || '|' || strftime(event_at, '%Y%m%d%H%M') AS bucket_id,
  branch_id, zone_id, time_bucket(INTERVAL '15 minutes', event_at) AS bucket_start,
  count(*) AS entries,
  count(DISTINCT track_id) AS unique_tracks,
  avg(dwell_secs) AS avg_dwell_secs
FROM events e JOIN tracks t USING (track_id)
WHERE cls = 'person' AND branch_id = 'lon-soho'
GROUP BY 1, 2, 3, 4
ON CONFLICT (bucket_id) DO UPDATE SET
  entries = EXCLUDED.entries,
  unique_tracks = EXCLUDED.unique_tracks,
  avg_dwell_secs = EXCLUDED.avg_dwell_secs;

Conversion is the same shape with one more join: door entries against till transactions (pull your POS into a transactions table on the same bucket, or join it live over the pg wire — see Connect your tools). The gap is the number the chain never had.

The chain — 15 branches, 7 countries, one consolidated HQ layer

Everything so far is one store. A chain owner does not run one store, and the moment you have fifteen of them in three currencies the interesting questions move up a level: which branch is the problem, and by how much, against what benchmark. That's the HQ layer, and it's four more tables on the same bucket.

The master data is the chain footprint — countries (reporting currency + region group), regions, and fx_rates, keyed by currency pair ("EUR:GBP"), which is what turns fifteen local P&Ls into one number. branches grows the enterprise columns it needs to be comparable: country_code, region_id, currency, format (flagship / standard / kiosk), sqm, opened_at. Format matters — a kiosk has no seating zone, so Bristol, Milan and Austin run 4 zones and 2 cameras where a flagship runs 5 and 3. Comparing a kiosk's dwell to a flagship's without that column is how you get a wrong answer confidently.

Then three fact tables. kpi_branch_daily is the grain — one row per branch per day, keyed "<branch_id>|<date>", with revenue in the branch's own currency. kpi_rollup is the consolidation — keyed "<scope>|<scope_id>|<date>" for chain, country and region — and it is the only place FX is applied. branch_benchmark ranks branches over a trailing period, keyed "<branch_id>|<metric>|<period>".

Consolidation is one recompute, and like the footfall rollup it is idempotent by construction — natural keys plus ON CONFLICT … DO UPDATE SET col = EXCLUDED.col, so you can run it on a schedule, run it twice, or replay a corrected day, and the rows land once:

-- kpi_rollup, country scope: FX is applied HERE and only here (revenue_local * fx.rate).
-- Conversion is re-derived at the rollup grain — SUM(tx)/SUM(entries), never an average of averages.
INSERT INTO kpi_rollup (id, scope, scope_id, date, entries, unique_visitors,
                        transactions, revenue_gbp, conversion_pct)
SELECT 'country|' || b.country_code || '|' || strftime(k.date, '%Y-%m-%d'),
       'country', b.country_code, k.date,
       SUM(k.entries), SUM(k.unique_visitors), SUM(k.transactions),
       ROUND(SUM(k.revenue_local * fx.rate), 2),
       ROUND(SUM(k.transactions) * 100.0 / NULLIF(SUM(k.entries), 0), 2)
FROM kpi_branch_daily k
JOIN branches b  ON b.branch_id = k.branch_id
JOIN fx_rates fx ON fx.pair = k.currency || ':GBP'
GROUP BY b.country_code, k.date
ON CONFLICT (id) DO UPDATE SET
  entries = EXCLUDED.entries, unique_visitors = EXCLUDED.unique_visitors,
  transactions = EXCLUDED.transactions, revenue_gbp = EXCLUDED.revenue_gbp,
  conversion_pct = EXCLUDED.conversion_pct;
 
-- the number the owner opens the dashboard for
SELECT SUM(entries), SUM(transactions), SUM(revenue_gbp),
       ROUND(SUM(transactions) * 100.0 / NULLIF(SUM(entries), 0), 2) AS conversion_pct
FROM kpi_rollup WHERE scope = 'chain';
-- 494868 | 155041 | 5505309.98 | 31.33

Fifty-six days of trading, consolidated to GBP. £5,505,309.98 on 494,868 door entries and 155,041 transactions — a 31.33% blended conversion. Split by country:

The benchmark table is the same idea, one statement per metric, with the ranking done in SQL — ROW_NUMBER() OVER (ORDER BY value DESC) for rank and PERCENT_RANK() for the percentile band the outlier view reads. Note "rank" is quoted: it's a reserved word.

SELECT bb.rank, b.name, b.country_code, b.format, bb.value AS revenue_gbp, bb.pctile
FROM branch_benchmark bb
JOIN branches b ON b.branch_id = bb.branch_id
WHERE bb.metric = 'revenue_gbp' AND bb.period = 'last_56d'
ORDER BY bb.rank LIMIT 5;
--  1 | NYC Midtown  | US | flagship | 1162038.59 | 100.00
--  2 | London Soho  | GB | flagship |  900450.93 |  92.86
--  3 | Paris        | FR | flagship |  693469.52 |  85.71
--  4 | San Francisco| US | standard |  388138.62 |  78.57
--  5 | NYC SoHo     | US | standard |  378185.62 |  71.43

A 27× revenue spread and a 2.3× conversion spread across one brand, one menu — and now it's one query against one bucket rather than fifteen exports reconciled in a spreadsheet. The floor-level data is right there next to it: NYC Midtown's 63 door entries and 14 tracklets in the sample window against Milan's 14 and 4, so "why does Bristol convert at 17.5%" drills straight down into that branch's dwell, queue and journey rows without leaving the bucket or changing tools.

Segmentation and returning visitors — the vector pillar

The appearance vector does two jobs at once, and neither needs a face. Cosine KNN over appearance groups shoppers by look-and-carry (a segment), and a very close match across visits is a returning-looking visitor — a soft, anonymous re-ID:

-- appearance KNN: returning-looking visitor + segment neighbours (no face involved)
SELECT a2.track_id, a1.embedding <=> a2.embedding AS cosine_distance
FROM appearance a1, appearance a2
WHERE a1.track_id = 't001' AND a2.track_id <> a1.track_id
ORDER BY cosine_distance
LIMIT 5;
-- t002          -> 0.0446   (returning-looking — same visitor, later pass)
-- t-sanfran-001 -> 0.1049   (same segment, different person)
-- t-sanfran-002 -> 0.1456
-- t-nyc-soho-005 -> 0.1473
-- t-dublin-001  -> 0.1670

That 0.045-vs-0.10+ spread is what lets you report "X% of this morning's cups were returning-looking regulars" across a whole chain — a loyalty signal with zero identity stored. When you do want named loyalty, that's the opt-in path below, and it's a different, consented table.

Be clear about what the demo data is. The image path is live and embed_appearance in detection.py uses it — a person crop in, a 2048-dim vector out. But the 105 vectors in this bucket were embedded from written appearance descriptors ("man in a navy hoodie, blue jeans, black backpack"), not from camera crops: there are no cameras attached to a demo bucket, and the dataset has to be reproducible by anyone who clones the package. jina embeds text and images into the same space, so the distances behave the same way and the queries are identical — but do not read the numbers above as "these came from pixels". Point DodilBackend at real crops and nothing in the schema, the query, or the index changes.

Index the vector column — an unindexed VECTOR is an exact scan

At 105 rows a brute-force KNN is instant and at a chain's real volume it is not. Create the index on the typed path (data sql / data_table_query); it builds and backfills the existing rows:

CREATE INDEX ON appearance (embedding);
-- -> __idx_appearance_embedding, backfilled 105 rows

Two things worth knowing before you reach for the pgvector syntax you already know. The pg wire silently no-ops the USING hnsw (embedding vector_cosine_ops) form — it returns success and you get no index, so issue the plain CREATE INDEX ON on the typed path and you'll know it worked. And re-running it is not idempotent: a second create is refused with '__idx_appearance_embedding' already holds 1 data file(s) — refusing to re-adopt over live data (drop the index first). That's a guard rail, not a bug — but it means index creation belongs in your provisioning step, not in a handler that runs on every request.

The journey — the graph pillar

zone_transitions records each move a track makes (entrance → counter → seating). graph_khop needs integer node keys and an integer-literal start node, so routes.py materialises an integer-keyed projection — zone_nodes (one zid per zone, ROW_NUMBER() assigned chain-wide so two branches can never collide) and zone_edges (this branch's transitions, aggregated with a count) — then creates the graph and walks it. Both writes are ON CONFLICT … DO UPDATE, and each is committed before the next reads it: DataK3 has no read-your-writes inside an open transaction.

-- the graph is a snapshot, so recreate it when the edges change
CREATE GRAPH journeys_lon_soho
  NODES (zone_nodes KEY zid) EDGES (zone_edges SRC src DST dst);
 
-- graph_khop projects `node` + `hop_distance` and only resolves as a TOP-LEVEL SELECT
-- with an integer-literal start node (35 = this branch's entrance zid).
SELECT k.hop_distance AS hop, n.name AS zone
FROM graph_khop('journeys_lon_soho', 35, 5, 'out') k
JOIN zone_nodes n ON n.zid = k.node
ORDER BY k.hop_distance, n.name;
--  1 | Order Counter
--  1 | Queue Rail
--  2 | Exit
--  2 | Seating

A branch with nothing to traverse is an empty result, not an error — a kiosk has no seating zone, so asking for a journey starting there returns 200 with an empty list rather than a 404 the dashboard renders as a broken page. That's the kind of detail the 15-branch chain forces you to get right and a single-store demo never does.

Loss prevention and safety — security on the same stream

Security is not a separate product; it's rules over the same events, written to security_events. After-hours a person detection is an intrusion; a bag that persists with no nearby person is unattended; a spill class is a safety alert; a person-on-floor is a possible fall. Staff get the alert in real time; the feed is queryable and auditable:

SELECT event_at, kind, severity, status
FROM security_events
WHERE branch_id = 'lon-soho' AND status <> 'closed'
ORDER BY CASE severity WHEN 'high' THEN 0 WHEN 'medium' THEN 1 ELSE 2 END, event_at DESC;

Consent & jurisdiction — identity is off by default

This is the part that keeps the chain out of trouble. Everything above is anonymous — person detection and appearance vectors, no face templates. Recognising a named returning customer means storing a face template, which is special-category biometric data (GDPR Art 9), so it is opt-in, consent-gated, and off by default.

The rules live in jurisdiction_policy, one row per region, read by the enrolment flow — so a branch in Illinois shows a BIPA-grade written-consent screen while a branch elsewhere shows the lighter notice its law allows, from one codebase:

# jurisdiction_policy — app-configurable consent, seeded per deployment
[
 {"jurisdiction_code":"EU",     "consent_mode":"explicit_optin",   "matching_scope":"enrolment_only", "retention_days":30},
 {"jurisdiction_code":"UK",     "consent_mode":"explicit_optin",   "matching_scope":"enrolment_only", "retention_days":30},
 {"jurisdiction_code":"US-IL",  "consent_mode":"written_optin",    "matching_scope":"enrolment_only", "retention_days":14, "erasure_sla_hours":48},
 {"jurisdiction_code":"US-GEN", "consent_mode":"notice_and_choice","matching_scope":"enrolment_only", "retention_days":90},
 {"jurisdiction_code":"ROW",    "consent_mode":"anonymous_only",   "matching_scope":"disabled",       "retention_days":7}
]

Enrolment is the only path that ever writes a face_templates row, and it's gated three ways: the caller needs the identity:enrol permission, the branch's jurisdiction_policy must permit matching, and a consent_ref is mandatory. Matching, when on, runs only against the opt-in gallery — never the general public. Withdraw consent and the template is deleted within the policy's erasure SLA. Across every preset, three things always hold: visible notice, a working erasure path, and enrolment-scoped matching. There is no covert "identify everyone" mode — the code can't express one.

Before you enable identity in any region: a signed DPIA and an EU AI Act review with your data-protection counsel, plus in-store signage. The jurisdiction_policy row records the decision; it does not replace the legal review. Ship the whole system anonymous first; turn identity on per deployment, deliberately.

Routes — the FastAPI app

routes.py fronts all of it — a plain SQLAlchemy 2.0 + FastAPI app (the ORM tabs above are literally its models.py). The routes fall into three planes: the edge writes, the projection jobs that turn raw events into answers, and the reads the dashboard lives on.

  EDGE (per branch)          PROJECTION JOBS                    READS (dashboard)
 ┌──────────────────┐   ┌─────────────────────────┐   ┌──────────────────────────────┐
 │ POST /events     │   │ POST /rollup/footfall   │   │ GET /branches/{id}/footfall  │
 │ POST /tracks     ├──▶│  events ─▶ footfall_15m ├──▶│ GET /branches/{id}/dwell     │
 │ POST /transitions│   │                         │   │ GET /branches/{id}/queue     │
 │ POST /appearance │   │ POST /rollup/visits     │   │ GET /branches/{id}/visits    │
 └──────────────────┘   │  tracklets ─▶ visits    ├──▶│ GET /branches/{id}/returning │
                        │  (stitch + calibrate)   │   │ GET /segments                │
   raw frames stay      │                         │   │ POST /appearance/similar     │
   in the store —       │ POST /rollup/journeys   │   │ POST /appearance/search      │
   only anonymised      │  transitions ─▶ zone    ├──▶│ GET /branches/{id}/journeys  │
   events leave         │  graph (atomic clear+   │   │                              │
                        │  rebuild per scope)     │   │ GET /branches/{id}/security  │
                        │                         │   │                              │
                        │ POST /rollup/consolidate│   │ GET /chain/summary·countries │
                        │  daily KPIs ─▶ chain +  ├──▶│  ·league·trends·outliers     │
                        │  country + region (FX)  │   │ GET /branches/{id}/kpis      │
                        └─────────────────────────┘   └──────────────────────────────┘

The rule the diagram encodes: readers never trigger work. Every GET is a flat read of a projection a job already materialised — that's the difference between the 1.3s journeys read and the 9.3s it cost when projection lived on the read path.

Writes go through one idempotent db.upsert (INSERT … ON CONFLICT (pk) DO UPDATE); each projection phase re-derives in a transaction after the previous phase commits, because DataK3 stages the write-log until commit and a SELECT will not see rows its own open transaction just inserted. The engine is lazy, so app.openapi() builds with no credentials — which is what lets CI generate the front-end's typed client without a bucket.

Auth — the app ships no auth code

A store manager opening this dashboard is a business user, not a DODIL org principal: they must never be a principal on the bucket. On DODIL that separation is configuration. Attach a dodil-appid pool to the Ignite app (user_pool: retail-vision in the deploy manifest, below) and the per-cluster gateway runs the entire browser login — PKCE S256 against the pool issuer, an AEAD-sealed host-only session cookie, single-flight refresh, JWT verification at the edge — then injects the verified identity as X-Dodil-User (with X-Dodil-User-Jwt carrying the expanded permissions claim, and X-Dodil-Auth-Source telling you whether the caller was a pool user or a platform principal). Any inbound copy of those headers is stripped first, so they can't be forged.

auth.py therefore does no signature checking at all — it reads the header the gateway already verified, and POST /members/enrol gates on the identity:enrol permission from the JWT. The app's own service account stays the only thing holding a DataK3 credential; the browser holds nothing but the gateway's cookie.

One deployment detail that produces a healthy app with a broken login if you miss it: the pool's redirect_uris must contain https://<your-app-fqdn>/.dodil/auth/callback. The full picture — including the case where you are not behind the gateway and must verify the pool JWT yourself with iss and aud mandatory — is in Auth is config, not code.

UI — the console in the same image

The package ships a web/ SPA (Vite + React + Tailwind + shadcn/ui + TanStack Query/Table) talking to the routes through a generated OpenAPI client, never to DataK3. It opens on the chain view — KPI tiles, revenue by country, a trend chart, the sortable league table and the outlier band — and every row drills into a branch view: footfall / dwell / queue, appearance segments, the journey flow diagram, the security feed and that branch's jurisdiction policy. Dropdowns switch branch and metric; there is no token in the browser, only the gateway's session cookie, which is why the SPA and the API are built into one image on one origin (a host-only cookie is not sent across two FQDNs).

Deploy — push, and it ships

The supported cycle is git-driven end to end: git push → the DODIL forge runs your workflow → an image in the DODIL registry → .dodil/deploy.yaml → a live Ignite app. No docker build on your laptop, no image reference to paste. The full walkthrough is Ship a DODIL app; here is what this app needs.

The build is a normal GitHub-Actions workflow — the forge executes it with act, and the runner injects the registry credentials, so there is nothing to configure:

# .github/workflows/ci.yml
name: ci
on: [push]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: docker/login-action@v3
        with:
          registry: ${{ secrets.DODIL_REGISTRY_URL }}
          username: ci
          password: ${{ secrets.DODIL_REGISTRY_TOKEN }}
      - uses: docker/build-push-action@v6
        with:
          context: .
          push: true
          tags: |
            ${{ secrets.DODIL_REGISTRY_URL }}/ihdiash/retail-vision:${{ github.sha }}
            ${{ secrets.DODIL_REGISTRY_URL }}/ihdiash/retail-vision:latest
          # provenance — the runner injects DODIL_GIT_*; repo_id is the join key
          annotations: |
            org.opencontainers.image.source=${{ env.DODIL_GIT_REPO_URL }}
            org.opencontainers.image.revision=${{ env.DODIL_GIT_SHA }}
            io.dodil.git.repo_id=${{ env.DODIL_GIT_REPO_ID }}

The CD runner applies the manifest once the checks pass. This is the whole deployment — resources, scaling, identity and configuration in one file:

# .dodil/deploy.yaml   — templates available: {registry} {org} {repo} {sha} {branch}
version: 1
create_if_missing: true
 
apps:
  - name: retail-vision
    image: "{registry}/ihdiash/retail-vision:{sha}"
    port: 8080
    resources:
      tier: medium
    scaling:
      max_replicas: 4
    group: retail
    # attach the pool -> the gateway does PKCE + the sealed session cookie + X-Dodil-User.
    # The pool's redirect_uris must include
    #   https://retail-vision-ihdiash-8080.ignite.dodil.cloud/.dodil/auth/callback
    user_pool: retail-vision
    # private => an end-user login is REQUIRED before a request ever reaches the app
    public_invoke: false
    # A declared env REPLACES the app's env map on every deploy, so every runtime variable
    # lives here. The credential is a REFERENCE: the CI runner resolves it from the repo's
    # secret store at deploy time and masks it in the logs. The value never exists in git.
    env:
      BUCKET: retailvis-core01
      WEB_DIST: ./web_dist
      DODIL_SERVICE_ACCOUNT_ID: cli-retail-vision-app
      DODIL_SERVICE_ACCOUNT_SECRET: "${{ secrets.RETAIL_SA }}"
    health:
      path: /healthz
 
when:
  branches: [main]
  require_checks: all

Three things that are easy to get wrong:

• The image must declare a numeric non-root user. Ignite admits pods with runAsNonRoot, and the kubelet cannot resolve a username from the image config — so USER appuser fails admission after a completely successful image pull, with container has runAsNonRoot and image will run as root. Use RUN useradd --uid 10001 … && chown -R 10001:10001 /app then USER 10001. Port 8080 is unprivileged, so nothing else changes.
• Same-org images need no registry secret (Ignite v1.0.5). Pulling your own org's image just works; you only create a registry secret for an external registry.
• Auth is config, not code. user_pool plus public_invoke: false is the entire login story. There is no library to add and no callback handler to write — only the pool's redirect_uris to allowlist.

Watch it land, then open it:

dodil ignite app get retail-vision
# public_urls: https://retail-vision-ihdiash-8080.ignite.dodil.cloud
#   (the shape is always $APP-$ORG-$PORT.ignite.dodil.cloud — not $APP.ignite.dodil.cloud)
 
dodil ignite app logs retail-vision --tail 20
# INFO: "GET /healthz HTTP/1.1" 200 OK

Get the code

The package — models.py, routes.py, detection.py (the pluggable detector), edge/agent.py, the web/ front-end, plus db.py, sa_token.py, auth.py, the seed scripts, .env.example and a README — is a download:

/code/retail-vision-core — .env-driven, runs against any bucket by editing the defaults. It ships BACKEND=synthetic so you can exercise the entire downstream — ingest, rollup, KNN, journey graph, security — offline with no token and no network; set BACKEND=dodil and DodilBackend calls the four live perception models with no change to any table, route or query.

Test

Everything on this page ran live against DataK3 and DODIL Models on 2026-09-05, bucket retailvis-core01 (uk-lon-1, org IHDIASH); the numbers inline are the real returned values.

export BUCKET=retailvis-core01
 
# 1) the perception models — three different image encodings, all live
#    gdino: `input` is a data: URI string  (bare base64 -> detections:[] with NO error)
#    scrfd: `input` is {type:image, image:<RAW base64>}  (a data: URI -> base64 decode failure)
#    jina / arcface: OpenAI content objects -> 2048-dim / 512-dim
#    verified: 12 persons + 3 bags + 1 cup (gdino), 14 faces (scrfd) on one street photo
 
# 2) the chain consolidates, and consolidating twice changes nothing
dodil data sql -b "$BUCKET" \
  "SELECT SUM(entries), SUM(transactions), SUM(revenue_gbp),
          ROUND(SUM(transactions)*100.0/NULLIF(SUM(entries),0),2)
   FROM kpi_rollup WHERE scope='chain'"
#  494868 | 155041 | 5505309.98 | 31.33
 
dodil data sql -b "$BUCKET" "SELECT scope, COUNT(*) FROM kpi_rollup GROUP BY scope ORDER BY scope"
#  chain 56 | country 392 | region 728        (1176 rows total)
dodil data sql -b "$BUCKET" "SELECT COUNT(*) FROM branch_benchmark"      # 45 = 15 branches x 3 metrics
dodil data sql -b "$BUCKET" "SELECT COUNT(*) FROM kpi_branch_daily"      # 840 = 15 branches x 56 days
 
# 3) the league table and its spread
dodil data sql -b "$BUCKET" \
  "SELECT bb.rank, b.name, bb.value FROM branch_benchmark bb
     JOIN branches b ON b.branch_id=bb.branch_id
   WHERE bb.metric='revenue_gbp' AND bb.period='last_56d' ORDER BY bb.rank"
#  1 NYC Midtown 1162038.59 · 2 London Soho 900450.93 · 3 Paris 693469.52
#  13 Austin 49811.02 · 14 Milan 48756.72 · 15 Bristol 42720.76
#  conversion_pct over the same period: 39.48 (NYC Midtown) -> 17.52 (Bristol)
 
# 4) format drives topology — a kiosk has no seating zone
dodil data sql -b "$BUCKET" \
  "SELECT b.format, COUNT(DISTINCT z.zone_id) zones, COUNT(DISTINCT c.camera_id) cams
     FROM branches b LEFT JOIN zones z ON z.branch_id=b.branch_id
     LEFT JOIN cameras c ON c.branch_id=b.branch_id
   WHERE b.branch_id='bristol' GROUP BY b.format"
#  kiosk | 4 | 2          (flagship/standard: 5 zones, 3 cameras)
 
# 5) the SQL pillar — the per-branch footfall rollup is idempotent
dodil data sql -b "$BUCKET" \
  "SELECT COUNT(*) buckets, SUM(entries) FROM footfall_15m WHERE branch_id='lon-soho'"
#  9 | 41           (entrance 09:00 = 10 entries / 10 unique / 570s avg dwell)
 
# 6) the vector pillar — 105 appearance vectors, indexed, KNN by cosine
dodil data sql -b "$BUCKET" "SELECT COUNT(*) FROM appearance"            # 105
dodil data sql -b "$BUCKET" "CREATE INDEX ON appearance (embedding)"
#  first run -> __idx_appearance_embedding, backfilled 105 rows
#  second run -> refused: "already holds 1 data file(s) … (drop the index first)"
dodil data pg -b "$BUCKET" \
  "SELECT a2.track_id, a1.embedding <=> a2.embedding d FROM appearance a1, appearance a2
   WHERE a1.track_id='t001' AND a2.track_id<>a1.track_id ORDER BY d LIMIT 3"
#  t002 0.0446 (returning-looking) · t-sanfran-001 0.1049 · t-sanfran-002 0.1456
 
# 7) the graph pillar — journey traversal from the entrance zone
dodil data sql -b "$BUCKET" \
  "SELECT k.hop_distance, n.name FROM graph_khop('journeys_lon_soho', 35, 5, 'out') k
     JOIN zone_nodes n ON n.zid=k.node ORDER BY k.hop_distance, n.name"
#  1 Order Counter · 1 Queue Rail · 2 Exit · 2 Seating
 
# 8) security rides the same stream
dodil data sql -b "$BUCKET" \
  "SELECT kind, severity, status FROM security_events WHERE branch_id='lon-soho' ORDER BY event_at"
#  intrusion high open · unattended_bag medium open · spill low ack
 
# 9) drop-in clients — same bucket, your own psql / cypher-shell / pgvector driver
dodil data connect "$BUCKET"

Deploy was proven on the same repo: ci/build green in 12m48s, image pushed to registry.dodil.io/ihdiash/retail-vision, pod pull in 419ms, GET /healthz → 200, and an unauthenticated request answered 401 {"error":"end-user login required","login_url":"…"} before reaching the app — the gateway doing the job the app has no code for.

One-shot

With the DODIL MCP connected, paste this to scaffold the whole system at once:

Build a retail camera-analytics core on DataK3 — one bucket, three pillars, anonymous by default. Confirm each step.
 
1. Create bucket `retailvis-core01` (uk-lon-1), then the merge-keyed tables (non-key columns nullable):
   TOPOLOGY  branches (key branch_id): name, country_code, region_id, currency, tz, format, sqm(int),
             opened_at(date), jurisdiction_code; zones (key zone_id): branch_id, name, kind
             (entrance|counter|queue|seating|exit); cameras (key camera_id): branch_id, zone_id, label, rtsp_ref.
   SPINE     events (key event_id): branch_id, camera_id, zone_id, event_at(timestamp), cls, confidence(double),
             bbox(json), track_id; tracks (key track_id): entered_at, exited_at, dwell_secs(long), zone_path(json),
             member_id; appearance (key track_id): descriptor, embedding VECTOR(2048);
             zone_transitions (key transition_id): track_id, from_zone, to_zone, moved_at(timestamp);
             zone_nodes (key zid int): zone_id, name; zone_edges (key edge_id): src(int), dst(int), cnt(int).
   ANALYTICS footfall_15m (key bucket_id = branch|zone|YYYYMMDDHHMM): branch_id, zone_id, bucket_start, entries,
             unique_tracks, avg_dwell_secs.
   ENTERPRISE countries (key country_code): name, currency, region_group; regions (key region_id): country_code,
             name; fx_rates (key pair, e.g. EUR:GBP): rate DECIMAL(18,6), as_of(date);
             kpi_branch_daily (key id = branch|date): branch_id, date, entries, unique_visitors, avg_dwell_secs,
             transactions, conversion_pct DECIMAL, revenue_local DECIMAL, currency, security_incidents;
             kpi_rollup (key id = scope|scope_id|date): scope, scope_id, date, entries, unique_visitors,
             transactions, revenue_gbp DECIMAL, conversion_pct DECIMAL;
             branch_benchmark (key id = branch|metric|period): branch_id, metric, period, value DECIMAL,
             rank(int), pctile DECIMAL.
   SECURITY  security_events (key sec_id): branch_id, zone_id, event_at, kind, severity, status, detail(json).
   POLICY    sampling_policy (key policy_id): camera_id, fps(double), motion_gate(boolean), models(json),
             active_hours(json), budget_calls_per_min(int);
             jurisdiction_policy (key jurisdiction_code): consent_mode, matching_scope, retention_days(int),
             erasure_sla_hours(int).
   IDENTITY (create, leave EMPTY) loyalty_members (key member_id): consent_ref, enrolled_at, jurisdiction_code;
             face_templates (key member_id): embedding VECTOR(512), consent_ref, expires_at.
2. Seed 15 branches across GB/FR/DE/NL/IE/IT/US with format flagship|standard|kiosk (kiosks get 4 zones and
   2 cameras — no seating), their countries/regions, and fx_rates EUR:GBP, USD:GBP, GBP:GBP=1. Seed 56 days of
   kpi_branch_daily per branch (840 rows) with revenue in each branch's LOCAL currency.
3. Perception (all live on https://api.dodil.io/v1, bearer = a service-account client_credentials token with
   ignite.model-user). MIND THE ENCODINGS: mm-gdino-large POST /infer with input = a data: URI STRING plus a
   labels array (bare base64 silently returns zero detections) -> boxes in ABSOLUTE pixels; scrfd-34g POST /infer
   with input = {"type":"image","image":"<RAW base64>"} (a data: URI fails to decode) -> boxes NORMALISED 0..1 plus
   5 landmarks; jina-embeddings-v4 and arcface-r100 POST /embeddings with input = [{"type":"image_url",
   "image_url":{"url":"<data URI>"}}] -> 2048-dim and 512-dim. Image models cold-start: retry with backoff.
4. SQL pillar: recompute footfall_15m per branch from events JOIN tracks with
   INSERT ... ON CONFLICT (bucket_id) DO UPDATE SET col = EXCLUDED.col. Re-run and prove it is identical.
5. HQ layer: roll kpi_branch_daily up into kpi_rollup for scope chain, country and region per day, applying
   fx_rates to get revenue_gbp, then rank branches into branch_benchmark for entries, revenue_gbp and
   conversion_pct with ROW_NUMBER and PERCENT_RANK. Both idempotent via ON CONFLICT DO UPDATE. Re-run and prove
   the rows are unchanged. Report chain totals and the top and bottom 3 of the league table.
6. Vector pillar: embed one appearance vector per track into appearance.embedding, then
   CREATE INDEX ON appearance (embedding) on the typed path (data sql). KNN with the pgvector cosine operator to
   find returning-looking visitors. Note that a second CREATE INDEX is refused until you drop the index.
7. Graph pillar: project zones into zone_nodes with a CHAIN-WIDE ROW_NUMBER (never per branch, or branches
   collide), aggregate that branch's zone_transitions into zone_edges, commit, then CREATE GRAPH over them and
   traverse with graph_khop('<graph>', <integer-literal start zid>, 5, 'out') as a TOP-LEVEL SELECT, joining
   k.node back to zone_nodes.zid.
8. Keep it anonymous: seed jurisdiction_policy per region, leave loyalty_members and face_templates EMPTY, and
   make enrolment the only path that can write a face template.

Ship it

Point the edge agent at a branch's cameras (start with a replayed public pedestrian dataset over a local RTSP server to test the whole path legally), seed one jurisdiction_policy and one sampling_policy per camera, and the anonymous analytics light up immediately — footfall, dwell, queue, segments, journeys, and the security feed. Then git push: the forge builds the image, .dodil/deploy.yaml deploys it, the gateway puts a login in front of it, and the chain view is live on EMEA hardware. Turn identity on later, per region, once the DPIA is signed. The frames never leave the store; the insight does.