URL Shortener at Global Scale

-- URL Shortener Core Schema (PostgreSQL)

CREATE TABLE tenants (
  tenant_id UUID PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  plan VARCHAR(32) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE links (
  link_id BIGINT PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
  short_code VARCHAR(16) NOT NULL,
  long_url TEXT NOT NULL,
  long_url_hash BYTEA NOT NULL,
  canonical_domain VARCHAR(255) NOT NULL,
  status VARCHAR(24) NOT NULL CHECK (status IN ('active', 'disabled', 'deleted', 'expired')),
  is_custom_alias BOOLEAN NOT NULL DEFAULT FALSE,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ,
  deleted_at TIMESTAMPTZ,
  metadata JSONB NOT NULL DEFAULT '{}',
  UNIQUE (tenant_id, short_code)
);

CREATE TABLE alias_claims (
  tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
  alias VARCHAR(64) NOT NULL,
  short_code VARCHAR(16) NOT NULL,
  claimed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (tenant_id, alias)
);

CREATE TABLE idempotency_keys (
  tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
  idempotency_key UUID NOT NULL,
  request_hash BYTEA NOT NULL,
  status VARCHAR(16) NOT NULL CHECK (status IN ('in_progress', 'completed', 'failed')),
  response_payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (tenant_id, idempotency_key)
);

CREATE TABLE deletion_tombstones (
  tenant_id UUID NOT NULL REFERENCES tenants(tenant_id),
  short_code VARCHAR(16) NOT NULL,
  tombstone_version BIGINT NOT NULL,
  reason VARCHAR(64) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (tenant_id, short_code)
);

-- High-volume click stream landing table (partition by day)
CREATE TABLE click_events (
  event_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  short_code VARCHAR(16) NOT NULL,
  clicked_at TIMESTAMPTZ NOT NULL,
  country_code CHAR(2),
  device_type VARCHAR(24),
  referrer_domain VARCHAR(255),
  ip_hash BYTEA,
  user_agent_hash BYTEA,
  edge_region VARCHAR(24),
  PRIMARY KEY (event_id, clicked_at)
) PARTITION BY RANGE (clicked_at);

CREATE TABLE click_events_2026_02 PARTITION OF click_events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE daily_link_metrics (
  tenant_id UUID NOT NULL,
  short_code VARCHAR(16) NOT NULL,
  metric_date DATE NOT NULL,
  country_code CHAR(2) NOT NULL DEFAULT 'ZZ',
  device_type VARCHAR(24) NOT NULL DEFAULT 'unknown',
  clicks BIGINT NOT NULL,
  uniques BIGINT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (tenant_id, short_code, metric_date, country_code, device_type)
);

CREATE TABLE blocked_domains (
  domain VARCHAR(255) PRIMARY KEY,
  risk_score NUMERIC(4,3) NOT NULL,
  source VARCHAR(64) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_links_tenant_status_created
  ON links (tenant_id, status, created_at DESC);

CREATE INDEX idx_links_active_lookup
  ON links (tenant_id, short_code)
  WHERE status = 'active';

CREATE INDEX idx_links_expires_at
  ON links (expires_at)
  WHERE expires_at IS NOT NULL;

CREATE INDEX idx_alias_claims_short_code
  ON alias_claims (short_code);

CREATE INDEX idx_idempotency_last_seen
  ON idempotency_keys (tenant_id, last_seen_at DESC);

CREATE INDEX idx_tombstones_version
  ON deletion_tombstones (tombstone_version DESC);

CREATE INDEX idx_click_events_tenant_code_time
  ON click_events (tenant_id, short_code, clicked_at DESC);

CREATE INDEX idx_click_events_country_time
  ON click_events (country_code, clicked_at DESC);

CREATE INDEX idx_daily_metrics_date
  ON daily_link_metrics (metric_date DESC, tenant_id);