CREATE TABLE boundary_sessions (
    id UUID PRIMARY KEY,
    workspace_agent_id UUID NOT NULL REFERENCES workspace_agents(id),
    confined_process_name TEXT NOT NULL,
    started_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

COMMENT ON TABLE boundary_sessions IS 'Boundary session metadata. Each row represents a single invocation of a Boundary process wrapping a confined agent.';
COMMENT ON COLUMN boundary_sessions.id IS 'The unique session ID generated by the Boundary process on startup.';
COMMENT ON COLUMN boundary_sessions.workspace_agent_id IS 'The workspace agent that this Boundary session is associated with.';
COMMENT ON COLUMN boundary_sessions.confined_process_name IS 'Name of the confined process (e.g. claude-code, codex, copilot).';
COMMENT ON COLUMN boundary_sessions.started_at IS 'Time when the first log for this session was received by coderd.';
COMMENT ON COLUMN boundary_sessions.updated_at IS 'Time when the session was last updated.';

CREATE TABLE boundary_logs (
    id UUID NOT NULL,
    session_id UUID NOT NULL REFERENCES boundary_sessions(id) ON DELETE CASCADE,
    sequence_number INT NOT NULL CHECK (sequence_number >= 0),
    captured_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    proto TEXT NOT NULL DEFAULT '',
    method TEXT NOT NULL DEFAULT '',
    detail TEXT NOT NULL DEFAULT '',
    matched_rule TEXT,

    PRIMARY KEY (id)
);

COMMENT ON TABLE boundary_logs IS 'Persisted boundary audit events. Each row is a single audit event processed by a Boundary proxy.';
COMMENT ON COLUMN boundary_logs.session_id IS 'The session ID generated by the Boundary process on startup. Groups all events from one invocation.';
COMMENT ON COLUMN boundary_logs.sequence_number IS 'Monotonically increasing integer assigned by Boundary, starting at 0 per session. Primary ordering key when Boundary is in use.';
COMMENT ON COLUMN boundary_logs.captured_at IS 'When the log was sent to the DB.';
COMMENT ON COLUMN boundary_logs.created_at IS 'When the event happened on the workspace.';
COMMENT ON COLUMN boundary_logs.proto IS 'The protocol of the audited action. e.g. http, dns, git, fs.';
COMMENT ON COLUMN boundary_logs.method IS 'The operation within the protocol. e.g. GET/POST for http, clone for git, A for dns, read/write for fs.';
COMMENT ON COLUMN boundary_logs.detail IS 'Protocol-specific detail. e.g. the full URL for http, the hostname for dns, the path for fs.';
COMMENT ON COLUMN boundary_logs.matched_rule IS 'The allow-list rule that matched. NULL when the request was denied; non-NULL implies the request was allowed.';

-- Ordering query path: list events for a session, sorted by sequence number.
CREATE INDEX idx_boundary_logs_session_seq ON boundary_logs (session_id, sequence_number);
-- Retention purge path: delete old rows by capture time.
CREATE INDEX idx_boundary_logs_captured_at ON boundary_logs (captured_at);
