-- Migration: shared_links
--
-- Public share links for trace/request views. PLG Loop ①: a logged-in user
-- generates a token from the dashboard and pastes spanlens.io/share/<token>
-- into Slack, an issue, a blog post. Anyone with the URL can view a
-- sanitised read-only render — no Spanlens account required.
--
-- Security model
--
--   The token IS the credential. There is no per-viewer ACL. Anyone with the
--   raw token can read until it expires or the owner revokes. Tokens are
--   generated server-side via crypto.randomHex(16) → 32 hex chars → ~128 bits
--   of entropy, well above any brute-force threshold for a public endpoint
--   that also rate-limits per IP.
--
--   The public lookup runs through the server's service-role client; this
--   table has deny-by-default RLS with no public SELECT/INSERT/UPDATE/DELETE
--   policies (same pattern as feedback / waitlist). The token is stored in
--   plain text because the lookup must be a single exact-match query on a
--   public path — hashing it would require either constant-time scan of all
--   rows or storing a non-secret lookup hint, neither of which improves the
--   security model in practice.
--
-- Sanitiser flags (fail-safe defaults)
--
--   redact_pii   DEFAULT true   — strip provider keys + Spanlens keys from
--                                 bodies via lib/pii-mask. Cheap, low cost
--                                 to debuggers, high cost if leaked.
--   redact_cost  DEFAULT true   — hide cost_usd. A trace's cost is workload
--                                 intel: combined with model + time it lets
--                                 a competitor estimate spend. Owner can
--                                 opt-in to showing cost when sharing.
--   redact_tokens DEFAULT false — token counts are essential debugging info
--                                 (which prompt was actually large?). Owner
--                                 can flip on for fully-anonymised public
--                                 shares; off by default to avoid breaking
--                                 the debugging use case.
--
-- Retention bypass
--
--   Share access in the server intentionally calls requestsScope() with
--   { ignoreRetention: true }. A Free-plan org has 14d retention on the
--   ClickHouse requests table, but a "never expire" share created on day 0
--   must still resolve on day 30. The TTL on the underlying ClickHouse table
--   is 365 days (gotcha #3 / requests-query.ts §3.1), so the longest usable
--   share window matches that ceiling. Anything older returns 404. This is
--   acceptable for PLG and not separately documented to viewers.
--
-- Indexability
--
--   indexable DEFAULT false. The /share/<token> page sets noindex unless the
--   owner explicitly toggles this on (rare: OSS maintainer wants the page
--   crawled). Default off prevents accidental public indexing of internal
--   debugging traces.

CREATE TABLE shared_links (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- URL-safe token; the only credential. Generated by lib/crypto.randomHex(16)
  -- so it's 32 hex chars (lowercase) with no ambiguous glyphs. Unique index
  -- below doubles as the lookup index for the public GET /share/:token path.
  token           TEXT NOT NULL UNIQUE,

  -- 'trace' or 'request'. Enforced at the API layer; the column is text (not
  -- an enum) so adding a new scope ('eval', 'dataset', etc.) later requires
  -- only an application-layer change.
  scope           TEXT NOT NULL CHECK (scope IN ('trace', 'request')),

  -- The trace_id (Postgres UUID) or request_id (ClickHouse UUID, stored as
  -- string). Stored as text to handle both sources uniformly.
  target_id       TEXT NOT NULL,

  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  -- Preserve the share if the user is deleted (e.g. left the org). Owner-only
  -- management UIs will treat null-creator shares as org-admin-managed.
  created_by      UUID REFERENCES auth.users(id) ON DELETE SET NULL,

  -- Sanitiser flags. See header comment for the fail-safe rationale.
  redact_pii      BOOLEAN NOT NULL DEFAULT true,
  redact_cost     BOOLEAN NOT NULL DEFAULT true,
  redact_tokens   BOOLEAN NOT NULL DEFAULT false,

  -- Allow search engines to index the share page. Default off — most shares
  -- contain semi-private debugging context the owner did not consent to
  -- indexing globally.
  indexable       BOOLEAN NOT NULL DEFAULT false,

  -- NULL = never expires. UI offers 7d / 30d / never presets.
  expires_at      TIMESTAMPTZ,

  -- Soft delete. Owner-revoked shares keep their view_count for the audit
  -- trail rather than being hard-deleted. The public endpoint treats
  -- revoked_at IS NOT NULL the same as an expired or missing token (404).
  revoked_at      TIMESTAMPTZ,

  -- Bumped synchronously on every public GET. UI exposes this on the share
  -- management page ("Viewed 245 times"). For analytics/funnels use the
  -- ClickHouse plg_events table — that's the system-of-record for
  -- attribution; this counter is just for UI display.
  view_count      INTEGER NOT NULL DEFAULT 0,

  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Listing index: most management UIs filter by org + sort by recency.
CREATE INDEX shared_links_org_created_idx
  ON shared_links (organization_id, created_at DESC);

-- "My shares" lookup: a user wants to see only the shares they created.
CREATE INDEX shared_links_creator_idx
  ON shared_links (created_by, created_at DESC)
  WHERE created_by IS NOT NULL;

-- Active-share filter helper for cleanup jobs / quota counters.
CREATE INDEX shared_links_active_idx
  ON shared_links (organization_id)
  WHERE revoked_at IS NULL;

-- ── RLS ────────────────────────────────────────────────────────────────────
-- Deny by default. Server reads/writes via service_role (bypasses RLS).
-- No authenticated-role policies, mirroring feedback + waitlist.
ALTER TABLE shared_links ENABLE ROW LEVEL SECURITY;

COMMENT ON TABLE shared_links IS
  'Public share tokens for trace/request views. PLG Loop ① — token in URL is the credential.';
COMMENT ON COLUMN shared_links.token IS
  'URL-safe lookup credential (32 hex chars / ~128 bits). Stored in plain text; the unique index is the lookup path.';
COMMENT ON COLUMN shared_links.redact_pii IS
  'Mask sk-*/sl_live_*/AIza* in bodies. Default true (fail-safe).';
COMMENT ON COLUMN shared_links.redact_cost IS
  'Hide cost_usd in shared view. Default true (workload intel protection).';
COMMENT ON COLUMN shared_links.redact_tokens IS
  'Hide prompt/completion token counts. Default false (essential debugging info).';
COMMENT ON COLUMN shared_links.indexable IS
  'Allow search engines to index. Default false (noindex meta in the rendered page).';
COMMENT ON COLUMN shared_links.expires_at IS
  'NULL = never. Public endpoint returns 404 (not 410) past expiry to avoid token enumeration leaks.';
