76c867fca4
- shared/ = portable Android-origin sources vendored from deferred/desktop-server (app/build.gradle.kts srcDir repointed; PlaybackController.kt excluded as Android-only) - backend/ = bundled-lite engine (SQLite + inline queue); .venv symlinked from the old checkout, PYTHONPATH pins THIS backend's code over any editable install - repoRoot() resolves this project dir (env SHONAR_REPO still wins); desktop-dev.sh watches shared/ + backend/ - Verified: :app:compileKotlin + :app:test green (23 tests); engine boots on :8010, self-migrates, /healthz ok
86 lines
3.1 KiB
Python
86 lines
3.1 KiB
Python
"""full-text search columns (PostgreSQL tsvector)
|
|
|
|
Revision ID: fts0000000001
|
|
Revises: 0c938663a363
|
|
|
|
Generated (STORED) tsvector columns + GIN indexes for search across title,
|
|
transcript text, summary content, tags, and action items. The SearchBackend
|
|
protocol keeps this swappable for Meilisearch/OpenSearch later.
|
|
"""
|
|
from __future__ import annotations
|
|
|
|
from collections.abc import Sequence
|
|
|
|
from alembic import op
|
|
|
|
revision: str = "fts0000000001"
|
|
down_revision: str | None = "8d51af959ae4"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# tsvector is PostgreSQL-only. On SQLite (desktop bundled engine) full-
|
|
# text search is handled outside the DB (the app scans report files),
|
|
# so this migration is a no-op.
|
|
if op.get_context().dialect.name != "postgresql":
|
|
return
|
|
# recordings: title + notes + tag names are indexed; transcript/summary
|
|
# contribute via their own tables (joined at query time).
|
|
op.execute(
|
|
"""
|
|
ALTER TABLE recordings
|
|
ADD COLUMN search_vector tsvector
|
|
GENERATED ALWAYS AS (
|
|
setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(notes, '')), 'B')
|
|
) STORED;
|
|
"""
|
|
)
|
|
op.execute("CREATE INDEX ix_recordings_search ON recordings USING GIN (search_vector);")
|
|
|
|
op.execute(
|
|
"""
|
|
ALTER TABLE transcripts
|
|
ADD COLUMN search_vector tsvector
|
|
GENERATED ALWAYS AS (
|
|
setweight(to_tsvector('simple', coalesce(text, '')), 'B')
|
|
) STORED;
|
|
"""
|
|
)
|
|
op.execute("CREATE INDEX ix_transcripts_search ON transcripts USING GIN (search_vector);")
|
|
|
|
# summary content JSON -> extractive text for FTS. Generated columns
|
|
# forbid subqueries/set-returning functions, so we index the JSON body
|
|
# with punctuation stripped (covers key_points/decisions/action_items/
|
|
# questions) plus weighted short/detailed fields.
|
|
op.execute(
|
|
"""
|
|
ALTER TABLE summaries
|
|
ADD COLUMN search_vector tsvector
|
|
GENERATED ALWAYS AS (
|
|
setweight(to_tsvector('simple', coalesce(content->>'short', '')), 'A') ||
|
|
setweight(to_tsvector('simple', coalesce(content->>'detailed', '')), 'B') ||
|
|
setweight(
|
|
to_tsvector('simple',
|
|
regexp_replace(coalesce(content::text, ''), '[\\[\\]{}"]', ' ', 'g')), 'C')
|
|
) STORED;
|
|
"""
|
|
)
|
|
op.execute("CREATE INDEX ix_summaries_search ON summaries USING GIN (search_vector);")
|
|
|
|
op.execute(
|
|
"""
|
|
ALTER TABLE tags ADD COLUMN IF NOT EXISTS search_vector tsvector
|
|
GENERATED ALWAYS AS (to_tsvector('simple', coalesce(name, ''))) STORED;
|
|
"""
|
|
)
|
|
op.execute("CREATE INDEX ix_tags_search ON tags USING GIN (search_vector);")
|
|
|
|
|
|
def downgrade() -> None:
|
|
if op.get_context().dialect.name != "postgresql":
|
|
return
|
|
for table in ("tags", "summaries", "transcripts", "recordings"):
|
|
op.execute(f"DROP INDEX IF EXISTS ix_{table}_search;")
|
|
op.execute(f"ALTER TABLE {table} DROP COLUMN IF EXISTS search_vector;")
|