Data model

Metrica is modeled around a strict tenant boundary and a single stream of CV output. The layers are: users (accounts) own stores, stores have cameras, cameras produce events, and events are later shaped into sessions and metrics.

USER ──< STORE ──< CAMERA ──< EVENT ──(worker builds)──▶ SESSION   (Phase 1.5)
                                 └── Phase 2: TRACK · DAILY_METRICS · HOURLY_METRICS

What exists today

The backend materializes four core tables: users, stores, cameras, events. Everything downstream of events — sessions and metrics — is derived on the fly from the event stream (see the architecture page), not stored. The camera table also carries line-calibration, snapshot, and heartbeat columns that go beyond the logical ERD, because the worker and admin app need them.

Entity	Status	Purpose
users	Live	Account / tenant boundary; profile mirror of the Supabase identity
stores	Live	A retail shop or mall location
cameras	Live	A CCTV/RTSP source + its counting-line calibration
events	Live	IN/OUT crossings — the CV output that matters
session	Deferred (Phase 1.5)	A full visit → dwell time
track	Deferred (Phase 2)	Raw CV identity, for re-ID / debugging
daily_metrics / hourly_metrics	Deferred (Phase 2)	Precomputed rollups for query speed

Entities

users — tenant / account

A store owner who logs in. The tenant boundary: all data is reached through the user’s stores. Authentication is Supabase Auth; this table is a thin profile mirror in Neon.

Column	Type	Notes
id	UUID, PK	Our immutable identifier. Every FK points here
auth_id	UUID, unique, nullable	The Supabase JWT sub. Kept separate from id so a provider/project migration never rewrites id or its FKs. Nullable so an invited owner can be pre-created before first login
email	varchar(255), unique, not null	Login email
first_name / last_name	varchar(255), nullable	Split from the JWT full_name on first login
avatar_url	nullable	From auth metadata
created_at / updated_at	timestamptz	updated_at auto-updates
deleted_at	timestamptz, nullable	Soft delete

id ≠ auth_id

id is our key and is immutable — it starts equal to the auth subject for brand new users only, and diverges the moment a user is migrated to a new Supabase project. Authorization and lookups always go through the User row, never the raw JWT sub.

stores

A retail shop or mall location; the unit of tenancy.

Column	Type	Notes
id	UUID, PK
owner_id	UUID, FK → users.id, indexed, nullable	Tenancy. Nullable until an owner is assigned
name	varchar
location	varchar, nullable
timezone	varchar, default UTC	Timestamps are stored UTC and converted to this tz for daily/hourly rollups and peak_hour
created_at	timestamptz

cameras

A CCTV / RTSP stream source and its counting-line calibration.

Column	Type	Notes
id	UUID, PK
store_id	UUID, FK → stores.id, indexed
name	varchar
rtsp_url	varchar	Stream source
position_type	varchar, default entrance	entrance / inside / unknown — matters for analytics
is_active	bool, default true
is_counting_line	bool, default false	Opt-in: only cameras with a real entrance line contribute to footfall, so interior cameras never inflate the count
line_x1, line_y1, line_x2, line_y2	float, nullable	Counting line, stored as normalized 0..1 fractions of the frame (resolution-independent). The worker denormalizes with the frame size on its first frame
line_invert	bool, default false	Flips IN/OUT direction
snapshot	bytea, nullable, deferred	Latest JPEG from the worker, used to draw the line on. Deferred so ordinary camera queries never drag the blob
snapshot_at	timestamptz, nullable	When the snapshot arrived
last_seen_at	timestamptz, nullable	Last worker heartbeat — camera health
created_at	timestamptz

events — the critical table

Movement across the virtual line: the CV output that everything else derives from.

Column	Type	Notes
id	UUID, PK
store_id	UUID, FK → stores.id, indexed
camera_id	UUID, FK → cameras.id, indexed
tracker_id	int, nullable	Ephemeral ByteTrack id, scoped to camera + run. Debug only, NOT a foreign key
event_type	varchar (IN / OUT)	Direction of crossing
timestamp	timestamptz, indexed, UTC	When the crossing happened
confidence	float, nullable	Detection confidence

session — a visit lifecycle (Phase 1.5, not yet materialized)

Represents a full visit → enables dwell time. Created on an IN event, closed on the matching OUT.

Column	Type	Notes
id	UUID, PK
store_id	UUID, FK
camera_id	UUID, FK
tracker_id	int, nullable	Which visit, scoped to camera + run
entry_time	timestamptz, UTC
exit_time	timestamptz, UTC, nullable	Null while still inside
dwell_time_seconds	int

track — raw CV identity (Phase 2, not yet materialized)

A detected person across frames. Not a user identity — a temporary CV identity for re-ID / debugging. Would get a UUID PK; the raw tracker_id int is only a plain attribute (it resets on restart and repeats across cameras, so it can never be a key).

daily_metrics / hourly_metrics — rollups (Phase 2, not yet materialized)

Precomputed analytics for dashboard speed. Until queries actually get slow, these are computed on the fly from events.

daily_metrics		hourly_metrics
id (UUID, PK)	total_visitors	id (UUID, PK)	visitors_count
store_id (FK)	total_entries	store_id	entries_count
date	avg_dwell_time	date, hour	avg_dwell_time
	peak_hour, created_at

Relationships

From	To	Cardinality
users	stores	1 → N (tenancy)
stores	cameras	1 → N
stores	events	1 → N
cameras	events	1 → N
stores	sessions (Phase 1.5)	1 → N
track (Phase 2)	events / sessions	1 → N / 1 → 1 (or 1 → N if re-entry allowed)
stores	metrics (Phase 2)	1 → N

Key design decisions

Track ≠ session

A track is a CV identity (temporary, unstable); a session is a business concept (a stable visit). Separating them keeps tracking errors and ID-switching from corrupting analytics.

Why the raw tracker id is never a key

The ByteTrack id is a small int that resets to 0 on restart and repeats across cameras — so it collides and can never be a foreign key. In the MVP it lives on events as a nullable debug column, meaningful only together with camera_id. IN/OUT counting does not depend on it. If a track table is added in Phase 2, it gets a UUID PK and everything references that.

Why we store events (not just aggregates)

Because sessions can be recomputed, debugging becomes possible, and analytics stay flexible. The CV layer produces events; the business layer builds sessions; the analytics layer builds metrics — that separation is what makes it a scalable SaaS.

The time rule

Store every timestamp in UTC. Convert to store.timezone only when rolling up daily/hourly metrics and peak_hour. Otherwise peak-hour analytics land in the wrong bucket.

Future extensions

Beyond Phase 2: staff detection (employee), AI insights (insight — LLM- generated summaries), and POS integration (sales → conversion rate).