chaim
0d678da25f
Phase 6 of the multi-topic KB refactor (backend half). Subsumes the original Task #19 design discussion (subject vs labels). EspoCRM extension UI ships separately. Migrations (4 new, all idempotent, transaction-wrapped): 004_kind_tool — adds 'tool' to kb_source/kb_ingest_job CHECK 005_source_description — kb_source.description + ai_classified_at 006_labels — kb_label + kb_source_label (M:N), GRANTs 007_ingest_classifier — awaiting_review status + processing_stage + ai_suggestions JSONB + batch_id Two-phase ingest (api/services/kb/ingest_jobs.py): queued → processing(stage=classifying) → awaiting_review ← user reviews AI output → processing(stage=embedding) ← after user commits → done | failed process_classify_stage() pulls the file, runs parse_first_pages (3-page text extract), calls classifier.classify (Claude tool-call via ai-gateway, 45s timeout, 5-concurrent semaphore, fail-soft on any error → empty suggestions), writes ai_suggestions JSONB, transitions to awaiting_review. commit_job() resolves user-confirmed labels (existing by slug, new ones via slugify+ON CONFLICT DO NOTHING), transitions to processing(embedding), schedules process_embed_stage. process_embed_stage() runs the legacy ingest_source path with user-edited metadata + summary as kb_source.description, then apply_to_source for labels. discard_job() removes a file from a batch (status=failed, S3 deleted). list_jobs_by_batch() — single-roundtrip review-screen load. Labels (NEW api/services/kb/labels.py): Hebrew → ASCII slugify (letter-by-letter map, no external dep); CRUD; lookup_or_create_batch (race-safe); apply_to_source + replace_for_source maintain usage_count; merge race-safely (UPDATE assignments + ON CONFLICT DO NOTHING). Classifier (NEW api/services/kb/classifier.py): AsyncOpenAI to ai-gateway, Sonnet, OpenAI-style tool calling for guaranteed JSON shape, Hebrew system prompt with 5 kind values, citation format examples, "do not invent" rule, summary length bounds 80-200 chars. Routes (api/routes/admin_kb.py — 8 new on top of existing 11): POST /admin/kb/upload-batch (multi-file, AI classify) GET /admin/kb/batch/{batch_id} (review-screen load) POST /admin/kb/jobs/{id}/commit (user confirms metadata) POST /admin/kb/jobs/{id}/discard (user removes from batch) GET /admin/kb/labels (typeahead) POST /admin/kb/labels (explicit create) POST /admin/kb/labels/{id}/merge (admin cleanup) DELETE /admin/kb/labels/{id} (only if usage=0) Public /kb/search gains optional label_id filter. admin_sources.list_sources LEFT JOINs labels into each row's output. update_source accepts label_ids to replace the set. End-to-end smoke test passed: synthetic Hebrew circular → upload-batch → background classify → awaiting_review with kind, title, subject, summary, identifier, published_at all populated correctly. Refs Task Master #20 (espocrm-extensions/KnowledgeBase v0.8.0) Subsumes: Task #19 (labels for sub-topics) Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
44 lines
1.8 KiB
PL/PgSQL
44 lines
1.8 KiB
PL/PgSQL
-- Migration 007: kb_ingest_job gains AI-classification staging.
|
|
--
|
|
-- Task Master #20 (v0.8.0). The two-phase ingest pipeline introduced in
|
|
-- this release runs:
|
|
-- queued → processing(stage=classifying)
|
|
-- → awaiting_review (user reviews AI suggestions)
|
|
-- → processing(stage=embedding) (after user commits)
|
|
-- → done | failed
|
|
--
|
|
-- New columns:
|
|
-- • status gains 'awaiting_review' value
|
|
-- • processing_stage: 'classifying' | 'embedding' | 'upserting' (audit)
|
|
-- • ai_suggestions: JSONB written by the classifier; consumed by
|
|
-- the review screen as default form values
|
|
-- • batch_id: uuid string assigned by the upload route to all
|
|
-- files in one drag-drop drop. Lets the review
|
|
-- screen group N files even after a tab refresh.
|
|
--
|
|
-- Idempotent — wrapped in a single transaction.
|
|
--
|
|
-- Run as postgres superuser:
|
|
-- docker exec -i <pg-container> psql -U postgres -d insurance_kb \
|
|
-- -f /tmp/007_ingest_classifier.sql
|
|
|
|
BEGIN;
|
|
|
|
ALTER TABLE kb_ingest_job DROP CONSTRAINT IF EXISTS kb_ingest_job_status_check;
|
|
ALTER TABLE kb_ingest_job
|
|
ADD CONSTRAINT kb_ingest_job_status_check
|
|
CHECK (status IN ('queued','processing','awaiting_review','done','failed'));
|
|
|
|
ALTER TABLE kb_ingest_job
|
|
ADD COLUMN IF NOT EXISTS processing_stage TEXT,
|
|
ADD COLUMN IF NOT EXISTS ai_suggestions JSONB,
|
|
ADD COLUMN IF NOT EXISTS batch_id TEXT;
|
|
|
|
CREATE INDEX IF NOT EXISTS kb_ingest_job_batch_idx ON kb_ingest_job (batch_id);
|
|
-- Partial index keeps it tiny: production should have very few rows in
|
|
-- 'awaiting_review' at any moment (it's a transient state).
|
|
CREATE INDEX IF NOT EXISTS kb_ingest_job_awaiting_idx
|
|
ON kb_ingest_job (created_at DESC) WHERE status = 'awaiting_review';
|
|
|
|
COMMIT;
|