Feature Flag Evaluation Engine at Scale

-- Core schema for feature flag engine (PostgreSQL)

-- Projects organize flags by team/application
CREATE TABLE projects (
  project_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  key VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

-- Flag definitions with versioning
CREATE TABLE flags (
  flag_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(project_id),
  key VARCHAR(100) NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  flag_type VARCHAR(20) NOT NULL CHECK (flag_type IN ('boolean', 'string', 'number', 'json')),

  -- State
  enabled BOOLEAN NOT NULL DEFAULT false,
  kill_switch_enabled BOOLEAN NOT NULL DEFAULT false,
  archived BOOLEAN NOT NULL DEFAULT false,

  -- Variants (e.g., [{key: "on", value: true}, {key: "off", value: false}])
  variants JSONB NOT NULL,
  off_variant VARCHAR(50) NOT NULL,

  -- Default rule when no targeting rules match
  default_rule JSONB NOT NULL,

  -- Bucketing salt (change to re-randomize experiment assignments)
  salt VARCHAR(50) NOT NULL DEFAULT gen_random_uuid()::text,

  -- Versioning
  version BIGINT NOT NULL DEFAULT 1,
  config_version VARCHAR(50) NOT NULL DEFAULT '1',

  -- Metadata
  tags TEXT[] DEFAULT '{}',
  created_by UUID NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),

  UNIQUE(project_id, key)
);

-- Targeting rules evaluated in priority order
CREATE TABLE flag_rules (
  rule_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flag_id UUID NOT NULL REFERENCES flags(flag_id) ON DELETE CASCADE,
  priority INT NOT NULL,
  name VARCHAR(100),
  description TEXT,

  -- Conditions expression tree (supports nested AND/OR/NOT)
  -- Example:
  -- {"op":"AND","clauses":[{"attribute":"country","op":"in","values":["US","CA"]},{"op":"NOT","clause":{"attribute":"app_version","op":"sem_ver_lt","value":"2.3.0"}}]}
  conditions JSONB NOT NULL,

  -- Result when matched
  variation_key VARCHAR(50),  -- Serve specific variant
  rollout JSONB,              -- Or percentage rollout

  enabled BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),

  UNIQUE(flag_id, priority)
);

-- Reusable user segments
CREATE TABLE segments (
  segment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID NOT NULL REFERENCES projects(project_id),
  key VARCHAR(100) NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,

  -- Rule-based membership
  rules JSONB NOT NULL DEFAULT '[]',

  -- Explicit user lists (for small segments, testing)
  included_users TEXT[] DEFAULT '{}',
  excluded_users TEXT[] DEFAULT '{}',

  -- For large user lists, use external reference
  user_list_url TEXT,  -- S3 URL for large lists
  user_count_approx INT DEFAULT 0,

  version BIGINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),

  UNIQUE(project_id, key)
);

-- Audit log (append-only, partitioned by month)
CREATE TABLE flag_audit_log (
  audit_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flag_id UUID NOT NULL,
  project_id UUID NOT NULL,

  -- Actor
  actor_id UUID NOT NULL,
  actor_type VARCHAR(20) NOT NULL CHECK (actor_type IN ('user', 'api_key', 'system', 'scheduled')),
  actor_email VARCHAR(255),

  -- Action
  action VARCHAR(50) NOT NULL,

  -- Change details
  previous_state JSONB,
  new_state JSONB,

  -- Context
  ip_address INET,
  user_agent TEXT,
  approval_id UUID,
  comment TEXT,

  created_at TIMESTAMP NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE flag_audit_log_2024_01 PARTITION OF flag_audit_log
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Scheduled flag changes
CREATE TABLE scheduled_changes (
  schedule_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flag_id UUID NOT NULL REFERENCES flags(flag_id),
  project_id UUID NOT NULL,

  change_type VARCHAR(30) NOT NULL,
  scheduled_at TIMESTAMP NOT NULL,
  change_payload JSONB NOT NULL,

  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'executed', 'cancelled', 'failed')),

  created_by UUID NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  executed_at TIMESTAMP,
  execution_error TEXT
);

-- Flag evaluation statistics for stale detection
CREATE TABLE flag_evaluation_stats (
  flag_id UUID NOT NULL REFERENCES flags(flag_id),
  date DATE NOT NULL,

  evaluation_count BIGINT NOT NULL DEFAULT 0,
  unique_users_hll BYTEA,  -- HyperLogLog for unique user approximation

  -- Variant distribution
  variant_counts JSONB DEFAULT '{}',

  -- Timing
  first_eval_at TIMESTAMP,
  last_eval_at TIMESTAMP,

  PRIMARY KEY (flag_id, date)
);

-- Indexes for common queries
CREATE INDEX idx_flags_project_key ON flags(project_id, key);
CREATE INDEX idx_flags_updated ON flags(updated_at) WHERE NOT archived;
CREATE INDEX idx_flags_stale ON flags(updated_at, enabled) WHERE NOT archived;
CREATE INDEX idx_flag_rules_flag ON flag_rules(flag_id, priority);
CREATE INDEX idx_segments_project ON segments(project_id);
CREATE INDEX idx_audit_flag ON flag_audit_log(flag_id, created_at DESC);
CREATE INDEX idx_audit_project ON flag_audit_log(project_id, created_at DESC);
CREATE INDEX idx_scheduled_pending ON scheduled_changes(scheduled_at)
  WHERE status = 'pending';
CREATE INDEX idx_eval_stats_flag ON flag_evaluation_stats(flag_id, date DESC);

-- Redis key schema (documented for SDK/edge reference)
-- flag:{project_key}:{flag_key} => JSON flag config
-- segment:{project_key}:{segment_key} => JSON segment definition
-- flag_version:{project_key} => latest config version hash
-- kill_switch:{project_key}:{flag_key} => "1" if active
-- user_segment:{segment_id}:{user_id} => "1" if user in segment (bloom filter)