-- project_memory: durable, typed project knowledge with optional embeddings.
CREATE TABLE project_memory (
    id              uuid PRIMARY KEY,
    project_id      uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    workspace_id    uuid NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    kind            text NOT NULL CHECK (kind IN ('decision','convention','file_map','gotcha')),
    title           text NOT NULL,
    body            text NOT NULL,
    tags            text[] NOT NULL DEFAULT '{}',
    source          text NOT NULL DEFAULT 'agent' CHECK (source IN ('agent','user','auto')),
    embedding       vector(1536) NULL,
    embedding_model text NULL,
    created_by      uuid REFERENCES users(id),
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_project_memory_proj ON project_memory (project_id, kind);
CREATE INDEX idx_project_memory_embedding ON project_memory USING hnsw (embedding vector_cosine_ops)
    WHERE embedding IS NOT NULL;
CREATE INDEX idx_project_memory_tags ON project_memory USING gin (tags);