"""Search models and tables."""
from sqlalchemy import DDL, Column, Integer, String, DateTime, Text, ForeignKey
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.types import JSON
from basic_memory.models.base import Base
class SearchIndex(Base):
"""Search index table for Postgres only.
For SQLite: This model is skipped; FTS5 virtual table is created via DDL instead.
For Postgres: This is the actual table structure with tsvector support.
"""
__tablename__ = "search_index"
# Primary key (rowid in SQLite FTS5, explicit id in Postgres)
id = Column(Integer, primary_key=True, autoincrement=True)
# Core searchable fields
title = Column(Text, nullable=True)
content_stems = Column(Text, nullable=True)
content_snippet = Column(Text, nullable=True)
permalink = Column(String(255), nullable=True, index=True)
file_path = Column(Text, nullable=True)
type = Column(String(50), nullable=True)
# Project context
project_id = Column(Integer, nullable=True, index=True)
# Relation fields
from_id = Column(Integer, nullable=True)
to_id = Column(Integer, nullable=True)
relation_type = Column(String(100), nullable=True)
# Observation fields
# Note: FK with CASCADE only applies to Postgres. SQLite uses FTS5 virtual tables
# which don't support foreign keys, so cascade delete is handled explicitly there.
entity_id = Column(Integer, ForeignKey("entity.id", ondelete="CASCADE"), nullable=True)
category = Column(String(100), nullable=True)
# Common fields
# Use JSONB for Postgres, JSON for SQLite
# Note: 'metadata' is a reserved name in SQLAlchemy, so we use 'metadata_' and map to 'metadata'
metadata_ = Column("metadata", JSON().with_variant(JSONB(), "postgresql"), nullable=True)
created_at = Column(DateTime(timezone=True), nullable=True)
updated_at = Column(DateTime(timezone=True), nullable=True)
# Note: textsearchable_index_col (tsvector) will be added by migration for Postgres only
# Define FTS5 virtual table creation for SQLite only
# This DDL is executed separately for SQLite databases
CREATE_SEARCH_INDEX = DDL("""
CREATE VIRTUAL TABLE IF NOT EXISTS search_index USING fts5(
-- Core entity fields
id UNINDEXED, -- Row ID
title, -- Title for searching
content_stems, -- Main searchable content split into stems
content_snippet, -- File content snippet for display
permalink, -- Stable identifier (now indexed for path search)
file_path UNINDEXED, -- Physical location
type UNINDEXED, -- entity/relation/observation
-- Project context
project_id UNINDEXED, -- Project identifier
-- Relation fields
from_id UNINDEXED, -- Source entity
to_id UNINDEXED, -- Target entity
relation_type UNINDEXED, -- Type of relation
-- Observation fields
entity_id UNINDEXED, -- Parent entity
category UNINDEXED, -- Observation category
-- Common fields
metadata UNINDEXED, -- JSON metadata
created_at UNINDEXED, -- Creation timestamp
updated_at UNINDEXED, -- Last update
-- Configuration
tokenize='unicode61 tokenchars 0x2F', -- Hex code for /
prefix='1,2,3,4' -- Support longer prefixes for paths
);
""")