35 lines
982 B
SQL
35 lines
982 B
SQL
-- Email tracking opens table
|
|
CREATE TABLE IF NOT EXISTS opens (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
|
|
-- Encrypted tracking ID (from URL)
|
|
tracking_id TEXT NOT NULL,
|
|
|
|
-- Decrypted from pixel payload
|
|
recipient TEXT NOT NULL,
|
|
subject_hash TEXT NOT NULL,
|
|
sent_at TEXT NOT NULL,
|
|
|
|
-- Recorded on open
|
|
opened_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
ip TEXT,
|
|
user_agent TEXT,
|
|
|
|
-- Geolocation (from Cloudflare request.cf)
|
|
country TEXT,
|
|
region TEXT,
|
|
city TEXT,
|
|
timezone TEXT,
|
|
|
|
-- Bot detection
|
|
is_bot INTEGER NOT NULL DEFAULT 0,
|
|
bot_type TEXT
|
|
);
|
|
|
|
-- Indexes for common queries
|
|
CREATE INDEX IF NOT EXISTS idx_opens_tracking_id ON opens(tracking_id);
|
|
CREATE INDEX IF NOT EXISTS idx_opens_recipient ON opens(recipient);
|
|
CREATE INDEX IF NOT EXISTS idx_opens_sent_at ON opens(sent_at);
|
|
CREATE INDEX IF NOT EXISTS idx_opens_opened_at ON opens(opened_at);
|
|
CREATE INDEX IF NOT EXISTS idx_opens_recipient_subject ON opens(recipient, subject_hash, sent_at);
|