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

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

```mermaid theme={null}
erDiagram
  ShopSettings ||--o{ VisitorSession : "shop"
  VisitorSession ||--o{ ProductEvent : "sessionId"
  VisitorSession ||--o{ ActiveViewer : "sessionId"
  VisitorSession ||--o{ CartSessionMap : "sessionId"
  ProductCache ||--o{ ProductEvent : "productId"
  ProductCache ||--o{ ActiveViewer : "productId"
  ProductCache ||--o{ VariantCache : "productId"
  CartSessionMap }o--|| OrderRecord : "cartToken (attribution)"
  OrderRecord ||--o{ RefundRecord : "orderId"
  ProductCache ||--o{ DailyProductStat : "productId"
  ProductCache ||--o{ InsightRecord : "productId (optional)"
  ShopBilling ||--|| ShopUsage : "shop (plan quotas)"
```

## 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](https://topten.mintlify.site/development/security.md).
* **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](https://topten.mintlify.site/architecture/background-jobs.md). The retention window is
merchant-configurable under [Settings](https://topten.mintlify.site/settings.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.