OneOps Unified Search (Cmd+K Omnibox), Grounded Assistant Bot (@OneOps) & Page-Context Sticky Suggestion Engine (O-048) (STOP 5 · T2-9 · O-043 · O-048 · GLEAN / PERPLEXITY ENTERPRISE / OWASP ASVS BENCHMARK)

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE search_index_documents (
    doc_id UUID PRIMARY KEY,
    entity_type VARCHAR(32) NOT NULL,
    entity_pk VARCHAR(64) NOT NULL,
    outlet_id VARCHAR(32),
    min_role_rank SMALLINT NOT NULL DEFAULT 10,
    title VARCHAR(200) NOT NULL,
    subtitle VARCHAR(300),
    attributes_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    search_tsv TSVECTOR NOT NULL,
    deep_link_url VARCHAR(255) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (entity_type, entity_pk)
);
CREATE INDEX idx_search_tsv ON search_index_documents USING GIN (search_tsv);
CREATE INDEX idx_search_trgm ON search_index_documents USING GIN (title gin_trgm_ops);

-- O-048 (21:40 IST): Page-Context-Aware Sticky Suggestion Engine Registry
CREATE TABLE bot_context_suggestion_templates (
    suggestion_id VARCHAR(64) PRIMARY KEY,
    left_rail_context VARCHAR(32) NOT NULL CHECK (left_rail_context IN (
        'LOUNGE','OUTLET_SUMMARY','COMPLIANCE_LEGAL','PROJECTS_ASSETS_MAINT',
        'NPL_PATISSERIE','PEOPLE_MASTER_DATA','TASKS_CHECKLISTS'
    )),
    route_prefix VARCHAR(64) NOT NULL,
    chip_order SMALLINT NOT NULL CHECK (chip_order BETWEEN 1 AND 8),
    label_template VARCHAR(160) NOT NULL,
    prompt_template TEXT NOT NULL,
    badge_count_sql_key VARCHAR(64),
    min_role_rank SMALLINT NOT NULL DEFAULT 20,
    requires_outlet_context_bool BOOLEAN NOT NULL DEFAULT FALSE,
    action_proposal_type VARCHAR(48),
    active_bool BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE (left_rail_context, chip_order)
);

CREATE TABLE search_bot_audit_log (
    turn_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    role_code VARCHAR(32) NOT NULL,
    left_rail_context VARCHAR(32) NOT NULL DEFAULT 'LOUNGE',
    clicked_suggestion_id VARCHAR(64) REFERENCES bot_context_suggestion_templates(suggestion_id),
    outlet_scope_json JSONB NOT NULL,
    active_filter_state_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    query_text TEXT NOT NULL,
    retrieved_entity_ids_json JSONB NOT NULL,
    cited_entity_ids_json JSONB NOT NULL,
    claim_verification_pass BOOLEAN NOT NULL,
    latency_ms INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE search_bot_action_proposals (
    proposal_id UUID PRIMARY KEY,
    turn_id UUID NOT NULL REFERENCES search_bot_audit_log(turn_id),
    user_id UUID NOT NULL,
    target_endpoint VARCHAR(120) NOT NULL,
    payload_json JSONB NOT NULL,
    status VARCHAR(24) NOT NULL DEFAULT 'DRAFT_PROPOSED' CHECK (status IN ('DRAFT_PROPOSED','CONFIRMED_EXECUTED','EXPIRED','CANCELLED')),
    confirmed_via_pin_at TIMESTAMPTZ
);

Automated Gate Evidence: `pytest tests/test_unified_search_and_bot.py` — 100% green across `< 120ms` p95 omnibox ranking, `O-048` 7-context sticky suggestion switching (`LOUNGE`, `OUTLET_SUMMARY`, `COMPLIANCE_LEGAL`, `PROJECTS_ASSETS_MAINT`, `NPL_PATISSERIE`, `PEOPLE_MASTER_DATA`, `TASKS_CHECKLISTS`), `FV-1..12` filter interpolation, cross-outlet negative RBAC isolation (`0` facet leak), prompt-injection refusal, claim-to-source mismatch rejection, and PIN-gated action execution.