The Trinity Beast — TBTS Data Dictionary

Translation Engine Tables
1. 1. translation_jobs — Translation job orchestration (36 columns)
2. 2. translation_job_events — Translation job audit log (8 columns)
3. 3. translation_quotes — Customer quotes, payment, requotes and refunds (61 columns)
4. 4. translation_parameters — Cost, pricing, refund and operational parameters (6 columns)
5. 5. bedrock_model_catalog — Multi-provider Bedrock model registry (12 columns)
6. 6. translation_tasks — Task Organizer parent-child orchestration (16 columns)
7. 7. translation_language_profiles — Per-language tuning with auto-tuning (18 columns, 127 rows)
8. 8. translation_supported_languages — The 127-language catalog (17 columns)
9. 9. translation_chunk_telemetry — Per-chunk translation metrics (23 columns)
Reference

List of Diagrams

Diagram 1.1 — Quotes, Jobs and Tasks
Diagram 1.2 — Languages, Profiles and Parameters

1. translation_jobs

Purpose: Translation job orchestration. Every batch of work the engine runs is a row here: the documents, the languages, the agent, the progress and the cost. A customer's paid quote becomes one parent job, and its one free retry becomes a second job pointing at the same quote, so the whole story of a purchase is two joins away.

36 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Internal UUID, generated by the database
`job_id`	`varchar(64)`	—	NO	Public job identifier, unique, used in every API response: 12 hex digits, a hyphen and 16 more (e.g., `019e4b8a37bc-1f2e3d4c5b6a7980`)
`state`	`varchar(20)`	queued	NO	Job state: `queued`, `running`, `finalizing`, `succeeded`, `partial`, `failed`, `cancelled`. There is no `completed` state; a fully delivered job is `succeeded`.
`submitted_at`	`timestamptz`	now()	NO	When the job was submitted
`started_at`	`timestamptz`	—	YES	When processing began
`completed_at`	`timestamptz`	—	YES	When the job reached a terminal state
`submitted_by`	`varchar(50)`	admin-api	YES	Which path created the job, e.g. `admin-api` (direct submission), `retry-failed` (admin retry) or `retry-customer` (a customer's free retry)
`docs`	`jsonb`	—	NO	Array of document names in this job
`langs`	`jsonb`	—	NO	Array of target language codes, each already resolved through the 127-language catalog
`options`	`jsonb`	{}	YES	Job options (chunk-size overrides, `skip_validation`, routing). A retry inherits its parent's options.
`progress`	`jsonb`	{}	YES	Per-pair progress snapshot
`errors`	`jsonb`	[]	YES	Error details for failed pairs
`bedrock_cost_usd`	`numeric(8,4)`	0	YES	Total Bedrock cost of this job in USD
`bedrock_invocations`	`integer`	0	YES	Bedrock calls counted from successful pairs
`total_chunks`	`integer`	0	YES	Document chunks processed
`total_pairs`	`integer`	0	YES	Document-language pairs in this job
`succeeded_pairs`	`integer`	0	YES	Pairs delivered
`failed_pairs`	`integer`	0	YES	Pairs that failed
`elapsed_seconds`	`integer`	—	YES	Wall-clock duration in seconds
`step_function_arn`	`text`	—	YES	Legacy. The Step Functions pipeline was retired on June 27, 2026, so new jobs leave this empty.
`retry_of`	`varchar(64)`	—	YES	`job_id` of the job this one retries
`idempotency_key`	`varchar(128)`	—	YES	Client-provided idempotency key
`cloudfront_invalidation_ids`	`jsonb`	[]	YES	CloudFront invalidations issued for library documents
`search_index_rebuilt`	`boolean`	false	YES	Whether the search index was rebuilt after this job
`notification_sent`	`boolean`	false	YES	Whether the completion notice went out (prevents a second email)
`created_at`	`timestamptz`	now()	NO	Row creation time
`updated_at`	`timestamptz`	now()	NO	Last change
`reason`	`text`	—	YES	Cancellation or failure reason
`model`	`varchar(50)`	claude-sonnet-4.6	YES	The agent for the whole job (`qwen3-235b`, `deepseek-v3`, `mistral-large`, `claude-haiku-5.5`, `claude-sonnet-4.6`, `claude-opus-4.8`), or `per-language` when each language has its own agent. The column default is a legacy value: an admin retry now writes the original job's model, and a customer's free retry runs on the agent stored on the quote.
`execution_mode`	`varchar(30)`	realtime	YES	`realtime` or `batch`
`s3_input_uri`	`text`	—	YES	Batch inference input location
`s3_output_uri`	`text`	—	YES	Batch inference output location
`task_id`	`varchar(48)`	NULL	YES	Parent task in the Task Organizer (`translation_tasks.task_id`). NULL for a standalone job.
`cancelled_by`	`text`	—	YES	Who or what cancelled the job. `enqueue-failed` marks a job whose queue messages never went out, so it never started.
`quote_id`	`varchar(64)`	—	YES	The customer quote this job serves, set on the parent job and on its free retry. NULL for internal jobs. Backfilled by the migration for existing jobs.
`first_enqueued_at`	`timestamptz`	—	YES	Set at the first successful queue send. A job counts as started only from this moment, or once the worker reports, which is how a start failure is told apart from a slow start.

Indexes

translation_jobs_pkey — UNIQUE on id
translation_jobs_job_id_key — UNIQUE on job_id
idx_translation_jobs_state — on state
idx_translation_jobs_submitted_at — on submitted_at DESC
idx_translation_jobs_task_id — on task_id WHERE task_id IS NOT NULL
idx_translation_jobs_quote_id — on quote_id WHERE quote_id IS NOT NULL (new)

Why first_enqueued_at matters: a paid job that never reached the queue must be refunded, and a slow job must not be. The LPO writes the job row first, then sends one queue message per language, and stamps first_enqueued_at on the first send that succeeds. A job with no stamp and no worker report never started, and its quote enters the start-failure refund; a job with the stamp is simply in progress.

2. translation_job_events

Purpose: The job audit log. Each pair start, success, failure and rejection is a separate row, which is what lets the status endpoint rebuild per-pair progress from Aurora long after the live Valkey state has expired.

8 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Event identifier
`job_id`	`varchar(64)`	—	NO	Job this event belongs to (`translation_jobs.job_id`)
`event_type`	`varchar(30)`	—	NO	Event type, e.g. `lang_started`, `lang_succeeded`, `lang_failed`, `lang_rejected`
`doc`	`varchar(200)`	—	YES	Document name, for pair-level events
`lang`	`varchar(12)`	—	YES	Target language code. Widened from 5 to 12 characters so regional and script codes such as `zh-TW` and `fa-AF` fit.
`detail`	`jsonb`	{}	YES	Event detail. New values: `lang_rejected {"reason":"target_is_source"}` (the page is already in the target language, never retried); `lang_failed {"error":"enqueue-failed"}` (written by the LPO for languages that were never sent); `lang_failed {"error":"cancel-state-unknown"}` (the worker could not confirm the job was still live before publishing, so it refused to publish). `lang_started` now also records `source_provenance` and `lang_runtime_source`.
`created_at`	`timestamptz`	now()	NO	Event time
`metrics`	`jsonb`	—	YES	Per-pair metrics reported by the worker

Indexes

translation_job_events_pkey — UNIQUE on id
idx_translation_job_events_job_id — on job_id
idx_translation_job_events_created_at — on created_at DESC

3. translation_quotes

Purpose: Customer quotes and their Stripe payment. A quote prices up to 42 documents into up to 12 languages, holds that price for 72 hours, and remembers enough (fingerprints, per-pair prices, source languages) to re-check every document at acceptance and to refund any pair to the cent.

61 columns

The 61 columns read best in three groups: what was quoted, how the quote moved through its life, and how its money came back when something was not delivered. Rows with a green edge arrived with this release (migration 2026-10-09-03).

3.1 Quote, documents and price

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Internal identifier
`quote_id`	`varchar(64)`	—	NO	Public quote identifier: `qt-` plus the first 12 characters of a UUID (e.g., `qt-a1b2c3d4-e5f`). Unique.
`api_key_id`	`varchar(128)`	—	NO	API key that requested the quote. Dashboard reads now match every translation key of the same user.
`doc_url`	`text`	—	NO	URL of the first document (see `doc_urls` for all of them)
`doc_name`	`varchar(255)`	—	NO	Name of the first document
`doc_size_bytes`	`integer`	—	NO	Size of the document in bytes. Nothing over 300 KB (307,200 bytes) is ever quoted.
`estimated_chunks`	`integer`	—	NO	Estimated translation chunks
`difficulty`	`varchar(21)`	standard	NO	Difficulty: `standard`, `technical` or `complex`
`code_blocks`	`integer`	0	NO	Code blocks detected
`diagrams`	`integer`	0	NO	Mermaid diagrams detected
`langs`	`jsonb`	—	NO	Target languages, canonical codes after alias conversion and de-duplication (up to 12)
`lang_count`	`integer`	—	NO	Number of target languages
`cost_per_chunk`	`numeric(8,4)`	—	NO	Internal: Bedrock cost per chunk. Never shown to the customer.
`cost_per_pair`	`numeric(8,4)`	—	NO	Internal: blended price per pair. Never shown to the customer.
`infra_per_pair`	`numeric(8,4)`	—	NO	Internal: infrastructure cost per pair
`subtotal`	`numeric(10,4)`	—	NO	Internal: subtotal before markup
`markup_pct`	`integer`	—	NO	Markup percentage applied
`total_price`	`numeric(10,4)`	—	NO	The price the customer pays (USD)
`model`	`varchar(50)`	claude-sonnet-4.6	YES	The agent the customer chose, or `per-language` when the quote was auto-routed (the map is in `model_by_lang`). The column default is legacy; the quote handler always writes this column.
`execution_mode`	`varchar(20)`	express	NO	Delivery mode, `express` for every customer quote today
`protected_terms`	`jsonb`	[]	YES	The customer's brand terms, protected during translation
`created_at`	`timestamptz`	now()	NO	Quote creation time
`updated_at`	`timestamptz`	now()	NO	Last change

3.2 Lifecycle, documents, source language and requote

Column	Data Type	Default	Nullable	Description
`state`	`varchar(21)`	pending	NO	Lifecycle: `pending`, `accepted`, `expired`, `superseded`, `refunded`, `partially_refunded`. There is no CHECK on this column.
`expires_at`	`timestamptz`	—	NO	Quote expiry, 72 hours after creation (a requote gets its own 72 hours)
`accepted_at`	`timestamptz`	—	YES	When the customer accepted and paid
`declined_at`	`timestamptz`	—	YES	Legacy decline time
`stripe_payment_intent_id`	`varchar(128)`	—	YES	Stripe PaymentIntent for this quote
`stripe_charge_id`	`varchar(128)`	—	YES	Stripe Charge after payment
`job_id`	`varchar(64)`	—	YES	The parent translation job created at acceptance
`doc_urls`	`jsonb`	—	YES	Every document URL in the quote, trimmed and de-duplicated (up to 42)
`doc_count`	`integer`	1	NO	Number of documents
`total_pairs`	`integer`	0	NO	Documents × languages
`doc_names`	`jsonb`	—	YES	`{"<url>":"<name>"}`: the delivered file name of each document
`source_langs`	`jsonb`	—	YES	`{"<name>":{"lang":"<code>\|auto","provenance":"stated\|declared\|detected\|auto"}}`: each document's source language and how we know it
`doc_fingerprints`	`jsonb`	—	YES	`{"<name>":{"url","alg":"tbfp1","content_sha256","raw_size","translatable_size"}}`: the content fingerprint compared when the quote is accepted
`pair_prices`	`jsonb`	—	YES	`{"<name>":{"<lang>":3.0}}`: the price of every pair, so any refund is exact
`model_by_lang`	`jsonb`	—	YES	`{"<lang>":"<model>"}` when auto-routed; `{}` for an explicit agent
`superseded_by`	`varchar(64)`	—	YES	The newer quote that replaced this one (a requote, or a dashboard re-quote with `supersedes_quote_id`)
`requoted_from`	`varchar(64)`	—	YES	The older quote this one was created to replace
`accept_claimed_until`	`timestamptz`	—	YES	The accept claim. A second accept of the same quote inside this window is answered at once with `tbts_quote_accept_in_progress`, so one quote can never be charged twice.

How a stale quote is caught: at acceptance every document is fetched again and fingerprinted (tbfp1, a SHA-256 of the translatable content). Same content and same price: the job proceeds. Changed content at the same price: it proceeds with a notice. A new price or a rule change: the old quote is marked superseded, a new one is made (requoted_from points back), and accept answers 409 tbts_quote_requoted. A page that grew past 300 KB is refused with 400 tbts_doc_too_large and no new quote.

3.3 Refunds, cancellation and the free retry

Column	Data Type	Default	Nullable	Description
`refunded_at`	`timestamp`	—	YES	When the last refund was issued
`refund_amount`	`numeric(10,2)`	NULL	YES	Amount of the last refund (USD)
`refund_id`	`varchar(255)`	NULL	YES	Stripe Refund ID of the last refund
`refunded_total`	`numeric(10,2)`	0	NO	Everything refunded on this quote so far, from every kind of refund (cancel, admin, failed pairs). Backfilled from `refund_amount` for existing refunded quotes.
`cancelled_at`	`timestamptz`	—	YES	When the customer cancelled
`cancelled_by`	`text`	—	YES	Who cancelled
`refund_due`	`numeric(10,2)`	—	YES	Cancel refund awaiting approval
`refund_status`	`text`	—	YES	Cancel refund state (the approval flow)
`failed_refund_status`	`text`	—	YES	The failed-pair refund machine: `window_open`, `retrying`, `due`, `issuing`, `issued`, `none`, `error` (CHECK `tq_failed_refund_status_check`)
`failed_refund_trigger`	`text`	—	YES	`start_failed` (the paid job never started) or `job_terminal` (the job finished with failed pairs)
`failed_refund_window_ends_at`	`timestamptz`	—	YES	End of the 72-hour window in which the customer can use the free retry
`failed_refund_due`	`numeric(10,2)`	—	YES	Amount due for the undelivered pairs
`failed_refund_amount`	`numeric(10,2)`	—	YES	Amount actually refunded
`failed_refund_pairs`	`jsonb`	—	YES	The refunded document-language pairs
`failed_refund_id`	`text`	—	YES	Stripe Refund ID (idempotency key `txfr-{quote_id}`, so it can never be sent twice)
`failed_refund_at`	`timestamptz`	—	YES	When the failed-pair refund was issued
`failed_refund_attempts`	`integer`	0	NO	Stripe attempts so far (the limit is `refund.failed_pair_max_attempts`)
`failed_refund_last_error`	`text`	—	YES	Last Stripe error, for the operator
`retry_job_id`	`text`	—	YES	The job of the one free retry
`retry_count`	`integer`	0	NO	Free retries used (at most one)

Indexes

translation_quotes_pkey — UNIQUE on id
translation_quotes_quote_id_key — UNIQUE on quote_id
idx_quotes_api_key — on api_key_id
idx_quotes_expires — on expires_at WHERE state = 'pending'
idx_quotes_state — on state
idx_quotes_failed_refund_active — on failed_refund_status WHERE it is window_open, retrying, due or issuing (new: the 15-minute refund sweeper reads only live rows)

One quote, one refund ledger. Cancel refunds, admin refunds and failed-pair refunds all add to refunded_total, and every refund is clamped to what is left, so a pair can never be refunded twice. The automatic failed-pair refund (failed_refund_*) runs behind refund.failed_pair_enabled, switched on 2026-10-11; the free retry (retry_job_id, retry_count) and the start-failure refund work from deploy.

4. translation_parameters

Purpose: Dedicated parameter store for the translation engine — separated from the general application_parameters table because translation is a standalone product with its own pricing, cost controls, and operational config. Cached in Valkey as the tx:params hash (27-hour TTL, so it always outlives the nightly refresh). Every parameter that governs translation behavior — per-token Bedrock rates, cost caps, markup percentages, batch config, and token estimation factors — lives here. Every LPO container reads Valkey first, then Aurora, then a hard-coded floor that is never zero, so a parameter change needs no code deploy: run POST /admin/translate/params/reload or let the nightly sync carry it.

Bedrock rates refresh themselves every night. The nightly sync job (BeastReconciler) reads each model's token rates from the public AWS Price List (pricing:GetProducts, priced for us-east-2), checks that every value is plausible, writes any change to the bedrock_pricing rows here, then copies the table into tx:params and confirms both stores agree. A failed fetch, a missing value or a jump of more than 3× in one night keeps that model's stored rates and raises one ops alert, so a bad night can never reprice a quote. Aurora stays the source of truth: Valkey only ever receives what Aurora holds. Keys follow bedrock.<rate key>.realtime.input_per_1m, with rate keys qwen3235b, deepseekv3, mistrallarge, haiku55, sonnet46, opus48 and opus5 (stored so a later switch needs no new data).

6 columns · 46 rows captured live on 2026-10-10, plus 6 refund rows from this release

Column	Data Type	Default	Nullable	Description
`key` PK	`varchar`	—	NO	Parameter key in dot-notation (e.g., `bedrock.sonnet46.realtime.input_per_1m`, `refund.failed_pair_enabled`)
`value`	`text`	—	NO	Parameter value (numeric stored as text for flexibility)
`description`	`text`	''	NO	Human-readable explanation of what this parameter controls
`category`	`varchar`	general	NO	Grouping category: `bedrock_pricing`, `token_estimation`, `cost_controls`, `customer_pricing`, `batch_config`, `refund`
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp
`updated_by`	`varchar`	system	NO	Who last changed this parameter (admin email, sync-job, kiro)

Category	Rows	What It Controls
`bedrock_pricing`	~30	Per-token rates for each model × mode (realtime input/output, batch input/output, cache read/write). Drives all cost calculations.
`token_estimation`	~12	Tokens-per-byte ratio, output/input multiplier, cache hit rate, speed factors per model. Used to estimate job cost before submission.
`cost_controls`	~9	Daily spend cap ($600) and the daily token limit, both checked before a customer is charged. `controls.max_docs_per_request` is now '42', a mirror of the code constant and not read for enforcement: the real limits (12 languages, 42 documents, 300 KB, 9 quotes per batch) are constants in `internal/txlimits`.
`customer_pricing`	~9	Markup percentage (30%), infra cost per pair, tier discount factors, quote validity (72 hours).
`batch_config`	~6	S3 bucket for batch inference, IAM role ARN, polling interval (33s), JSONL processing config.
`refund`	6	The failed-pair refund machine: `refund.failed_pair_enabled` (on since 2026-10-11; code default 'false'), `refund.failed_pair_window_hours` (72), `refund.failed_pair_max_attempts` (3), `refund.sweep_interval_minutes` (15, a multiple of 3 clamped to 3–60), `refund.failed_pair_since` (set in the same statement that turns the flag on) and `refund.start_failure_since` (stamped by migration 03b at deploy).

5. bedrock_model_catalog

Purpose: Registry of all Bedrock models available to the translation engine. The agent-agnostic architecture reads this catalog to resolve friendly model names to Bedrock model IDs, determine regional availability, and select inference types (realtime vs batch). Enables adding new models (or blocking broken ones) without code changes.

Corrected 2026-08-12 — no lookupModelID() function exists. This entry previously claimed that function consulted the catalog to resolve models and enforce blocked regions. It doesn't exist anywhere in the codebase, and nothing reads this table's blocked=true rows — they document intent, not a control. Region blocking (specifically, the hard rule that no Canadian region is ever used) is actually enforced by FAILOVER_REGIONS in tbi-translate-worker/translator/engine.py being a fixed allow-list that simply never contains a Canadian region, backed by an import-time assertion that fails fast if one is ever added. See Trinity-Beast-TBI-Translation-Engine.html §12.1 for the real mechanism. Model-ID resolution for an active job reads directly from this table's live rows via ordinary SQL — there is no dedicated named function performing that lookup.

12 columns · 20 rows

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Auto-incrementing identifier
`friendly_name`	`varchar`	—	NO	Human-friendly model name used in API requests (e.g., `claude-sonnet-4.6`, `qwen3-235b`)
`display_name`	`varchar`	—	NO	Marketing display name (e.g., "Claude Sonnet 4.6", "Qwen3 235B")
`bedrock_model_id`	`varchar`	—	NO	Full Bedrock model ARN/ID (e.g., `us.anthropic.claude-sonnet-4-6`)
`region`	`varchar`	—	NO	AWS region where this model is invoked. HARD RULE: `ca-central-1` and `ca-west-1` are NEVER used.
`inference_type`	`varchar`	—	NO	Invocation method: `realtime` (InvokeModel) or `batch` (CreateModelInvocationJob)
`priority`	`integer`	1	NO	Preference order when multiple entries exist for the same `friendly_name` (lower = preferred). Used for regional failover.
`enabled`	`boolean`	true	NO	Whether this catalog entry is active. Disabled entries are skipped during model resolution.
`notes`	`text`	''	NO	Admin notes (why it's blocked, performance observations, etc.)
`verified_at`	`timestamptz`	now()	NO	When this model was last verified to be responding correctly in its region
`created_at`	`timestamptz`	now()	NO	When this catalog entry was created
`blocked`	`boolean`	false	NO	Hard block — entries with `blocked=true` are documentation of regions/models that must NEVER be used (CA regions). Unlike `enabled=false` (temporarily off), blocked entries represent permanent policy constraints.

Format Families

The translation engine routes models through one of three format families based on their prefix. Each family has its own request/response JSON structure — the engine handles this transparently via DetectFamily(), BuildRequestBody(), and ParseResponseText() in internal/bedrock/formats.go:

Family	Prefix Match	Models
`anthropic`	`anthropic.`, `us.anthropic.`	Claude Haiku 5.5, Claude Sonnet 4.6, Claude Opus 4.8
`openai`	`qwen.`, `deepseek.`, `mistral.*`	Qwen3 235B, DeepSeek V3, Mistral Large
`nova`	`amazon.nova.*`	(Reserved — Nova Pro removed from active roster due to HTML tag dropping)

Blocked regions (permanent policy): ca-central-1 and ca-west-1 have blocked=true entries in this catalog as a record of the policy. The enforcement is the worker's fixed FAILOVER_REGIONS allow-list, which never contains a Canadian region and is backed by an import-time assertion (see the correction above). This is a hard architectural constraint, not a temporary disable.

6. translation_tasks

Purpose: Task Organizer — parent-child orchestration for translation jobs. A Task is the unit above a Job: 1 customer acceptance = 1 Task = N Jobs. One notification per Task (not per Job) when all Jobs reach terminal state. Tracks task lifecycle, customer email for notification routing, and quote linkage for the payment flow. A customer polls a batch with GET /translate/task/{task_id}.

16 columns

Column	Data Type	Default	Nullable	Description
`task_id` PK	`varchar(48)`	—	NO	Public task identifier
`task_type`	`varchar(24)`	admin_batch	NO	`acceptance` (a customer's batch acceptance) or `admin_batch` (internal)
`state`	`varchar(24)`	queued	NO	Task state: queued, running, succeeded, partial, failed, cancelled
`customer_email`	`varchar(255)`	—	YES	Where the one task-level completion email goes (acceptance tasks)
`quote_ids`	`jsonb`	[]	NO	Quotes bundled into this task (up to 9)
`job_ids`	`jsonb`	[]	NO	Child jobs of this task
`total_jobs`	`integer`	0	NO	Child jobs submitted
`completed_jobs`	`integer`	0	NO	Jobs that succeeded
`failed_jobs`	`integer`	0	NO	Jobs that failed
`cancelled_jobs`	`integer`	0	NO	Jobs that were cancelled
`notification_sent`	`boolean`	false	NO	Prevents a second completion email
`submitted_at`	`timestamptz`	now()	NO	When the task was created
`completed_at`	`timestamptz`	—	YES	When every child job reached a terminal state
`created_at`	`timestamptz`	now()	NO	Row creation time
`updated_at`	`timestamptz`	now()	NO	Last change
`recorded_job_ids`	`text[]`	ARRAY[]	YES	Jobs already counted, so a repeated completion never counts twice

Indexes

translation_tasks_pkey — UNIQUE on task_id

Aurora Functions

create_translation_task(task_id, task_type, customer_email, quote_ids, job_ids, total_jobs) — Creates the task record
get_task_status(task_id) — Returns task + all jobs in one call (JOIN)
get_active_tasks() — Returns all non-terminal tasks (poll loop query)
record_task_job_completed(task_id, job_id, job_state) — Atomic counter update + "all done?" boolean
mark_task_notified(task_id) — Idempotent notification flag set
cancel_task_remaining(task_id, reason) — Cancel all pending jobs, close task

Relationship: translation_jobs.task_id is a nullable link column (with a partial index on non-NULL values) from each child job to its parent task; it is not a declared foreign key. Jobs created outside the Task Organizer (direct POST /admin/translate) have task_id = NULL.

7. translation_language_profiles

Purpose: Per-language tuning: chunk size, tag limits, retries, temperature and the auto-tuned density knobs. The 11 hand-tuned profiles of our own library are untouched; the release seeds 116 more by copying each new language's tuning_lang row, so every one of the 127 languages starts from proven settings instead of guesses.

18 columns · 127 rows

Column	Data Type	Default	Nullable	Description
`lang_code` PK	`varchar(12)`	—	NO	Language code. Widened from 5 to 12 characters for codes such as `zh-TW`.
`display_name`	`varchar(50)`	—	NO	Language name
`script_family`	`varchar(20)`	—	NO	Script family (latin, cjk, indic, arabic, cyrillic, ...). Drives preprocessor density and chunk sizing.
`max_chunk_kb`	`integer`	15	NO	Maximum chunk size in KB for this language
`max_code_tags`	`integer`	81	NO	Maximum code tags per chunk before a forced split (a multiple of 3)
`token_expansion_factor`	`numeric(3,2)`	1.00	NO	Expected output/input token ratio
`max_retries`	`integer`	1	NO	Retry attempts per chunk on validation failure
`retry_chunk_shrink_pct`	`integer`	30	NO	How much smaller a retried chunk is (30 = 70% of the original)
`model_override`	`varchar(100)`	NULL	YES	Engine-level model override for this language
`temperature`	`numeric(2,1)`	0.3	NO	Bedrock temperature for this language
`max_output_tokens`	`integer`	4096	NO	Output token ceiling per Bedrock call
`priority`	`integer`	6	NO	Processing priority (lower runs first). The 116 seeded rows use 12.
`enabled`	`boolean`	true	NO	Whether this profile is active
`notes`	`text`	—	YES	Operational notes. Seeded rows say which tuning row they were copied from.
`updated_at`	`timestamptz`	now()	NO	Last change
`updated_by`	`varchar(100)`	system	NO	Who last changed the row
`density_lift_threshold`	`numeric(4,3)`	NULL	YES	Auto-tuned. Density ratio above which a container is lifted whole. Tightened by telemetry when the tag drop rate passes 3%. Floor 0.015.
`density_max_prose`	`integer`	—	YES	Auto-tuned. Most prose characters a container may hold and still be lifted

Indexes

translation_language_profiles_pkey — UNIQUE on lang_code

Auto-tuning: the worker's _auto_tune_density_threshold() reads the last 7 days of translation_chunk_telemetry (code tags in versus out) and tightens the density threshold for a language that keeps dropping tags: 3–6% drop rate → -0.005, 6–12% → -0.01, above 12% → -0.015, never below 0.015. It runs once per container lifetime per language.

Aurora functions: get_language_profile(p_lang) returns one profile row and is kept unchanged for compatibility. The worker now reads get_language_runtime(p_lang) (section 8), which joins the profile to its catalog row.

8. translation_supported_languages

Purpose: The language catalog: the single list of the 127 languages a customer can order. Every quote, the admin submit path and the public GET /translate/languages resolve codes against it, which is why an unsupported code can never be quoted, charged and silently mistranslated. The rows are generated from one file, migrations/data/translation_language_catalog.tsv: the 75 Amazon Translate languages together with the 119 of Qwen 3.

17 columns · 127 rows

Column	Data Type	Default	Nullable	Description
`lang_code` PK	`varchar(10)`	—	NO	Canonical code, e.g. `es`, `zh-TW`, `fa-AF`
`english_name`	`varchar(120)`	—	NO	English name (e.g., Persian, Tagalog (Filipino))
`source`	`varchar(40)`	aws_translate	NO	Where the row first came from. A re-run of the catalog migration never changes it.
`status`	`varchar(20)`	supported	NO	`supported`, `blocked` or `unverified` (CHECK). An admin's choice here always survives a re-run.
`notes`	`text`	—	YES	Admin notes
`created_at`	`timestamptz`	now()	NO	Row creation time
`updated_at`	`timestamptz`	now()	NO	Last change
`updated_by`	`varchar(120)`	system	NO	Who last changed the row
`native_name`	`varchar(96)`	—	YES	The language's own name for itself (Español, 日本語), shown in the dashboard picker
`script`	`varchar(4)`	—	YES	ISO 15924 script code (Latn, Arab, Hans, ...). CHECK `tsl_script_present` requires both script and native name.
`rtl`	`boolean`	false	NO	Written right to left. True for 15 languages; their delivered files are marked `dir="rtl"`.
`word_spaced`	`boolean`	true	NO	False for the 8 languages written without spaces (ja, zh, zh-TW, yue, th, lo, km, my), so the term-spacing check skips them
`base_lang`	`varchar(10)`	—	YES	The parent language of a regional form (`es-MX` → `es`). Foreign key `tsl_base_fk` to this table.
`tuning_lang`	`varchar(10)`	—	YES	Whose chunk limits and prompts this language borrows (`fa` → Urdu, `ko` → Japanese)
`provenance`	`varchar(48)`	manual	NO	Why the row exists: `aws_translate`, `qwen3` or both
`target_allowed`	`boolean`	true	NO	Whether the language can be ordered as a target (true for all 127)
`default_model`	`varchar(32)`	—	YES	The auto-routing agent for this language. `claude-sonnet-4.6` for am, ha, mn, ps and so; empty for the rest, which run on Qwen 3 235B. An admin's value survives a re-run.

Indexes

translation_supported_languages_pkey — UNIQUE on lang_code
idx_translation_supported_languages_status — on status

Aurora function get_language_runtime(p_lang) (new) returns 24 columns: the catalog row LEFT JOINed to its profile, matched case-insensitively. The worker calls it once per language and caches the answer for 15 minutes, falling back to a generated snapshot (translator/language_catalog_snapshot.json) if Aurora is unreachable, so a language never fails because a lookup did.

Aliases have no table, on purpose. The 36 alternate codes (nb → no, zh-Hant → zh-TW, iw → he, any ar-XX → Modern Standard Arabic ar) live in migrations/data/translation_language_aliases.tsv, are generated into the LPO (internal/txlangs/aliases_gen.go) and the worker snapshot, and are published in the aliases field of GET /translate/languages. They are converted quietly, before any limit is counted. A region tag that is neither a catalog code nor an alias, such as en-US or es-AR, is not in the file: the resolver reduces it to its base language separately. Arabic dialects keep their own codes (arz, apc, ...).

9. translation_chunk_telemetry

Purpose: One row per translated chunk attempt: size, tokens, sentinels, code tags and outcome. It is the evidence the auto-tuner learns from, and the first place to look when one language keeps failing on one document.

23 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Row identifier
`job_id`	`varchar(50)`	—	NO	Job the chunk belongs to
`doc`	`varchar(200)`	—	NO	Document name
`lang`	`varchar(12)`	—	NO	Target language. Widened from 5 to 12 characters.
`chunk_index`	`integer`	—	NO	Position of the chunk in the document
`attempt`	`integer`	1	NO	Attempt number for this chunk
`chunk_size_bytes`	`integer`	—	NO	Chunk size in bytes
`input_tokens`	`integer`	—	YES	Bedrock input tokens
`output_tokens`	`integer`	—	YES	Bedrock output tokens
`expansion_ratio`	`numeric(4,2)`	—	YES	Output/input token ratio actually observed
`sentinel_count`	`integer`	—	YES	Sentinels sent with the chunk
`sentinels_restored`	`integer`	—	YES	Sentinels found and restored in the answer
`sentinel_integrity`	`boolean`	—	YES	Whether every sentinel came back intact
`code_tags_in`	`integer`	—	YES	Code tags in the source chunk
`code_tags_out`	`integer`	—	YES	Code tags in the translated chunk (the auto-tuner compares the two)
`validation_passed`	`boolean`	—	YES	Whether the chunk passed validation
`validation_errors`	`jsonb`	—	YES	Validation failures, if any
`duration_ms`	`integer`	—	YES	Time spent on the chunk
`model_used`	`varchar(100)`	—	YES	Bedrock model ID that answered
`status`	`varchar(20)`	—	NO	Chunk outcome
`error_class`	`varchar(50)`	—	YES	Error category
`error_detail`	`text`	—	YES	Error text
`created_at`	`timestamptz`	now()	NO	Row creation time

Indexes

translation_chunk_telemetry_pkey — UNIQUE on id
idx_chunk_telemetry_job — on job_id
idx_chunk_telemetry_doc_lang — on doc, lang
idx_chunk_telemetry_lang — on lang, status

Entity Relationships

Two pictures tell the whole schema. The first follows the money and the work: a quote becomes a job, a job writes events and telemetry, and a batch groups quotes under one task. The second is the configuration every job reads: which languages exist, how each one is tuned, what each model costs.

Diagram 1.1 — Quotes, Jobs and Tasks

erDiagram
    translation_tasks ||--o{ translation_jobs : "task_id (1 task = N jobs)"
    translation_quotes ||--o{ translation_jobs : "quote_id (parent job + free retry)"
    translation_quotes ||--o| translation_quotes : "superseded_by / requoted_from"
    translation_jobs ||--o{ translation_job_events : "job_id (audit trail)"
    translation_jobs ||--o{ translation_chunk_telemetry : "job_id (per-chunk metrics)"
    translation_quotes {
        varchar quote_id UK
        varchar job_id
        varchar retry_job_id
        varchar superseded_by
        text failed_refund_status
        numeric refunded_total
    }
    translation_jobs {
        varchar job_id UK
        varchar quote_id
        varchar task_id
        varchar retry_of
        timestamptz first_enqueued_at
    }
    translation_tasks {
        varchar task_id PK
        jsonb quote_ids
    }

Diagram 1.2 — Languages, Profiles and Parameters

erDiagram
    translation_supported_languages ||--o| translation_language_profiles : "lang_code"
    translation_supported_languages |o--o{ translation_supported_languages : "base_lang (regional form)"
    translation_supported_languages ||--o{ translation_jobs : "every language resolved here"
    translation_language_profiles ||--o{ translation_jobs : "per-language tuning"
    bedrock_model_catalog ||--o{ translation_jobs : "model selection"
    translation_parameters ||--o{ translation_jobs : "rates, caps, refund rules"
    translation_supported_languages {
        varchar lang_code PK
        bool rtl
        bool word_spaced
        varchar base_lang FK
        varchar tuning_lang
        varchar default_model
    }
    translation_language_profiles {
        varchar lang_code PK
        int max_chunk_kb
        numeric density_lift_threshold
    }

Key Relationships

translation_quotes.job_id → translation_jobs.job_id (the parent job, set at acceptance)
translation_jobs.quote_id → translation_quotes.quote_id (new: set on the parent job and on its free retry, so both directions are one lookup)
translation_quotes.retry_job_id → translation_jobs.job_id (new: the one free retry)
translation_quotes.superseded_by / requoted_from → translation_quotes.quote_id (new: a requote and the quote it replaced)
translation_jobs.task_id → translation_tasks.task_id (link column, partial index on non-NULL)
translation_job_events.job_id and translation_chunk_telemetry.job_id → translation_jobs.job_id
translation_supported_languages.lang_code → translation_language_profiles.lang_code, and base_lang → lang_code of the same table (foreign key tsl_base_fk)

Valkey Cache Keys

Purpose: Aurora is the permanent ledger and Valkey is the fast read. Live jobs are read from Valkey first and completed jobs fall back to Aurora, and every key here can be lost without losing anything: the system keeps working on Aurora alone.

Key Pattern	Type	TTL	Content
`tx:job:{id}`	Hash	None while live; 6 days once the job ends	Live job state (status, progress, per-pair results). The customer status endpoints read it first and fall back to Aurora.
`tx:active`	Set	Managed	Currently running job IDs (at 12 or more the LPO logs a warning and still queues; nothing caps concurrency)
`tx:history`	List	None (capped)	Completed job summaries, newest first
`tx:idempotency:{key}`	String	24h	Idempotency deduplication
`tx:params`	Hash	27 h	`translation_parameters` cache, refreshed nightly by the sync job (Bedrock rates included) and on reload
`tx:supported_langs`	Hash	27 h	Language code → status, the compact form of the catalog
`tx:lang_catalog`	String (JSON)	27 h	The full 127-row catalog. Every LPO container also refreshes its copy in place every 15 minutes, so a language change reaches every container without a restart.
`tx:chunk-hash:*`	String	Permanent	Per-chunk content hashes (delta detection)
`dashboard:notifications:seen:user:{users.id}`	String	—	Dashboard notification badge, now per user across every translation key (one fallback read of the old per-key value)
`autoops:bedrock:spend:daily`	String	24h	Today's Bedrock translation spend ($)
`autoops:bedrock:tokens:input:daily`	String	24h	Today's input token count
`autoops:bedrock:tokens:output:daily`	String	24h	Today's output token count

Admin API Endpoints

Purpose: Admin endpoints for managing translation data. All require X-Admin-Key header.

Endpoint	Method	Purpose
`/admin/translate`	POST	Submit a translation job (up to 42 documents and 12 languages, or `"all"` for the internal 11)
`/admin/translate/status/{job_id}`	GET	Real-time per-pair progress
`/admin/translate/queue`	GET	Active queue with all running/queued jobs
`/admin/translate/health`	GET	System health (spend, active jobs)
`/admin/translate/history`	GET	Job history (last 50)
`/admin/translate/cancel/{job_id}`	POST	Cancel a running job
`/admin/translate/retry-failed/{job_id}`	POST	Retry only the failed pairs of a partial job, on the same model as the original job. On a customer job it uses the quote's one free retry.
`/admin/translate/params`	GET/POST	List all / upsert translation parameters
`/admin/translate/params/reload`	POST	Refresh the Valkey cache from Aurora
`/admin/translate/languages`	GET/POST	List the catalog / update one language (a new code needs its English name, native name, script and tuning language)
`/admin/translate/languages/reload`	POST	Refresh the catalog in place
`/admin/translate/refund/{quote_id}`	POST	Full or partial refund, allowed on `accepted` or `partially_refunded` quotes and clamped to what is left
`/admin/translate/refund-approve/{quote_id}`	POST	Approve a customer's cancel refund
`/admin/translate/refund-sweep`	POST	Run one pass of the 15-minute failed-pair refund sweeper now

Agents and languages for everyone: the agent list is the public GET /translate/models (there is no admin models route), and the live catalog with its limits and aliases is the public GET /translate/languages. Dashboard equivalents: GET/POST/DELETE /dashboard/api/translate/params, POST /dashboard/api/translate/params/reload — same functionality, different auth path (magic link session vs admin key).

Schema Summary

9 tables, 197 columns in total, plus the Task Organizer functions and two language functions (get_language_profile, get_language_runtime). The columns of eight tables were read from the live schema on 2026-10-10 and include the release migrations; bedrock_model_catalog was not part of that capture and keeps its earlier column list. Row counts change daily, so read them live rather than from a page.

Table	Columns	Purpose
`translation_jobs`	36	Job orchestration and state
`translation_job_events`	8	Granular audit trail
`translation_quotes`	61	Customer quotes, Stripe payment, requotes and refunds
`translation_parameters`	6	Operational configuration store
`bedrock_model_catalog`	12	Multi-provider model registry
`translation_tasks`	16	Parent-child task orchestration
`translation_language_profiles`	18	Per-language tuning + auto-tuning
`translation_supported_languages`	17	The 127-language catalog
`translation_chunk_telemetry`	23	Per-chunk metrics

Related documents:

TBI Translation Engine — Architecture, sentinel system, validators, worker pattern, cost protection
Translation Service — Customer-facing product: languages, pricing, limits, retries and refunds
Translation Parameters — Full parameter reference with descriptions
Aurora Data Dictionary — Complete schema for every other table

The Trinity Beast — TBTS Data Dictionary

Table of Contents

List of Diagrams

1. translation_jobs

Indexes

2. translation_job_events

Indexes

3. translation_quotes

3.1 Quote, documents and price

3.2 Lifecycle, documents, source language and requote

3.3 Refunds, cancellation and the free retry

Indexes

4. translation_parameters

Categories

5. bedrock_model_catalog

Format Families

6. translation_tasks

Indexes

Aurora Functions

7. translation_language_profiles

Indexes

8. translation_supported_languages

Indexes

9. translation_chunk_telemetry

Indexes

Entity Relationships

Key Relationships

Valkey Cache Keys

Admin API Endpoints

Schema Summary