Data Model

PULSE database schema and data model

Data Model

PULSE uses a SQLite-based data model optimized for analytics queries.

Core Tables

events

Raw event data from all sources.

CREATE TABLE events (
  event_id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  event_type TEXT NOT NULL,
  timestamp_ms INTEGER NOT NULL,
  session_id TEXT NOT NULL,
  user_id TEXT,
  url TEXT,
  referrer TEXT,
  country TEXT,
  device_type TEXT,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (site_id) REFERENCES sites(id)
);

CREATE INDEX idx_events_site_timestamp
  ON events(site_id, timestamp DESC);
CREATE INDEX idx_events_session
  ON events(session_id);

Fields:

• event_id — Unique event identifier (UUID)
• site_id — Site that generated the event
• event_type — Type of event (page_view, click, etc.)
• timestamp_ms — Event timestamp in milliseconds
• session_id — Session identifier for grouping
• user_id — Optional user identifier
• url — Page URL if applicable
• referrer — Referrer URL
• country — ISO country code
• device_type — Device type (desktop, mobile, tablet)
• created_at — Server-side timestamp

sessions

Session tracking and user attributes.

CREATE TABLE sessions (
  session_id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  user_id TEXT,
  first_event_at INTEGER NOT NULL,
  last_event_at INTEGER NOT NULL,
  event_count INTEGER DEFAULT 1,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (site_id) REFERENCES sites(id)
);

users

User attributes and metadata.

CREATE TABLE users (
  user_id TEXT PRIMARY KEY,
  site_id TEXT,
  name TEXT,
  email TEXT,
  company TEXT,
  plan TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

cohorts

User cohort definitions.

CREATE TABLE cohorts (
  cohort_id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  criteria TEXT NOT NULL, -- JSON
  status TEXT DEFAULT 'active',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (site_id) REFERENCES sites(id)
);

Criteria JSON schema:

{
  "type": "and",
  "conditions": [
    {
      "property": "event_type",
      "operator": "equals",
      "value": "user_signup"
    },
    {
      "property": "country",
      "operator": "in",
      "value": ["US", "CA"]
    }
  ]
}

cohort_members

Cohort membership tracking.

CREATE TABLE cohort_members (
  member_id TEXT PRIMARY KEY,
  cohort_id TEXT NOT NULL,
  session_id TEXT,
  user_id TEXT,
  joined_at INTEGER NOT NULL,
  last_seen_at INTEGER NOT NULL,
  is_active BOOLEAN DEFAULT 1,
  FOREIGN KEY (cohort_id) REFERENCES cohorts(cohort_id)
);

CREATE INDEX idx_cohort_members_cohort
  ON cohort_members(cohort_id);

retention_snapshots

Pre-computed retention metrics.

CREATE TABLE cohort_retention_snapshots (
  snapshot_id TEXT PRIMARY KEY,
  cohort_id TEXT NOT NULL,
  site_id TEXT NOT NULL,
  week_number INTEGER NOT NULL,
  cohort_size INTEGER NOT NULL,
  returning_users INTEGER NOT NULL,
  retention_rate REAL NOT NULL,
  captured_at INTEGER NOT NULL,
  UNIQUE(cohort_id, week_number),
  FOREIGN KEY (cohort_id) REFERENCES cohorts(cohort_id)
);

Feature Tables

alert_rules

Alert rule configuration.

CREATE TABLE alert_rules (
  rule_id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  name TEXT NOT NULL,
  condition TEXT NOT NULL, -- JSON
  notification_type TEXT NOT NULL,
  notification_config TEXT NOT NULL, -- JSON
  status TEXT DEFAULT 'active',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

webhook_endpoints

Webhook configuration for external integrations.

CREATE TABLE webhook_endpoints (
  id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  url TEXT NOT NULL,
  events TEXT NOT NULL, -- JSON array
  active BOOLEAN DEFAULT 1,
  secret TEXT,
  headers TEXT, -- JSON
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

import_jobs

Bulk data import tracking.

CREATE TABLE import_jobs (
  id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  type TEXT NOT NULL, -- events, users, cohort_members
  status TEXT DEFAULT 'uploading',
  file_name TEXT NOT NULL,
  file_size INTEGER NOT NULL,
  total_records INTEGER,
  processed_records INTEGER DEFAULT 0,
  failed_records INTEGER DEFAULT 0,
  errors TEXT, -- JSON array
  created_at INTEGER NOT NULL,
  started_at INTEGER,
  completed_at INTEGER
);

export_jobs

Scheduled export configuration.

CREATE TABLE export_jobs (
  id TEXT PRIMARY KEY,
  site_id TEXT NOT NULL,
  type TEXT NOT NULL, -- report, metrics, audience
  name TEXT NOT NULL,
  format TEXT DEFAULT 'csv',
  filters TEXT, -- JSON
  status TEXT DEFAULT 'pending',
  file_url TEXT,
  scheduled TEXT, -- JSON {frequency, next_run}
  created_at INTEGER NOT NULL,
  completed_at INTEGER
);

Relationship Diagram

sites
  ├── events
  ├── sessions
  ├── users
  ├── cohorts
  │   └── cohort_members
  │       └── cohort_retention_snapshots
  ├── alert_rules
  ├── webhook_endpoints
  ├── import_jobs
  └── export_jobs

Indexing Strategy

Critical indexes for query performance:

-- Event queries (hot path)
CREATE INDEX idx_events_site_timestamp ON events(site_id, timestamp DESC);
CREATE INDEX idx_events_session ON events(session_id);
CREATE INDEX idx_events_user ON events(user_id);

-- Cohort queries
CREATE INDEX idx_cohort_members_cohort ON cohort_members(cohort_id);
CREATE INDEX idx_cohort_members_session ON cohort_members(session_id);

-- Alert and webhook queries
CREATE INDEX idx_alert_rules_site ON alert_rules(site_id);
CREATE INDEX idx_webhook_endpoints_site ON webhook_endpoints(site_id);

-- Job tracking
CREATE INDEX idx_import_jobs_status ON import_jobs(status);
CREATE INDEX idx_export_jobs_status ON export_jobs(status);

Data Types

PULSE uses SQLite’s native types:

Time Handling

All timestamps are in milliseconds since Unix epoch for consistency:

// Correct
const timestamp = Date.now()  // milliseconds

// Incorrect
const timestamp = Math.floor(Date.now() / 1000)  // seconds

Data Retention

Automatic cleanup based on retention policy:

-- Events older than 90 days are deleted
DELETE FROM events
WHERE created_at < (CURRENT_TIMESTAMP - 90 * 86400000)

-- Exports older than 30 days are deleted
DELETE FROM export_jobs
WHERE created_at < (CURRENT_TIMESTAMP - 30 * 86400000)

Query Patterns

Common Aggregations

-- Daily event count
SELECT DATE(timestamp_ms/1000, 'unixepoch') as day,
       COUNT(*) as event_count
FROM events
WHERE site_id = ? AND timestamp_ms BETWEEN ? AND ?
GROUP BY day

-- Unique users
SELECT COUNT(DISTINCT user_id) as unique_users
FROM events
WHERE site_id = ? AND timestamp_ms BETWEEN ? AND ?

-- Conversion funnel
SELECT event_type,
       COUNT(*) as count,
       COUNT(DISTINCT user_id) as users
FROM events
WHERE site_id = ? AND timestamp_ms BETWEEN ? AND ?
GROUP BY event_type

Next Steps

• Components — System components
• Performance — Query optimization
• API Reference — Event format