Data Model

The 14 SQLite tables behind Live Product Insights: identity and events, commerce, configuration, and operations — plus data lifecycle and deletion paths.

The app stores everything in a single SQLite database accessed through Prisma. The schema is privacy-first by construction: no customer names, emails, or addresses exist in any table. Shopper identity is an anonymous, client-generated session UUID.

​

Tables at a glance

Identity and events

Table	Purpose
VisitorSession	One row per anonymous shopper session: device class, referrer host, UTM parameters, first/last seen
ActiveViewer	Live presence: one row per shop + product + session, refreshed by heartbeats with a last-heartbeat timestamp
ProductEvent	The raw event log: view, add-to-cart, and remove-from-cart events with variant, quantity, and traffic source
CartSessionMap	Cart token to session mapping — written on cart activity, consumed by order attribution

Commerce

Table	Purpose
OrderRecord	Orders from webhooks and catch-up sync. Line items are a JSON array with per-line price, discounts, refunds, and attribution flags; test and cancellation flags are stored per order
ProductCache	Catalog mirror synced from the Admin API: status, category, collections, total inventory, and a per-product counter-disabled flag
VariantCache	Variant mirror: inventory, price, and the inventory item id used to map inventory webhooks back to products

Configuration

Table	Purpose
ShopSettings	One row per store (the shop domain is the key): all merchant settings with safe defaults

Operations

Table	Purpose
DailyProductStat	Daily per-product rollups (views, sessions, cart events, orders, units, attributed orders, revenue) — the durable historical record that survives event pruning
RefundRecord	Refund events from the refunds/create webhook, applied per unit onto order line items
InsightRecord	Decision-support findings: type, severity, evidence, suggested action, period, dismissed state
ShopBilling	Per-store subscription state: plan, charge status, trial and cycle dates
ShopUsage	Per-store usage counters for plan quotas (events and storage this billing month)
Session	Shopify admin auth sessions (managed by the app library) — the only table with access tokens

​

Logical relations

The schema declares no database-level foreign keys. Every cross-table link is a string id, and most tables use composite natural keys scoped by shop (for example shop + product id + session id). Store isolation is enforced by every query filtering on the shop column, which is regression-tested.

​

SQLite specifics that matter to maintainers

• DateTimes are epoch-millisecond integers. Prisma stores SQLite datetimes as integer milliseconds, and the metrics code relies on it: time buckets are computed with integer division, and raw MAX() results are converted back to dates explicitly.
• Money is BigInt cents. Order totals, refunds, prices, and rollup revenue are stored as integer cents and surface as JavaScript BigInt.
• Raw SQL uses tagged templates only ($queryRaw), never string concatenation.
• SQLite serializes writes, not reads. The view-dedupe check-then-insert is wrapped in a per-key in-process promise chain to close the race; this is one of the single-instance assumptions documented in Security.
• Dev and production databases are separate files (dev.sqlite locally, /data/prod.sqlite on the deployment volume). Integration tests share the dev database and therefore run sequentially.

​

Data lifecycle

Data	Written by	Pruned
Product events, visitor sessions	Storefront ingest	Daily after the retention window (default 90 days, range 7–365)
Active viewer presence	Heartbeats	Expires within the presence window; stale rows swept continuously, hard backstop after 7 days
Orders and refunds	Webhooks + catch-up sync	Daily after the retention window
Daily rollups	Nightly rollup job	Kept — the historical record once raw events are pruned
Catalog caches	Sync + product/inventory webhooks	Kept; removed on product delete
Settings, billing state	Merchant actions, Shopify billing	Kept until uninstall or shop redaction

​

Deletion matrix

Trigger	Deletes	Keeps
Clear analytics data (merchant action in Settings)	Events, sessions, presence, cart map, orders, rollups, insights	Settings, catalog caches, auth session
App uninstall (app/uninstalled)	The auth session and all shop analytics data including settings	Nothing for that shop
shop/redact (mandatory privacy webhook)	Every shop-scoped row across all store tables	Nothing for that shop
customers/redact	Order records for the listed order ids	Everything else (no customer PII exists to redact)

Prune and deletion jobs run inside the scheduler — see Background jobs. The retention window is merchant-configurable under Settings.