8c9e5ee91d
Node.js MCP server exposing translate/search_tm/upsert_glossary/record_correction over StreamableHTTP on :9222. Routes translation calls to gemini-2.5-flash via LiteLLM, augmented with per-tenant TM + glossary + tone profile from the SmartTranslate pgvector DB. Schema migration in sql/001_schema.sql already applied to smarttranslate DB. Fleet registration in Infrastructure/litellm/config.yaml. Refs: CF-3122 CF-3123 CF-3124 CF-3125
84 lines
4.3 KiB
PL/PgSQL
84 lines
4.3 KiB
PL/PgSQL
-- CF-3124: add tenant discriminator + tenant_profile to SmartTranslate DB
|
|
-- Safe to run multiple times; migrations wrapped in IF NOT EXISTS.
|
|
-- Rollback at bottom of file (commented out).
|
|
|
|
BEGIN;
|
|
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|
|
|
-- translation_memory: tenant column
|
|
ALTER TABLE translation_memory
|
|
ADD COLUMN IF NOT EXISTS tenant VARCHAR(64);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_tm_tenant_lang
|
|
ON translation_memory (tenant, source_lang, target_lang);
|
|
|
|
-- glossary_terms: tenant column
|
|
ALTER TABLE glossary_terms
|
|
ADD COLUMN IF NOT EXISTS tenant VARCHAR(64);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_glossary_tenant_src
|
|
ON glossary_terms (tenant, LOWER(source_term), target_lang);
|
|
|
|
-- Per-tenant uniqueness. The existing uq_glossary_term_ci_global (WHERE customer_id
|
|
-- IS NULL) would collide across tenants for NULL-customer rows — redefine to exclude
|
|
-- tenant'd rows, and add a parallel partial unique index for tenant'd rows.
|
|
DROP INDEX IF EXISTS uq_glossary_term_ci_global;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_glossary_term_ci_global
|
|
ON glossary_terms (LOWER(source_term), target_lang, COALESCE(context_hint, ''))
|
|
WHERE customer_id IS NULL AND tenant IS NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_glossary_term_tenant
|
|
ON glossary_terms (tenant, LOWER(source_term), target_lang, COALESCE(context_hint, ''))
|
|
WHERE tenant IS NOT NULL;
|
|
|
|
-- tenant_profile: new table
|
|
CREATE TABLE IF NOT EXISTS tenant_profile (
|
|
tenant VARCHAR(64) PRIMARY KEY,
|
|
tone VARCHAR(32), -- professional / friendly / technical / marketing / editorial
|
|
formality VARCHAR(16), -- formal / informal / neutral
|
|
industry VARCHAR(64),
|
|
base_rules TEXT, -- free-form system prompt fragment
|
|
forbidden_terms TEXT[] DEFAULT '{}', -- e.g. {'AdGuardHome','Hetzner'} for vpn-marketing
|
|
preferred_author_voice TEXT, -- e.g. "wir/nous (author voice ok, never Sie/du/vous)"
|
|
direct_address_allowed BOOLEAN DEFAULT TRUE,
|
|
default_source_lang VARCHAR(8),
|
|
default_target_langs VARCHAR(8)[] DEFAULT '{}',
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
-- Seed known tenants (idempotent upsert on tenant PK)
|
|
INSERT INTO tenant_profile (tenant, tone, formality, industry, base_rules, forbidden_terms, preferred_author_voice, direct_address_allowed, default_source_lang, default_target_langs)
|
|
VALUES
|
|
('ifk', 'editorial', 'neutral', 'kids-education',
|
|
'Neutral, parent-facing tone. Refer to "Eltern", "das Kind", "les parents", "parents" rather than addressing the reader. Author voice "wir" / "nous" is OK. Do not append sales/CTA blocks; inline mentions only.',
|
|
'{}'::text[], 'wir/nous (author voice only)', false, 'en', ARRAY['de','fr']),
|
|
('vpn-marketing', 'marketing', 'neutral', 'consumer-vpn',
|
|
'Consumer privacy/VPN marketing. Describe blocklists qualitatively (curated HaGeZi/OISD/Cloudflare-top-10k allowlist, ~15-30k DE-optimized entries). Use "Cloud-Server in Deutschland" instead of naming the hoster.',
|
|
ARRAY['AdGuardHome','Hetzner'], NULL, true, 'en', ARRAY['de','fr']),
|
|
('clicksports.de', 'professional', 'formal', 'hosting-msp',
|
|
'B2B hosting/MSP tone. Always "Service" never "Support". Update-safe WHMCS context; avoid internal ticket jargon in customer-facing strings.',
|
|
ARRAY['Support'], NULL, true, 'de', ARRAY['en']),
|
|
('etoro', 'professional', 'neutral', 'retail-trading',
|
|
'Retail trading/copy-trading tone. Factual, compliance-aware. No exaggerated performance claims. Use official instrument names.',
|
|
'{}'::text[], NULL, true, 'en', ARRAY['de','fr','es','it']),
|
|
('matrixhost', 'friendly', 'neutral', 'matrix-hosting',
|
|
'Matrix chat hosting SaaS tone. Technical-but-approachable; family-first angle.',
|
|
'{}'::text[], NULL, true, 'en', ARRAY['de']),
|
|
('agiliton', 'professional', 'formal', 'msp',
|
|
'Agiliton corporate voice. Short sentences, German engineering precision.',
|
|
'{}'::text[], NULL, true, 'de', ARRAY['en'])
|
|
ON CONFLICT (tenant) DO NOTHING;
|
|
|
|
COMMIT;
|
|
|
|
-- ROLLBACK (run manually if needed):
|
|
-- BEGIN;
|
|
-- DROP TABLE IF EXISTS tenant_profile;
|
|
-- DROP INDEX IF EXISTS ix_glossary_tenant_src;
|
|
-- DROP INDEX IF EXISTS ix_tm_tenant_lang;
|
|
-- ALTER TABLE glossary_terms DROP COLUMN IF EXISTS tenant;
|
|
-- ALTER TABLE translation_memory DROP COLUMN IF EXISTS tenant;
|
|
-- COMMIT;
|