Milestone 1.1.7 — Core Domain Schema: users and agents Migrations

-- Shared trigger function for updated_at maintenance.
-- Referenced by organizations (already created), users, agents.
CREATE OR REPLACE FUNCTION ibex_core.set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 000005_create_set_updated_at_function.down.sql
DROP FUNCTION IF EXISTS ibex_core.set_updated_at() CASCADE;

CREATE TABLE ibex_core.users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id          UUID NOT NULL
                    REFERENCES ibex_core.organizations(id)
                    ON DELETE RESTRICT,
    email           TEXT NOT NULL,
    name            TEXT NOT NULL,
    role            TEXT NOT NULL DEFAULT 'member'
                    CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'invited', 'suspended', 'deactivated')),
 
    -- SSO (nullable; filled when org uses SSO)
    sso_provider    TEXT,
    sso_subject     TEXT,
 
    -- Timestamps
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ,
 
    UNIQUE(org_id, email),
    CONSTRAINT users_email_format CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$')
);
 
CREATE INDEX idx_users_org_id
    ON ibex_core.users(org_id)
    WHERE deleted_at IS NULL;
 
CREATE INDEX idx_users_email
    ON ibex_core.users(email)
    WHERE deleted_at IS NULL;
 
-- RLS: users see only their own org's records
ALTER TABLE ibex_core.users ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY users_isolation ON ibex_core.users
    USING (
        org_id = current_setting('app.current_org_id', true)::UUID
        OR id  = current_setting('app.current_user_id', true)::UUID
        OR current_setting('app.is_service_account', true)::BOOLEAN = true
    );
 
CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON ibex_core.users
    FOR EACH ROW EXECUTE FUNCTION ibex_core.set_updated_at();

-- 000006_create_users.down.sql
DROP TRIGGER IF EXISTS users_updated_at ON ibex_core.users;
DROP POLICY IF EXISTS users_isolation ON ibex_core.users;
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX IF EXISTS idx_users_org_id;
DROP TABLE IF EXISTS ibex_core.users;

CREATE TABLE ibex_core.agents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id      UUID NOT NULL
                REFERENCES ibex_core.organizations(id)
                ON DELETE RESTRICT,
    created_by  UUID
                REFERENCES ibex_core.users(id)
                ON DELETE SET NULL,
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'paused', 'suspended', 'archived')),
    config      JSONB NOT NULL DEFAULT '{}',
    metadata    JSONB NOT NULL DEFAULT '{}',
    tags        TEXT[] NOT NULL DEFAULT '{}',
 
    -- Statistics (denormalized; maintained by triggers in Phase 3)
    total_sessions    INTEGER NOT NULL DEFAULT 0,
    total_memories    INTEGER NOT NULL DEFAULT 0,
    total_tokens_used BIGINT  NOT NULL DEFAULT 0,
    last_active_at    TIMESTAMPTZ,
 
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ,
 
    UNIQUE(org_id, slug),
    CONSTRAINT agents_slug_format CHECK (slug ~ '^[a-z0-9-]+$')
);
 
CREATE INDEX idx_agents_org_id
    ON ibex_core.agents(org_id)
    WHERE deleted_at IS NULL;
 
CREATE INDEX idx_agents_status
    ON ibex_core.agents(org_id, status)
    WHERE deleted_at IS NULL;
 
ALTER TABLE ibex_core.agents ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY agents_isolation ON ibex_core.agents
    USING (
        org_id = current_setting('app.current_org_id', true)::UUID
        OR current_setting('app.is_service_account', true)::BOOLEAN = true
    );
 
CREATE TRIGGER agents_updated_at
    BEFORE UPDATE ON ibex_core.agents
    FOR EACH ROW EXECUTE FUNCTION ibex_core.set_updated_at();

-- 000007_create_agents.down.sql
DROP TRIGGER IF EXISTS agents_updated_at ON ibex_core.agents;
DROP POLICY IF EXISTS agents_isolation ON ibex_core.agents;
DROP INDEX IF EXISTS idx_agents_status;
DROP INDEX IF EXISTS idx_agents_org_id;
DROP TABLE IF EXISTS ibex_core.agents;

-- Add the deferred FK constraints noted in DATABASE_SCHEMA.md.
-- Executed as NOT VALID to avoid a full table scan on the existing
-- tokens rows (which currently have NULL values in these columns).
-- VALIDATE CONSTRAINT is run separately below to allow concurrent reads.
 
ALTER TABLE ibex_core.tokens
    ADD CONSTRAINT tokens_user_id_fk
    FOREIGN KEY (user_id)
    REFERENCES ibex_core.users(id)
    ON DELETE CASCADE
    NOT VALID;
 
ALTER TABLE ibex_core.tokens
    ADD CONSTRAINT tokens_agent_id_fk
    FOREIGN KEY (agent_id)
    REFERENCES ibex_core.agents(id)
    ON DELETE CASCADE
    NOT VALID;
 
ALTER TABLE ibex_core.tokens
    ADD CONSTRAINT tokens_revoked_by_fk
    FOREIGN KEY (revoked_by)
    REFERENCES ibex_core.users(id)
    ON DELETE SET NULL
    NOT VALID;
 
-- Validate now (all existing rows have NULL, so this is instant)
ALTER TABLE ibex_core.tokens VALIDATE CONSTRAINT tokens_user_id_fk;
ALTER TABLE ibex_core.tokens VALIDATE CONSTRAINT tokens_agent_id_fk;
ALTER TABLE ibex_core.tokens VALIDATE CONSTRAINT tokens_revoked_by_fk;

-- 000008_tokens_fk_constraints.down.sql
ALTER TABLE ibex_core.tokens DROP CONSTRAINT IF EXISTS tokens_revoked_by_fk;
ALTER TABLE ibex_core.tokens DROP CONSTRAINT IF EXISTS tokens_agent_id_fk;
ALTER TABLE ibex_core.tokens DROP CONSTRAINT IF EXISTS tokens_user_id_fk;

// Package store provides typed database access functions.
// All queries enforce org_id isolation via the RLS session variable.
package store
 
import (
    "context"
 
    "github.com/google/uuid"
    "github.com/jackc/pgx/v5/pgxpool"
)
 
// AgentRecord is the minimal projection needed by the proxy for
// agent identity verification. Additional fields are added as
// downstream services require them.
type AgentRecord struct {
    ID     uuid.UUID
    OrgID  uuid.UUID
    Status string
}
 
// AgentStore provides read access to ibex_core.agents.
type AgentStore struct {
    pool *pgxpool.Pool
}
 
func NewAgentStore(pool *pgxpool.Pool) *AgentStore {
    return &AgentStore{pool: pool}
}
 
// GetByIDAndOrg fetches a single agent and verifies it belongs to orgID.
// Returns (nil, nil) when the agent does not exist or belongs to a
// different org — the caller maps this to a 403, not a 404, to avoid
// leaking the existence of another org's agent.
func (s *AgentStore) GetByIDAndOrg(
    ctx context.Context,
    agentID, orgID uuid.UUID,
) (*AgentRecord, error) {
    const q = `
        SELECT id, org_id, status
        FROM ibex_core.agents
        WHERE id = $1
          AND org_id = $2
          AND deleted_at IS NULL
        LIMIT 1`
 
    row := s.pool.QueryRow(ctx, q, agentID, orgID)
    var a AgentRecord
    if err := row.Scan(&a.ID, &a.OrgID, &a.Status); err != nil {
        return nil, err // pgx.ErrNoRows on miss; caller interprets
    }
    return &a, nil
}

// ValidateAgent checks that agent_id belongs to org_id and is active.
// Returns the agent record on success, or UNAUTHENTICATED/PERMISSION_DENIED
// on failure. Called by the proxy after ValidateToken.
rpc ValidateAgent(ValidateAgentRequest) returns (ValidateAgentResponse);

message ValidateAgentRequest {
    string agent_id = 1; // UUID string
    string org_id   = 2; // UUID string; extracted from token claims
}

message ValidateAgentResponse {
    string agent_id = 1;
    string org_id   = 2;
    string status   = 3; // "active", "paused", "suspended", "archived"
}