mirror of
https://github.com/hanzoai/team.git
synced 2026-09-30 01:57:35 +00:00
66b169b93c
* AutoTranslate config settings * comment out Agents provider * Add auto translate timeout config validation * i18n messages for autotranslation config validation * fix test * validate url for libreTranslate * Feedback review * Admin Console UI for Auto-Translation * fix admin console conditional section display * i18n * removed unintentional change * update admin.general.localization.autoTranslateProviderDescription newline * fix lint * Fix types * UX feedback review * fix typo in i18n * Fix AutoTranslation feature flag * feedback review * Fix test default values * feedback review * re-add isHidden property to feature discovery * Database Migrations, Indexes and Methods for Auto-Translation * i18n * fix retrylayer and storetest * Fix search query * fix lint * remove the request.CTX and modify Translation model * fix lint and external url * Add settings to playwright * Add empty as a valid value for the Provider * Update jsonb queries * Fix queries and add model methods * fix go lint * go lint fix 2 * fix db migrations * feedback review + store cache * increase migration number * cleanup autotranslation store cache * use NULL as objectType for posts * fix bad merge * fix tests * add missing i18n * Switch prop bags column to boolean * fix lint * fix tests * Remove database search * use Builder methods --------- Co-authored-by: BenCookie95 <[email protected]> Co-authored-by: Hanzo Dev <[email protected]>
42 lines
2.0 KiB
SQL
42 lines
2.0 KiB
SQL
-- Create translations table
|
|
CREATE TABLE IF NOT EXISTS translations (
|
|
objectId varchar(26) NOT NULL, -- object ID (e.g., post ID)
|
|
dstLang varchar NOT NULL, -- ISO code from user preference
|
|
objectType varchar NULL, -- e.g., 'post' (object-agnostic) if NULL assume "post"
|
|
providerId varchar NOT NULL, -- 'agents' | 'libretranslate' | ...
|
|
normHash char(64) NOT NULL, -- sha256 (hex) of normalized source
|
|
text text NOT NULL, -- translated text (rehydrated)
|
|
confidence real , -- provider confidence 0..1 (nullable)
|
|
meta jsonb , -- provider metadata (nullable)
|
|
updateAt bigint NOT NULL, -- epoch millis
|
|
PRIMARY KEY (objectId, dstLang)
|
|
);
|
|
|
|
-- Index for recency and lookup performance
|
|
CREATE INDEX IF NOT EXISTS idx_translations_updateat
|
|
ON translations (updateAt DESC);
|
|
|
|
-- Add autotranslation boolean column to channels table
|
|
ALTER TABLE channels
|
|
ADD COLUMN IF NOT EXISTS autotranslation boolean NOT NULL DEFAULT false;
|
|
|
|
-- Add autotranslation boolean column to channelmembers table
|
|
ALTER TABLE channelmembers
|
|
ADD COLUMN IF NOT EXISTS autotranslation boolean NOT NULL DEFAULT false;
|
|
|
|
-- Hot path index: Members opted in for a channel
|
|
-- Partial index only includes rows where autotranslation is enabled for performance
|
|
CREATE INDEX IF NOT EXISTS idx_channelmembers_autotranslation_enabled
|
|
ON channelmembers (channelid)
|
|
WHERE autotranslation = true;
|
|
|
|
-- Index for efficient channel autotranslation lookups
|
|
CREATE INDEX IF NOT EXISTS idx_channels_autotranslation_enabled
|
|
ON channels (id)
|
|
WHERE autotranslation = true;
|
|
|
|
-- Covering index for GetActiveDestinationLanguages query
|
|
-- Allows index-only scans when fetching user locales (avoids heap access)
|
|
CREATE INDEX IF NOT EXISTS idx_users_id_locale
|
|
ON users (id, locale);
|