cocottetech/@platform/infrastructure/sql/migrations/0009_content_drops.sql

-- 0009_content_drops.sql
-- (Renumbered from 0007 — main already holds 0007_surface_bump_policy + 0008_fix_surface_rls_guc.)
--
-- Content-drop composer schema. A "drop" sits ABOVE the existing per-surface
-- content tables (content_plans / content_assets / content_posts from 0001):
-- a cluster of assets + one narrative arc, split into per-surface "legs"
-- (anchor NSFW set → onlyfans/fansly · ToS-safe teasers → x/instagram ·
-- long-form → blog) laid on a tease → drop → follow-up timeline.
--
-- Spec: @platform/codebase/@features/ai-copilot/docs/content-drop-composer.screen.md
-- Owned by content-social (x/ig/fansly/blog legs) + content-onlyfans (OF leg).
--
-- On dispatch a leg materializes a content_plan (per surface) + content_posts
-- (per asset); until then the leg holds the derived intent (plan_id NULL). The
-- drop itself is a parent agent_actions row (target_kind='content_drop'); each
-- leg dispatch is a child row — same lineage pattern as cross-surface-fanout.
--
-- Reuses helpers from 0001: current_user_uuid() (RLS) + touch_updated_at().
-- platform.db is Postgres ≥ 14 on black:25437 (INFRA.md §1).

BEGIN;

-- ---------------------------------------------------------------------------
-- Enum types
-- ---------------------------------------------------------------------------
CREATE TYPE content_drop_state AS ENUM (
  'clustering',   -- assets being grouped into the drop
  'arc_draft',    -- narrative arc drafted; per-surface legs not yet derived
  'derived',      -- legs derived from the arc; ready for review/schedule
  'scheduled',    -- timeline set; queued for dispatch
  'dispatched',   -- handed to the cross-surface fanout machinery
  'partial',      -- some legs published, some failed
  'done',         -- all legs resolved
  'cancelled'
);

CREATE TYPE drop_leg_role AS ENUM ('anchor', 'teaser', 'longform');

CREATE TYPE drop_leg_tos_status AS ENUM ('ok', 'flagged', 'blocked');

-- ---------------------------------------------------------------------------
-- content_drops: the narrative bundle (one arc, many legs).
-- ---------------------------------------------------------------------------
CREATE TABLE content_drops (
  id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id                UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  org_id                 UUID NULL     REFERENCES orgs(id)  ON DELETE CASCADE,
  title                  TEXT NOT NULL,
  arc                    TEXT NOT NULL DEFAULT '',          -- the narrative spine (markdown)
  state                  content_drop_state NOT NULL DEFAULT 'clustering',
  cluster_source         TEXT NULL,                         -- 'shoot:2026-10-03' | 'tag:luxe' | 'manual'
  tease_at               TIMESTAMPTZ NULL,
  drop_at                TIMESTAMPTZ NULL,                  -- NULL until scheduled
  followup_at            TIMESTAMPTZ NULL,
  created_by_specialist  TEXT NOT NULL DEFAULT 'content-social',
  created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT content_drops_timeline_chk CHECK (
        (tease_at    IS NULL OR drop_at IS NULL OR tease_at    <= drop_at)
    AND (followup_at IS NULL OR drop_at IS NULL OR followup_at >= drop_at)
  )
);
CREATE INDEX idx_content_drops_user    ON content_drops(user_id, created_at DESC);
CREATE INDEX idx_content_drops_org     ON content_drops(org_id, created_at DESC) WHERE org_id IS NOT NULL;
CREATE INDEX idx_content_drops_state   ON content_drops(state);
CREATE INDEX idx_content_drops_drop_at ON content_drops(drop_at) WHERE drop_at IS NOT NULL;

-- ---------------------------------------------------------------------------
-- content_drop_assets: the ordered asset cluster backing a drop.
-- ---------------------------------------------------------------------------
CREATE TABLE content_drop_assets (
  drop_id     UUID NOT NULL REFERENCES content_drops(id)  ON DELETE CASCADE,
  asset_id    UUID NOT NULL REFERENCES content_assets(id) ON DELETE CASCADE,
  sort_order  INT  NOT NULL DEFAULT 0,
  PRIMARY KEY (drop_id, asset_id)
);
CREATE INDEX idx_content_drop_assets_asset ON content_drop_assets(asset_id);

-- ---------------------------------------------------------------------------
-- content_drop_legs: one per target surface, or a brand-site for the longform
-- leg. Exactly one of (surface, brand_site_target) is set per leg.
-- ---------------------------------------------------------------------------
CREATE TABLE content_drop_legs (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  drop_id           UUID NOT NULL REFERENCES content_drops(id) ON DELETE CASCADE,
  user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,  -- denormalized for RLS
  org_id            UUID NULL     REFERENCES orgs(id)  ON DELETE CASCADE,
  surface           surface_kind NULL,            -- external-platform target (of/fansly/x/instagram/...)
  brand_site_target TEXT NULL,                    -- longform/blog target (N3); placeholder until brand_site_kind ENUM lands (0002 deferral)
  role              drop_leg_role NOT NULL,
  asset_ids         UUID[] NOT NULL DEFAULT '{}', -- subset of the drop cluster this leg uses
  caption           TEXT NOT NULL DEFAULT '',
  link_target       TEXT NULL,                    -- K3b: never onlyfans.com/fansly.com from SFW surfaces (storefront redirect)
  ppv_price_cents   INT NULL,                     -- anchor legs (OF/Fansly) only
  ppv_currency      TEXT NULL,                    -- ISO 4217 when ppv_price_cents set
  tos_status        drop_leg_tos_status NOT NULL DEFAULT 'ok',
  plan_id           UUID NULL REFERENCES content_plans(id) ON DELETE SET NULL,  -- set when the leg is dispatched
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT drop_leg_target_chk CHECK (
        (surface IS NOT NULL AND brand_site_target IS NULL)
     OR (surface IS NULL     AND brand_site_target IS NOT NULL)
  ),
  CONSTRAINT drop_leg_ppv_chk CHECK (
    ppv_price_cents IS NULL
    OR (ppv_price_cents >= 0 AND ppv_currency ~ '^[A-Z]{3}$')
  ),
  -- One leg per surface per drop (NULLs distinct → multiple brand-site legs allowed).
  CONSTRAINT uq_drop_leg_surface UNIQUE (drop_id, surface)
);
CREATE INDEX idx_content_drop_legs_drop ON content_drop_legs(drop_id);
CREATE INDEX idx_content_drop_legs_user ON content_drop_legs(user_id, created_at DESC);
CREATE INDEX idx_content_drop_legs_tos  ON content_drop_legs(tos_status) WHERE tos_status <> 'ok';
CREATE INDEX idx_content_drop_legs_plan ON content_drop_legs(plan_id) WHERE plan_id IS NOT NULL;

-- ---------------------------------------------------------------------------
-- updated_at triggers (touch_updated_at() defined in 0001).
-- ---------------------------------------------------------------------------
CREATE TRIGGER trg_touch_content_drops BEFORE UPDATE ON content_drops
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
CREATE TRIGGER trg_touch_content_drop_legs BEFORE UPDATE ON content_drop_legs
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- ---------------------------------------------------------------------------
-- Row-level security (defense-in-depth, per 0001 convention).
-- current_user_uuid() reads the app.current_user_id GUC (defined in 0001).
-- ---------------------------------------------------------------------------
ALTER TABLE content_drops       ENABLE ROW LEVEL SECURITY;
ALTER TABLE content_drop_legs   ENABLE ROW LEVEL SECURITY;
ALTER TABLE content_drop_assets ENABLE ROW LEVEL SECURITY;

-- user/org-scoped tables: same policy shape as 0001's content_* tables.
DO $$
DECLARE t TEXT;
BEGIN
  FOREACH t IN ARRAY ARRAY['content_drops','content_drop_legs'] LOOP
    EXECUTE format($p$
      CREATE POLICY tenant_isolation ON %1$s
        USING (
          user_id = current_user_uuid()
          OR (org_id IS NOT NULL
              AND org_id IN (SELECT org_id FROM org_members
                             WHERE user_id = current_user_uuid()))
        )
        WITH CHECK (
          user_id = current_user_uuid()
          OR (org_id IS NOT NULL
              AND org_id IN (SELECT org_id FROM org_members
                             WHERE user_id = current_user_uuid()))
        );
    $p$, t);
  END LOOP;
END $$;

-- content_drop_assets has no own tenancy columns: inherit from the parent drop.
CREATE POLICY tenant_isolation ON content_drop_assets
  USING (
    drop_id IN (
      SELECT id FROM content_drops
      WHERE user_id = current_user_uuid()
        OR (org_id IS NOT NULL
            AND org_id IN (SELECT org_id FROM org_members
                           WHERE user_id = current_user_uuid()))
    )
  )
  WITH CHECK (
    drop_id IN (
      SELECT id FROM content_drops
      WHERE user_id = current_user_uuid()
        OR (org_id IS NOT NULL
            AND org_id IN (SELECT org_id FROM org_members
                           WHERE user_id = current_user_uuid()))
    )
  );

COMMENT ON TABLE content_drops IS
  'Narrative content drop: a cluster of assets + one arc, split into per-surface legs on a tease/drop/follow-up timeline. Sits above content_plans/posts; legs materialize plans+posts on dispatch.';

COMMIT;