D1 Schema Reference

The D1 database stores real-time events and analytics at the edge. This document describes all tables.

Raw Event Tables

VisitorIdentity

Stores unique visitors identified by fingerprint.

CREATE TABLE VisitorIdentity (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  cookie_id TEXT,
  ip_addresses TEXT,         -- JSON array of seen IPs
  user_agents TEXT,          -- JSON array of seen UAs
  first_seen INTEGER NOT NULL,
  last_seen INTEGER NOT NULL,
  visit_count INTEGER DEFAULT 1,
  risk_score INTEGER DEFAULT 0
);

CREATE INDEX idx_visitor_shop ON VisitorIdentity(shop);
CREATE INDEX idx_visitor_fingerprint ON VisitorIdentity(fingerprint);

SessionSnapshot

Stores individual browsing sessions.

CREATE TABLE SessionSnapshot (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  visitor_id TEXT NOT NULL,
  started_at INTEGER NOT NULL,
  last_activity INTEGER NOT NULL,
  device_info TEXT,          -- JSON: browser, OS, device type
  page_count INTEGER DEFAULT 1,
  checkout_reached INTEGER DEFAULT 0,
  cart_value REAL,
  FOREIGN KEY (visitor_id) REFERENCES VisitorIdentity(id)
);

CREATE INDEX idx_session_shop ON SessionSnapshot(shop);
CREATE INDEX idx_session_visitor ON SessionSnapshot(visitor_id);
CREATE INDEX idx_session_started ON SessionSnapshot(started_at);

BotSignal

Stores bot detection events.

CREATE TABLE BotSignal (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  visitor_id TEXT NOT NULL,
  session_id TEXT NOT NULL,
  signal_type TEXT NOT NULL,   -- selenium, headless, honeypot, trap_link, behavioral
  confidence INTEGER NOT NULL,
  details TEXT,                -- JSON: specific detection details
  ip TEXT,
  page TEXT,
  user_agent TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX idx_bot_shop_date ON BotSignal(shop, created_at);
CREATE INDEX idx_bot_signal_type ON BotSignal(signal_type);

SpySignal

Stores spy tool detection events.

CREATE TABLE SpySignal (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  visitor_id TEXT NOT NULL,
  session_id TEXT NOT NULL,
  tool_name TEXT NOT NULL,     -- ppspy, koala, commerce_inspector, etc.
  detection_method TEXT,       -- global_object, dom_element, extension_resource
  ip TEXT,
  page TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX idx_spy_shop_date ON SpySignal(shop, created_at);

IPBlockingEvent

Stores IP/country blocking events.

CREATE TABLE IPBlockingEvent (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  ip TEXT NOT NULL,
  country TEXT,
  reason TEXT NOT NULL,        -- blocked_ip, blocked_cidr, blocked_country, vpn, datacenter
  page TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX idx_ipblock_shop_date ON IPBlockingEvent(shop, created_at);

ProtectionEvent

Stores content protection trigger events.

CREATE TABLE ProtectionEvent (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  session_id TEXT NOT NULL,
  visitor_id TEXT NOT NULL,
  event_type TEXT NOT NULL,    -- right_click_blocked, copy_blocked, etc.
  page TEXT,
  ip TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX idx_protection_shop_date ON ProtectionEvent(shop, created_at);

BehavioralSignal

Stores mouse/scroll behavioral analytics.

CREATE TABLE BehavioralSignal (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  visitor_id TEXT NOT NULL,
  session_id TEXT NOT NULL,
  signal_type TEXT NOT NULL,   -- linear_mouse, regular_scroll, etc.
  score REAL,
  details TEXT,                -- JSON: movement data
  created_at INTEGER NOT NULL
);

CREATE INDEX idx_behavioral_shop ON BehavioralSignal(shop);

FraudSignal

Stores fraud-related signals.

CREATE TABLE FraudSignal (
  id TEXT PRIMARY KEY,
  shop TEXT NOT NULL,
  visitor_id TEXT NOT NULL,
  session_id TEXT NOT NULL,
  signal_type TEXT NOT NULL,
  score INTEGER,
  details TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX idx_fraud_shop ON FraudSignal(shop);

Config Tables

MerchantConfig

Caches merchant protection configuration at the edge.

CREATE TABLE MerchantConfig (
  shop TEXT PRIMARY KEY,
  protections TEXT,            -- JSON: protection toggles
  bot_detection TEXT,          -- JSON: bot detection config
  spy_detection INTEGER DEFAULT 0,
  ip_blocking INTEGER DEFAULT 0,
  last_seen INTEGER,
  updated_at INTEGER
);

IPBlockingConfig

Caches IP blocking rules at the edge.

CREATE TABLE IPBlockingConfig (
  shop TEXT PRIMARY KEY,
  enabled INTEGER DEFAULT 0,
  blocked_ips TEXT,            -- JSON array
  blocked_cidrs TEXT,          -- JSON array
  blocked_countries TEXT,      -- JSON array
  allowed_ips TEXT,            -- JSON array (whitelist)
  block_vpn INTEGER DEFAULT 0,
  block_datacenter INTEGER DEFAULT 0,
  block_tor INTEGER DEFAULT 0,
  updated_at INTEGER
);

Aggregate Tables

DailyMetrics

Pre-aggregated daily metrics per shop.

CREATE TABLE DailyMetrics (
  shop TEXT NOT NULL,
  date TEXT NOT NULL,          -- YYYY-MM-DD
  sessions INTEGER DEFAULT 0,
  unique_visitors INTEGER DEFAULT 0,
  protection_events INTEGER DEFAULT 0,
  bot_events INTEGER DEFAULT 0,
  spy_events INTEGER DEFAULT 0,
  ip_blocking_events INTEGER DEFAULT 0,
  checkout_sessions INTEGER DEFAULT 0,
  PRIMARY KEY (shop, date)
);

CREATE INDEX idx_metrics_date ON DailyMetrics(date);

TopIPsDaily

Top threat IPs per shop per day.

CREATE TABLE TopIPsDaily (
  shop TEXT NOT NULL,
  date TEXT NOT NULL,
  ip TEXT NOT NULL,
  count INTEGER DEFAULT 1,
  PRIMARY KEY (shop, date, ip)
);

CREATE INDEX idx_topips_shop_date ON TopIPsDaily(shop, date);

TopPagesDaily

Most targeted pages per shop per day.

CREATE TABLE TopPagesDaily (
  shop TEXT NOT NULL,
  date TEXT NOT NULL,
  page TEXT NOT NULL,
  count INTEGER DEFAULT 1,
  PRIMARY KEY (shop, date, page)
);

CREATE INDEX idx_toppages_shop_date ON TopPagesDaily(shop, date);

TopCountriesDaily

Threat distribution by country.

CREATE TABLE TopCountriesDaily (
  shop TEXT NOT NULL,
  date TEXT NOT NULL,
  country TEXT NOT NULL,
  count INTEGER DEFAULT 1,
  PRIMARY KEY (shop, date, country)
);

CREATE INDEX idx_topcountries_shop_date ON TopCountriesDaily(shop, date);

DailyUniqueVisitors

Tracks unique visitors for billing.

CREATE TABLE DailyUniqueVisitors (
  shop TEXT NOT NULL,
  date TEXT NOT NULL,
  visitor_id TEXT NOT NULL,
  PRIMARY KEY (shop, date, visitor_id)
);

CREATE INDEX idx_uniquevisitors_shop ON DailyUniqueVisitors(shop);

Risk Profile Tables

VisitorRiskProfile

Aggregated risk scores per visitor.

CREATE TABLE VisitorRiskProfile (
  visitor_id TEXT PRIMARY KEY,
  risk_score INTEGER DEFAULT 0,
  signals TEXT,                -- JSON: array of signal types
  first_flagged INTEGER,
  last_flagged INTEGER,
  flag_count INTEGER DEFAULT 0
);

IPReputation

IP reputation data (VPN, datacenter, etc.).

CREATE TABLE IPReputation (
  ip TEXT PRIMARY KEY,
  is_vpn INTEGER DEFAULT 0,
  is_proxy INTEGER DEFAULT 0,
  is_datacenter INTEGER DEFAULT 0,
  is_tor INTEGER DEFAULT 0,
  country TEXT,
  asn TEXT,
  org TEXT,
  last_checked INTEGER
);

Data Retention

Table	Retention	Notes
Raw events (BotSignal, SpySignal, etc.)	30 days	Pruned via scheduled job
Aggregate tables (DailyMetrics, Top*)	90 days	Pruned via scheduled job
Config tables	Indefinite	Overwritten on update
DailyUniqueVisitors	Current billing period	Pruned after period ends