Environment Variables

SELECT id AS audit_id, company_id, entity_id AS agent_id, details, created_at
FROM activity_log
WHERE action = 'marketplace.legacy_steward_adopted'
ORDER BY created_at DESC;

BEGIN;
CREATE TEMP TABLE steward_reconcile_backout_targets ON COMMIT DROP AS
SELECT
  company_id,
  entity_id::uuid AS agent_id,
  (details->>'teamId')::uuid AS team_id,
  COALESCE((details->>'memberInserted')::boolean, false) AS member_inserted,
  details->>'previousTemplateVersion' AS previous_template_version
FROM activity_log
WHERE action = 'marketplace.legacy_steward_adopted'
  AND id IN (
    '00000000-0000-0000-0000-000000000000'::uuid
  );

-- Preview every target and predicate before changing anything.
SELECT targets.*, steward.template_origin, steward.template_version
FROM steward_reconcile_backout_targets AS targets
JOIN agents AS steward
  ON steward.company_id = targets.company_id
 AND steward.id = targets.agent_id;

CREATE TEMP TABLE steward_reconcile_reverted_targets (
  company_id uuid NOT NULL,
  agent_id uuid NOT NULL,
  team_id uuid NOT NULL,
  member_inserted boolean NOT NULL
) ON COMMIT DROP;

WITH reverted AS (
  UPDATE agents AS steward
  SET template_origin = NULL,
      template_version = targets.previous_template_version,
      updated_at = now()
  FROM steward_reconcile_backout_targets AS targets
  WHERE steward.company_id = targets.company_id
    AND steward.id = targets.agent_id
    AND steward.kind = 'aoa'
    AND steward.name = 'Steward'
    AND steward.template_origin = 'agent:aoa-curated/aoa-steward'
    AND steward.template_version = '0.0.0-legacy'
  RETURNING
    targets.company_id,
    targets.agent_id,
    targets.team_id,
    targets.member_inserted
)
INSERT INTO steward_reconcile_reverted_targets
SELECT * FROM reverted
RETURNING agent_id, company_id;

-- Abort the whole transaction instead of performing a partial backout if any
-- selected row no longer has the exact post-reconciliation pointer state.
DO $$
BEGIN
  IF (
    SELECT count(*) FROM steward_reconcile_reverted_targets
  ) <> (
    SELECT count(*) FROM steward_reconcile_backout_targets
  ) THEN
    RAISE EXCEPTION
      'Steward backout aborted: selected and reverted target counts differ';
  END IF;
END
$$;

DELETE FROM team_members AS member
USING steward_reconcile_reverted_targets AS targets
WHERE targets.member_inserted
  AND member.team_id = targets.team_id
  AND member.agent_id = targets.agent_id
RETURNING member.id, member.team_id, member.agent_id;

CREATE TEMP TABLE steward_reconcile_reverted_updates ON COMMIT DROP AS
SELECT
  pending.id,
  pending.company_id,
  pending.latest_version
FROM marketplace_pending_updates AS pending
JOIN steward_reconcile_reverted_targets AS targets
  ON targets.company_id = pending.company_id
WHERE pending.catalog_item_id = 'agent:aoa-curated/aoa-steward'
  AND pending.current_version = '0.0.0-legacy';

DELETE FROM marketplace_pending_updates AS pending
USING steward_reconcile_reverted_updates AS reverted
WHERE pending.id = reverted.id
RETURNING pending.id, pending.company_id;

UPDATE notifications AS hub
SET status = 'archived',
    archived_at = now()
FROM steward_reconcile_reverted_updates AS reverted
WHERE hub.company_id = reverted.company_id
  AND hub.status = 'open'
  AND hub.semantic_type = 'marketplace_op'
  AND hub.source_type = 'marketplace_update'
  AND (
    hub.source_id = 'update_available:Steward:' || reverted.latest_version
    OR left(
         hub.source_id,
         length('update_available:Steward:' || reverted.latest_version || ':')
       ) = 'update_available:Steward:' || reverted.latest_version || ':'
  )
RETURNING hub.id, hub.company_id, hub.source_id;

COMMIT;