# Read schema reference
cat docs/guides/schema-reference.md

# Or check existing migrations
ls supabase/migrations/

20251230120000_add_agent_registry_table.sql

-- Tables
CREATE TABLE IF NOT EXISTS agent_registry (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  agent_id TEXT NOT NULL,
  version TEXT NOT NULL,
  capabilities JSONB NOT NULL DEFAULT '[]'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),

  UNIQUE(workspace_id, agent_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_agent_registry_workspace
  ON agent_registry(workspace_id);

CREATE INDEX IF NOT EXISTS idx_agent_registry_agent
  ON agent_registry(agent_id, workspace_id);

-- RLS
ALTER TABLE agent_registry ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view their workspace agents" ON agent_registry;
CREATE POLICY "Users can view their workspace agents" ON agent_registry
  FOR SELECT USING (
    workspace_id IN (
      SELECT w.id FROM workspaces w
      INNER JOIN user_organizations uo ON uo.org_id = w.org_id
      WHERE uo.user_id = auth.uid()
    )
  );

DROP POLICY IF EXISTS "System can manage agents" ON agent_registry;
CREATE POLICY "System can manage agents" ON agent_registry
  FOR ALL USING (true) WITH CHECK (true);

-- Functions
CREATE OR REPLACE FUNCTION get_agent_count(p_workspace_id UUID)
RETURNS INTEGER AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM agent_registry WHERE workspace_id = p_workspace_id);
END;
$$ LANGUAGE plpgsql STABLE;

-- Comments
COMMENT ON TABLE agent_registry IS 'Registry of all active agents per workspace';

-- Correct pattern
workspace_id IN (
  SELECT w.id FROM workspaces w
  INNER JOIN user_organizations uo ON uo.org_id = w.org_id
  WHERE uo.user_id = auth.uid()
)

-- For admin/owner only
workspace_id IN (
  SELECT w.id FROM workspaces w
  INNER JOIN user_organizations uo ON uo.org_id = w.org_id
  WHERE uo.user_id = auth.uid() AND uo.role IN ('admin', 'owner')
)

CREATE TABLE IF NOT EXISTS my_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_my_table_workspace ON my_table(workspace_id);

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "workspace_isolation" ON my_table;
CREATE POLICY "workspace_isolation" ON my_table
  FOR ALL USING (
    workspace_id IN (
      SELECT w.id FROM workspaces w
      INNER JOIN user_organizations uo ON uo.org_id = w.org_id
      WHERE uo.user_id = auth.uid()
    )
  );

DO $$ BEGIN
  CREATE TYPE agent_status AS ENUM ('active', 'paused', 'disabled');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_updated_at ON my_table;
CREATE TRIGGER trigger_update_updated_at
  BEFORE UPDATE ON my_table
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

CREATE TABLE table_name (
  ...
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  ...
);

-- Always add index on workspace_id
CREATE INDEX IF NOT EXISTS idx_table_workspace ON table_name(workspace_id);

-- Always add RLS
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

-- Check constraints
CONSTRAINT valid_status CHECK (status IN ('active', 'inactive')),
CONSTRAINT valid_score CHECK (score >= 0 AND score <= 100),
CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

-- With cascade delete
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,

-- With set null
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,

-- With restrict (prevents delete if referenced)
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE RESTRICT