spb/tools-spboucher-ai

1-- Add a keywords column (tags, use cases, category names, vendor) to the full-text vector at weight B.2ALTER TABLE tools ADD COLUMN IF NOT EXISTS keywords text NOT NULL DEFAULT '';3DROP VIEW IF EXISTS public_tools;4ALTER TABLE tools DROP COLUMN search_vector;5ALTER TABLE tools ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (6    setweight(to_tsvector('english', f_unaccent(coalesce(name,''))), 'A') ||7    setweight(to_tsvector('english', f_unaccent(f_join(aliases))), 'A') ||8    setweight(to_tsvector('english', f_unaccent(coalesce(summary,''))), 'B') ||9    setweight(to_tsvector('english', f_unaccent(coalesce(keywords,''))), 'B') ||10    setweight(to_tsvector('english', f_unaccent(f_join(capabilities))), 'C') ||11    setweight(to_tsvector('english', f_unaccent(coalesce(description,''))), 'D')12  ) STORED;13CREATE INDEX tools_search_idx ON tools USING gin(search_vector);14CREATE VIEW public_tools AS15  SELECT id, slug, name, aliases, vendor_id, homepage_url, docs_url, repo_url, pricing_url, license_url, privacy_url, security_url,16         logo_url, logo_attribution, product_type, product_status, summary, description, capabilities, workflows, advantages, limitations,17         prerequisites, audiences, comparison_notes, operating_systems, execution_modes, interfaces, deployment_models, integrations,18         data_formats, languages, source_model, license_spdx, pricing_model, pricing_checked_at, privacy_posture, data_residency,19         self_hostable, confidence, uncertainty_note, depth, quality_score, link_state, featured, reviewed_at, checked_at, published_at,20         updated_at, created_at, search_vector, embedding21  FROM tools WHERE visibility = 'public' AND editorial_status = 'published' AND duplicate_of IS NULL;22DO $$ BEGIN IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='tools_reader') THEN EXECUTE 'GRANT SELECT ON public_tools TO tools_reader'; END IF;23          IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='tools_app') THEN EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON public_tools TO tools_app'; END IF; END $$;24