The Trinity Beast — Aurora Data Dictionary

Group 1 — Core: Users & Authentication
1. 1.1 api_keys — API key management for subscribers
2. 1.2 api_key_groups — API key capability group templates
3. 1.3 rate_limit_template — Rate limit presets by subscription tier
4. 1.3 users — Subscriber and donor accounts
Group 2 — Configuration
1. 2.1 application_parameter_profiles — System-mode parameter profiles
2. 2.2 application_parameters — Runtime configuration parameters
3. 2.3 report_parameters — LRS-specific configuration
Group 3 — Billing & Payments
1. 3.1 payment_links — Stripe Payment Link management for checkout
2. 3.2 transactions — Payment records
3. 3.3 tier_catalog — Product pricing catalog (single source of truth)
Group 4 — Usage Logging
1. 4.1 monthly_report_counts — Monthly report usage aggregation
2. 4.2 report_count — Per-API-key report limit tracking
3. 4.3 report_usage_logs — LRS report request log
4. 4.4 usage_logs — LPO API call log
5. 4.5 mv_usage_daily — Daily-rollup materialized view (Phase 6)
Group 5 — Exchange & Pricing
1. 5.1 asset_categories — Crypto asset category groupings
2. 5.2 exchange_asset_map — Exchange-to-asset symbol translations
3. 5.3 exchange_feeds — Exchange WebSocket feed configurations
Group 6 — Email & Newsletters
1. 6.1 email_drafts — Email composition drafts
2. 6.2 email_senders — Verified SES sender identities
3. 6.3 email_templates — SES email templates
4. 6.4 email_translations — Multi-language email template fields
5. 6.5 newsletter_subscribers — Newsletter mailing list
6. 6.6 newsletter_templates — Newsletter email templates
7. 6.7 newsletter_translations — Multi-language newsletter content
8. 6.8 newsletters — Sent newsletter campaigns
Group 7 — Support & Engagement
1. 7.1 demo_leads — Demo registration captures
2. 7.2 error_messages — Multi-language API error messages
3. 7.3 map_pins — CPMP mission impact map markers
4. 7.4 partner_applications — AWS Partner application submissions
5. 7.5 support_replies — Support ticket replies
6. 7.6 support_tickets — Customer support system
Group 8 — Operations & Maintenance
1. 8.1 cron_execution_logs — Cron job execution tracking
2. 8.2 cron_reset_logs — Cron job execution log (same schema as cron_execution_logs)
3. 8.3 cron_reset_summary — Reset summary statistics
Group 9 — Website Analytics
1. 9.1 page_events — Website interaction event tracking
2. 9.2 page_views — Website page view analytics
Group 10 — Webhook Push
1. 10.1 webhook_subscriptions — Webhook subscriber configurations and delivery state
2. 10.2 webhook_delivery_log — Webhook delivery attempt log
Group 11 — Translation Engine
1. 11.1 translation_jobs — Translation job orchestration
2. 11.2 translation_job_events — Translation job audit log
3. 11.3 translation_quotes — Customer-facing translation quotes with Stripe payment
Group 12 — Agent Profiles
1. 12.1 agent_profiles — UME agent identity registry
2. 12.2 agent_profile_events — Agent profile audit log

Logical Data Model — Full Database Diagram

Complete entity-relationship view of all 38 Aurora tables, organized into 11 domain groups. Each diagram shows related tables with their columns and foreign key relationships.

Identity & Access

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    users {
        uuid id PK
        text email UK
        text tier
        text name
        varchar preferred_lang
    }
    api_keys {
        uuid id PK
        uuid user_id FK
        text api_key UK
        text tier
        int query_limit
        int current_usage
        varchar api_lang
    }
    rate_limit_template {
        text tier PK
        int rate_limit_qps
        int burst_limit
        int query_limit
    }
    users ||--o{ api_keys : "owns"

Configuration

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    application_parameters {
        varchar key PK
        text value
        text description
    }
    application_parameter_profiles {
        uuid id PK
        text profile_name UK
        int qps
        int burst
        text log_level
    }
    report_parameters {
        varchar key PK
        text value
        text description
    }

Billing & Usage Tracking

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    transactions {
        uuid id PK
        text type
        text donor_email
        numeric amount_usd
        text stripe_charge_id UK
    }
    payment_links {
        int id PK
        text link_type
        text tier
        text stripe_url
    }
    usage_logs {
        uuid id PK
        text api_key_id
        text asset
        text source
        float8 price
    }
    report_count {
        uuid api_key PK
        int monthly_count
        int daily_count
    }
    report_usage_logs {
        uuid id PK
        text api_key_id
        text report_type
        int report_duration_ms
    }
    monthly_report_counts {
        text api_key PK
        bigint monthly_reports_counter
    }
    api_keys ||--o{ report_count : "tracks"
    api_keys ||--o{ usage_logs : "generates"
    api_keys {
        uuid id PK
        text tier
    }

Exchange & Pricing

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    exchange_feeds {
        int id PK
        text exchange_name UK
        text ws_endpoint
        boolean enabled
        text source_tag
    }
    exchange_asset_map {
        int id PK
        text exchange_name
        text asset
        text exchange_symbol
        boolean enabled
    }
    asset_categories {
        int id PK
        text category_name UK
        int display_order
        boolean enabled
    }

Email & Newsletters

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    newsletters {
        uuid id PK
        text title
        text type
        text status
    }
    newsletter_subscribers {
        uuid id PK
        text email
        text source
        varchar preferred_lang
    }
    newsletter_templates {
        uuid id PK
        text name
        text type
    }
    newsletter_translations {
        uuid id PK
        uuid newsletter_id FK
        varchar lang
    }
    email_templates {
        uuid id PK
        text name
        text topic
    }
    email_translations {
        uuid id PK
        text template_name
        varchar lang
        text field_name
    }
    email_drafts {
        uuid id PK
        text subject
        text status
    }
    email_senders {
        uuid id PK
        text display_name
        text email UK
    }
    newsletters ||--o{ newsletter_translations : "translated"

Support & Engagement

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    support_tickets {
        uuid id PK
        varchar ticket_number UK
        text email
        varchar status
        varchar preferred_lang
    }
    support_replies {
        uuid id PK
        uuid ticket_id FK
        text author
        varchar author_type
    }
    error_messages {
        int id PK
        varchar message_key
        varchar lang
        text message
    }
    demo_leads {
        uuid id PK
        text email
        text platform
    }
    map_pins {
        uuid id PK
        text type
        float8 lat
        float8 lng
    }
    partner_applications {
        uuid id PK
        text company_name
        text status
    }
    support_tickets ||--o{ support_replies : "has"

Operations & Maintenance

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    cron_execution_logs {
        uuid id PK
        text job_name
        text status
        int rows_affected
    }
    cron_reset_logs {
        uuid id PK
        text job_name
        text status
        int rows_affected
    }
    cron_reset_summary {
        uuid id PK
        text job_name
        text status
        int rows_affected
    }

Website Analytics

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    page_views {
        uuid id PK
        varchar page_path
        varchar country
        varchar device_type
        varchar visitor_id
    }
    page_events {
        uuid id PK
        varchar page_path
        varchar event_type
        varchar visitor_id
    }

Webhook Push Delivery

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    webhook_subscriptions {
        uuid id PK
        uuid api_key_id FK
        text tier
        text delivery_method
        int interval_seconds
        text status
    }
    webhook_delivery_log {
        bigint id PK
        uuid subscription_id FK
        text asset
        numeric price
        text status
        float8 latency_ms
    }
    webhook_subscriptions ||--o{ webhook_delivery_log : "logs"
    api_keys ||--o{ webhook_subscriptions : "subscribes"
    api_keys {
        uuid id PK
        text tier
    }

Translation Engine

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#334155', 'primaryTextColor': '#e2e8f0', 'primaryBorderColor': '#475569', 'lineColor': '#64748b', 'secondaryColor': '#1e293b', 'tertiaryColor': '#253347', 'background': 'transparent', 'mainBkg': '#334155', 'nodeBorder': '#475569'}}}%%
erDiagram
    translation_jobs {
        uuid id PK
        varchar job_id UK
        varchar state
        jsonb docs
        jsonb langs
        int total_pairs
        int succeeded_pairs
        int failed_pairs
        numeric bedrock_cost_usd
        varchar model
    }
    translation_job_events {
        bigint id PK
        varchar job_id
        varchar event_type
        varchar doc
        varchar lang
        jsonb detail
    }
    translation_jobs ||--o{ translation_job_events : "emits"

38 tables across 11 domain groups. Lines between entities represent foreign key relationships.

Group 1 — Core: Users & Authentication3 tables

1.1 api_keys

Purpose: API key management for all system actors. Stores every issued API key along with its capability flags, tier, rate limits, usage counters, Stripe billing references, and subscription state. Central table for authentication and authorization of all LPO/LRS API requests. The boolean capability flags are the authoritative source for behavioral routing — rate limiting, billing checks, product access, and translation service eligibility.

32 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique identifier for the API key record
`user_id` FK → users.id	`uuid`	—	`YES`	Owner of the key; references the users table. NULL for system/internal keys.
`key_hash`	`text`	—	`YES`	Legacy hash field (unused in current implementation)
`name`	`text`	—	`YES`	Subscriber name associated with this key
`tier`	`text`	free	`YES`	Subscription tier: free, pro, enterprise, unlimited, lifetime, partner, stress, standard, or translation tier variants. The tier drives rate limit values; the boolean flags drive behavioral routing.
`query_limit`	`integer`	1000	`YES`	Maximum number of API queries allowed per month
`current_usage`	`integer`	0	`YES`	Current month's query count; reset monthly by cron job
`last_used`	`timestamptz`	—	`YES`	Timestamp of the most recent API call using this key
`created_at`	`timestamptz`	now()	`YES`	When this API key was created
`revoked`	`boolean`	false	`YES`	Whether this key has been disabled/revoked
`rate_limit_qps`	`integer`	5	`YES`	Maximum queries per second allowed for this key
`burst_limit`	`integer`	20	`YES`	Token bucket burst capacity for rate limiting
`burst_tokens`	`float8`	20	`YES`	Current token balance in the rate-limit token bucket
`api_key`	`text`	encode(extensions.gen_random_bytes(32), 'hex')	NO	The actual API key string (plaintext); used for authentication
`last_success`	`timestamptz`	—	`YES`	Timestamp of the last successful (non-error) API call
`minimum_wait_seconds`	`float8`	'1'::double precision	`YES`	Minimum time between requests when the key is being throttled
`lrs_enabled`	`boolean`	false	`YES`	Whether unlimited LRS (Listener Report Service) reports are enabled
`response_format`	`text`	tbc	NO	Preferred response format: tbc (The Trinity Beast Canonical) or plain
`stripe_customer_id`	`text`	—	`YES`	Stripe customer ID used for billing portal and webhook event lookups
`stripe_subscription_id`	`text`	—	`YES`	Main LPO subscription ID in Stripe
`stripe_lrs_subscription_id`	`text`	—	`YES`	Separate LRS add-on subscription ID in Stripe
`subscription_status`	`text`	active	NO	Current subscription state: active, past_due, or canceled
`tier_effective_date`	`timestamptz`	—	`YES`	When the current tier took effect (for proration tracking)
`payment_failed_at`	`timestamptz`	—	`YES`	Timestamp of the first payment failure (triggers grace period logic)
`api_lang`	`varchar(5)`	en	NO	Preferred language for API response messages (UME Phase 2). Default: en
`is_system`	`boolean`	false	NO	Capability flag. Internal/tooling key — admin, demo, stress. No billing. No rate limits. No user account required.
`is_subscriber`	`boolean`	false	NO	Capability flag. REST API subscriber — any paying or free tier (free, pro, enterprise, unlimited, lifetime). Has a user account and Stripe record.
`is_partner`	`boolean`	false	NO	Capability flag. AWS PrivateLink / VPC peering partner. No rate limits. No billing. 60-minute API key cache.
`is_webhook`	`boolean`	false	NO	Capability flag. Webhook push product subscriber. Outbound price delivery at tier-configured intervals. Separate tier catalog from REST subscriptions.
`is_translation`	`boolean`	false	NO	Capability flag. Translation service customer. Files stored in `trinity-beast-translations` S3 bucket under `{api_key_id}/{lang}/{file}`. Has a customer registry in Valkey.
`is_rate_limited`	`boolean`	false	NO	Behavioral flag. Subject to QPS limits and monthly query caps. Hot path checks this single boolean — no tier string comparison needed. True for: free, pro, enterprise, webhook tiers.
`is_billing_exempt`	`boolean`	false	NO	Behavioral flag. Skip monthly cap checks entirely. True for: system keys, partner, unlimited, lifetime (paid upfront — no monthly caps). The billing check path is bypassed completely.

API Key Groups — Capability Flag Reference

Every API key belongs to one or more groups defined by its boolean capability flags. The flags are set at key creation and updated when a key's purpose changes. They are the authoritative source for behavioral routing — no tier string comparisons in application code.

Group	is_system	is_subscriber	is_partner	is_webhook	is_translation	is_rate_limited	is_billing_exempt	Purpose
System	✓	—	—	—	—	—	✓	Internal tooling — admin console, demo key, stress testing. No user account required. No billing. No rate limits.
Subscriber (rate-limited)	—	✓	—	—	—	✓	—	Paying REST API subscribers on metered tiers (free, pro, enterprise). Subject to QPS limits and monthly query caps.
Subscriber (exempt)	—	✓	—	—	—	—	✓	Subscribers who paid upfront (unlimited, lifetime). No monthly caps. No rate limits. Billing check path bypassed entirely.
Partner	—	—	✓	—	—	—	✓	AWS PrivateLink / VPC peering partners. No rate limits. No billing. 60-minute API key cache. We receive freely, we give freely.
Webhook	—	—	—	✓	—	✓	—	Webhook push product subscribers. Outbound price delivery at tier-configured intervals (3s–60s). Separate tier catalog. Rate-limited by push interval, not QPS.
Translation	—	—	—	—	✓	—	—	Translation service customers. Files stored in `trinity-beast-translations/{api_key_id}/{lang}/{file}`. Customer registry in Valkey. Optional delivery webhook URL.

Behavioral Rules

Check	Flag	Logic
Apply rate limiting?	`is_rate_limited`	If `true` → enforce QPS + monthly cap. If `false` → skip entirely.
Check monthly billing cap?	`is_billing_exempt`	If `true` → skip cap check. If `false` → check `current_usage` vs `query_limit`.
Allow translation service?	`is_translation`	If `true` → customer has a registry entry and S3 storage path.
Allow partner endpoints?	`is_partner`	If `true` → PrivateLink / VPC peering access, 60-min cache.
Allow webhook delivery?	`is_webhook`	If `true` → outbound push delivery active for this key.

Live Key Inventory (as of 2026-05-31)

Key (truncated)	Tier	Group	Owner
`tbcc-admin-fab798…`	admin	System	Internal — KCC admin console
`demo-public-2026-03-01-abc123`	demo	System	Internal — public demo key
`stress-test-unlimited-…`	stress	System	Internal — stress testing
`stress-lifetime-2026-05-01-run17`	lifetime	System	Internal — Run 17 stress test
`lpo-admin-5194ac1e…`	lifetime	Subscriber (exempt)	corydeankalani@cpmp-site.org
`lpo-pro-e3b2cd3b…`	pro	Subscriber (rate-limited)	Test account
`lpo-partner-07be921e…`	partner	Partner	Internal — partner test key
`lpo-webhook-289d463d…`	standard	Webhook	Test account

A key can hold multiple capability flags simultaneously. For example, a translation customer who also subscribes to the REST API would have both is_subscriber = true and is_translation = true.

Indexes

api_keys_api_key_key — UNIQUE on api_key
api_keys_pkey — UNIQUE on id
idx_api_keys_hash — on key_hash
idx_api_keys_stripe_customer_id — on stripe_customer_id WHERE stripe_customer_id IS NOT NULL
idx_api_keys_stripe_subscription_id — on stripe_subscription_id WHERE stripe_subscription_id IS NOT NULL
idx_api_keys_user — on user_id

Foreign Keys

user_id → users.id

1.2 api_key_groups

Purpose: API key capability group templates. Each row defines a named group with a preset combination of boolean capability flags. When a new API key is created, it is assigned a group_id and the flags are stamped onto the key from the group template in a single JOIN-based UPDATE. Adding a new capability to an entire group requires updating one row here — no per-key migrations. The group is the default; per-key flag overrides are still possible for exceptions.

12 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`serial`	auto	NO	Auto-increment group identifier. Referenced by `api_keys.group_id`.
`name`	`text`	—	NO	Unique machine-readable group name: `system`, `subscriber_metered`, `subscriber_unlimited`, `partner`, `webhook`, `translation`.
`display_name`	`text`	—	NO	Human-readable group name for UI and reports.
`description`	`text`	—	`YES`	Plain-English description of the group's purpose and behavioral rules.
`is_system`	`boolean`	false	NO	Template flag — internal/tooling keys. No billing. No rate limits.
`is_subscriber`	`boolean`	false	NO	Template flag — REST API subscriber with a user account and Stripe record.
`is_partner`	`boolean`	false	NO	Template flag — AWS PrivateLink / VPC peering partner.
`is_webhook`	`boolean`	false	NO	Template flag — webhook push product subscriber.
`is_translation`	`boolean`	false	NO	Template flag — translation service customer with S3 storage and Valkey registry.
`is_rate_limited`	`boolean`	false	NO	Template flag — subject to QPS limits and monthly query caps.
`is_billing_exempt`	`boolean`	false	NO	Template flag — skip monthly cap checks entirely (paid upfront or internal).
`enabled`	`boolean`	true	NO	Whether this group is available for assignment to new keys.
`created_at`	`timestamptz`	now()	NO	When this group was created.

Indexes

api_key_groups_name_key — UNIQUE on name
idx_api_key_groups_name — on name
idx_api_key_groups_enabled — on enabled

Seeded Groups (6 rows)

id	name	display_name	sys	sub	prt	web	tx	rl	be
1	`system`	System / Internal	✓	—	—	—	—	—	✓
2	`subscriber_metered`	Metered Subscriber	—	✓	—	—	—	✓	—
3	`subscriber_unlimited`	Unlimited Subscriber	—	✓	—	—	—	—	✓
4	`partner`	AWS Partner	—	—	✓	—	—	—	✓
5	`webhook`	Webhook Push	—	—	—	✓	—	✓	—
6	`translation`	Translation Customer	—	—	—	—	✓	—	—

rl = is_rate_limited · be = is_billing_exempt · sys/sub/prt/web/tx = product capability flags

The JOIN Pattern

To read a key with its full capability profile in one query:

SELECT k.api_key, k.tier, g.name AS group_name,
       g.is_rate_limited, g.is_billing_exempt, g.is_translation
FROM api_keys k
JOIN api_key_groups g ON k.group_id = g.id
WHERE k.api_key = $1

To apply a new capability to an entire group (e.g. give all partners access to a new feature):

-- 1. Add the column to api_key_groups (and api_keys for the cached copy)
ALTER TABLE api_key_groups ADD COLUMN is_new_feature BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE api_keys ADD COLUMN is_new_feature BOOLEAN NOT NULL DEFAULT false;

-- 2. Set the flag on the group
UPDATE api_key_groups SET is_new_feature = true WHERE name = 'partner';

-- 3. Stamp onto all existing keys in that group
UPDATE api_keys k SET is_new_feature = g.is_new_feature
FROM api_key_groups g WHERE k.group_id = g.id AND g.name = 'partner';

New keys automatically get the correct flags at creation time — the receipt Lambda does a JOIN-based UPDATE immediately after INSERT.

1.3 rate_limit_template

Purpose: Rate limit presets by subscription tier. Defines QPS, burst, query limits, and minimum wait configurations for each tier. Used by the Lambda receipt handler when creating new API keys and by the LPO server for runtime rate limiting. Single source of truth for all tier configurations.

7 columns

Column	Data Type	Default	Nullable	Description
`tier` PK	`text`	—	NO	Tier name (PK): free, pro, enterprise, unlimited, lifetime, partner, stress, or webhook_* variants
`rate_limit_qps`	`integer`	1	NO	Queries per second limit (0 = no limit for partner/unlimited/lifetime)
`burst_limit`	`integer`	5	NO	Token bucket burst capacity
`burst_tokens`	`float8`	5.0	NO	Initial token balance
`query_limit`	`integer`	1000	NO	Monthly query limit (999999999 = unlimited)
`min_wait_seconds`	`float8`	1.0	NO	Minimum wait between requests in seconds (0 = no wait)
`description`	`text`	—	`YES`	Human-readable description of the tier

Indexes

rate_limit_template_pkey — UNIQUE on tier

Current Tier Data (6 LPO + 5 Webhook = 11 rows)

Tier	Query Limit	QPS	Burst	Min Wait
LPO Subscription Tiers (6)
`free`	1,000	1	5	1.0s
`pro`	50,000	5	10	0.2s
`enterprise`	500,000	10	20	0.1s
`unlimited`	Unlimited	0 (none)	0 (none)	0 (none)
`lifetime`	Unlimited	0 (none)	0 (none)	0 (none)
`partner`	Unlimited	0 (none)	0 (none)	0 (none)
Webhook Associate Tiers (5)
`webhook_starter`	—	—	—	—
`webhook_standard`	—	—	—	—
`webhook_professional`	—	—	—	—
`webhook_enterprise`	—	—	—	—
`webhook_partner`	—	—	—	—

Webhook tiers control push intervals and max assets, not QPS/burst (those fields are unused for webhook tiers).

1.3 users

Purpose: Subscriber and donor accounts. Minimal profile — email, name, tier, and language preference. The api_keys table holds the detailed subscription state.

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique user identifier
`email`	`text`	—	NO	User email address (unique)
`tier`	`text`	free	`YES`	Account tier: free, pro, enterprise, unlimited, lifetime, partner
`created_at`	`timestamptz`	now()	`YES`	Account creation timestamp
`name`	`text`	Friend	`YES`	Display name (default: 'Friend')
`preferred_lang`	`varchar(5)`	NULL	`YES`	Preferred language for communications (emails, newsletters, support). ISO 639-1 code.

Indexes

users_email_key — UNIQUE on email
users_pkey — UNIQUE on id

Group 2 — Configuration3 tables

2.1 application_parameter_profiles

Purpose: System-mode parameter profiles. Each profile defines a complete set of runtime tuning parameters (QPS, burst, cache, DB pool, SQS, UDP) that can be activated as a unit. Allows switching between performance modes without editing individual parameters.

34 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Unique profile identifier
`profile_name`	`text`	—	NO	Profile name (unique): production, high-traffic, maintenance, etc.
`description`	`text`	—	`YES`	Human-readable description of when to use this profile
`qps`	`integer`	1000	NO	Queries per second setting for this profile
`burst`	`integer`	1000	NO	Burst limit setting
`log_level`	`text`	info	NO	Logging verbosity: debug, info, warn, error
`batch_size`	`integer`	300	NO	SQS message batch size for usage logging
`batch_seconds`	`integer`	1	NO	SQS batch flush interval in seconds
`micro_batch_cap`	`integer`	500	NO	Maximum messages in micro-batch buffer
`flush_interval_ms`	`integer`	100	NO	Micro-batch flush interval in milliseconds
`db_max_open`	`integer`	150	NO	Maximum open database connections
`db_max_idle`	`integer`	75	NO	Maximum idle database connections
`db_conn_lifetime_min`	`integer`	10	NO	Maximum connection lifetime in minutes
`db_conn_idle_min`	`integer`	5	NO	Maximum idle connection time in minutes
`cache_ttl`	`integer`	9	NO	Default cache TTL setting
`config_poll`	`integer`	300	NO	Configuration poll interval
`cache_pool_size`	`integer`	600	NO	Valkey connection pool size
`cache_min_idle`	`integer`	120	NO	Minimum idle Valkey connections
`cache_max_retries`	`integer`	1	NO	Maximum Valkey operation retries
`cache_dial_ms`	`integer`	500	NO	Valkey connection dial timeout in ms
`cache_read_ms`	`integer`	500	NO	Valkey read timeout in ms
`cache_write_ms`	`integer`	500	NO	Valkey write timeout in ms
`created_at`	`timestamptz`	now()	NO	Profile creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp
`udp_reader_goroutines`	`integer`	8	`YES`	Number of UDP reader goroutines per socket
`udp_read_buffer_mb`	`integer`	32	`YES`	UDP socket read buffer size in MB
`udp_write_buffer_mb`	`integer`	32	`YES`	UDP socket write buffer size in MB
`udp_workers_per_socket`	`integer`	128	`YES`	Worker goroutines per UDP socket
`udp_batch_size`	`integer`	32	`YES`	UDP response batch size
`udp_pre_serialize`	`boolean`	true	`YES`	Whether to pre-serialize UDP responses
`sqs_batch_size`	`integer`	10	NO	SQS send batch size
`sqs_flush_ms`	`integer`	100	NO	SQS flush interval in milliseconds
`sqs_buffer_size`	`integer`	50000	NO	SQS in-memory buffer capacity
`sqs_timeout_ms`	`integer`	3000	NO	SQS operation timeout in milliseconds

Indexes

application_parameter_profiles_pkey — UNIQUE on id
application_parameter_profiles_profile_name_key — UNIQUE on profile_name

2.2 application_parameters

Purpose: Runtime configuration parameters. Key-value store for all application settings that can be changed without redeployment. Polled every 5 minutes by ECS containers.

4 columns

Column	Data Type	Default	Nullable	Description
`key` PK	`varchar(100)`	—	NO	Parameter name (PK): e.g., admin_api_key, rate_limit_qps, cache_ttl_seconds
`value`	`text`	—	NO	Parameter value (stored as text, interpreted by application)
`description`	`text`	—	`YES`	Human-readable description of what this parameter controls
`updated_at`	`timestamp`	CURRENT_TIMESTAMP	`YES`	Last modification timestamp

Indexes

application_parameters_pkey — UNIQUE on key

2.3 report_parameters

Purpose: LRS-specific configuration. Key-value pairs for report generation settings (date ranges, limits, formatting).

4 columns

Column	Data Type	Default	Nullable	Description
`key`	`varchar(100)`	—	`YES`	Parameter name (PK): LRS-specific settings
`value`	`text`	—	`YES`	Parameter value
`description`	`text`	—	`YES`	Human-readable description
`updated_at`	`timestamp`	—	`YES`	Last modification timestamp

Group 3 — Billing & Payments3 tables

3.1 payment_links

Purpose: Stripe Payment Link management for checkout. Maps link types (subscription, donation, lrs-addon) and tiers to Stripe URLs. Tracks click counts for analytics.

11 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Auto-incrementing identifier
`link_type`	`text`	—	NO	Type: subscription, donation, lrs-addon, subscription-lrs-addon
`tier`	`text`	—	NO	Associated subscription tier
`display_name`	`text`		NO	Human-readable label shown in admin UI
`stripe_url`	`text`	—	NO	Full Stripe Payment Link URL
`return_url`	`text`		NO	Post-checkout redirect URL
`enabled`	`boolean`	true	NO	Whether this link is currently active
`click_count`	`bigint`	0	NO	Number of times this link has been clicked
`notes`	`text`	—	`YES`	Admin notes about this link
`created_at`	`timestamptz`	now()	NO	Creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp

Indexes

idx_payment_links_enabled — on enabled
idx_payment_links_type — on link_type
payment_links_link_type_tier_key — UNIQUE on link_type, tier
payment_links_pkey — UNIQUE on id

3.2 transactions

Purpose: Payment records. Every donation, subscription, and LRS addon purchase is logged here with Stripe references, amounts, and donor information.

15 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique transaction identifier
`user_id`	`uuid`	—	`YES`	Associated user (nullable for anonymous donations)
`timestamp`	`timestamptz`	now()	`YES`	When the transaction occurred
`type`	`text`	—	`YES`	Transaction type: donation, subscription, lrs-addon, subscription-lrs-addon
`amount_usd`	`numeric`	—	`YES`	Amount in USD
`stripe_charge_id`	`text`	—	`YES`	Stripe charge/payment intent ID (unique)
`status`	`text`	—	`YES`	Payment status: succeeded, pending, failed
`donor_name`	`text`	—	`YES`	Name provided at checkout
`donor_email`	`text`	—	`YES`	Email provided at checkout
`impact_type`	`text`	general	`YES`	Donation impact category: general, medical, provisions, etc.
`recurring`	`boolean`	false	`YES`	Whether this is a recurring payment
`stripe_session_id`	`text`	—	`YES`	Stripe Checkout session ID
`tier`	`text`	Free	`YES`	Subscription tier (for subscription transactions)
`api_key`	`text`	—	`YES`	API key issued or associated with this transaction
`preferred_lang`	`varchar(5)`	NULL	`YES`	Language preference captured at checkout

Indexes

idx_transactions_email — on donor_email
idx_transactions_session — on stripe_session_id
idx_transactions_tier — on tier
transactions_pkey — UNIQUE on id
unique_stripe_charge_id — UNIQUE on stripe_charge_id

3.3 tier_catalog

Purpose: Single source of truth for all product pricing and limits — REST subscriptions, webhook push tiers, and donation/impact products. Read live by Raima's /raima/catalog endpoint (no redeploy needed to change prices). The customer-facing checkout pages and this table must agree.

15 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Auto-incrementing identifier
`product`	`text`	—	NO	Product line: rest, webhook, donation, lrs-addon
`tier`	`text`	—	NO	Tier name within the product (e.g., pro, enterprise, unlimited, starter, standard)
`display_name`	`text`	''	NO	Human-readable tier name for display
`price_cents`	`bigint`	0	NO	Price in cents (e.g., 9000 = $90.00)
`currency`	`text`	USD	NO	ISO currency code
`billing_period`	`text`	month	NO	Billing cycle: month, year, one-time
`lpo_limit`	`bigint`	0	NO	Monthly LPO query limit (0 = unlimited)
`lrs_limit`	`bigint`	0	NO	Monthly LRS report limit (0 = unlimited)
`rate_limited`	`boolean`	true	NO	Whether rate limiting applies to this tier
`features`	`jsonb`	[]	NO	Feature list as JSON array (displayed on checkout pages)
`summary`	`text`	''	NO	Short description for Raima and internal tooling
`sort_order`	`integer`	0	NO	Display order within the product group
`published`	`boolean`	false	NO	Whether this tier is visible to customers
`created_at`	`timestamptz`	now()	NO	Creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp

Indexes

tier_catalog_pkey — UNIQUE on id
tier_catalog_product_tier_key — UNIQUE on (product, tier)
idx_tier_catalog_product — on product
idx_tier_catalog_published — on published

Group 4 — Usage Logging4 tables

4.1 monthly_report_counts

Purpose: Monthly report usage aggregation. Tracks per-key report generation counts and limits for the LRS report system.

4 columns

Column	Data Type	Default	Nullable	Description
`api_key`	`text`	—	NO	API key string (PK) — the actual key value
`last_report_generated`	`date`	—	`YES`	Date of the most recent report generation
`monthly_reports_allowed`	`bigint`	—	NO	Maximum reports allowed per month for this key
`monthly_reports_counter`	`bigint`	—	`YES`	Current month report generation count

Indexes

pk_monthly_report_counts_api_key — UNIQUE on api_key

4.2 report_count

Purpose: Per-API-key report limit tracking. Enforces daily and monthly report generation caps with automatic monthly reset via cron.

7 columns

Column	Data Type	Default	Nullable	Description
`api_key` PK FK → api_keys.id	`uuid`	—	NO	API key UUID (PK, FK → api_keys.id)
`last_report_date`	`timestamp`	now()	NO	Date of the most recent report generation
`monthly_limit`	`integer`	10	NO	Maximum reports per month
`monthly_count`	`integer`	0	NO	Reports generated this month
`daily_count`	`integer`	0	NO	Reports generated today
`daily_limit`	`integer`	50	NO	Maximum reports per day
`reset_month`	`integer`	EXTRACT(month FROM now())	NO	Month number when counters were last reset

Indexes

idx_report_count_reset_month — on reset_month
report_count_pkey — UNIQUE on api_key

Foreign Keys

api_key → api_keys.id

4.3 report_usage_logs

Purpose: LRS report request log. Every report generation is logged with filters, duration, and output format for analytics and debugging.

14 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique log entry identifier
`api_key_id`	`text`	—	NO	API key that generated this report
`report_type`	`text`	—	NO	Report type: summary, usage, detailed
`report_run_date`	`timestamp`	now()	NO	When the report was generated
`asset_filter`	`text`	—	`YES`	Asset filter applied (if any)
`start_date_filter`	`date`	—	`YES`	Start date filter applied
`end_date_filter`	`date`	—	`YES`	End date filter applied
`max_rows_returned`	`integer`	—	`YES`	Number of rows in the report output
`report_duration_ms`	`integer`	—	`YES`	Report generation time in milliseconds
`output_format`	`text`	—	NO	Output format: json, csv
`additional_filters`	`jsonb`	—	`YES`	Any additional filters as JSON
`region`	`text`	—	`YES`	AWS region that served this request
`cluster_node`	`text`	—	`YES`	ECS node that generated this report
`created_at`	`timestamp`	now()	NO	Log entry creation timestamp

Indexes

idx_report_usage_logs_api_key — on api_key_id
idx_report_usage_logs_created_at — on created_at
report_usage_logs_pkey — UNIQUE on id

4.4 usage_logs

Purpose: LPO API call log. Every API request is logged with timing, status, asset, price, and source information. Written via SQS queue for decoupled performance.

20 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique log entry identifier
`user_id`	`uuid`	—	`YES`	User who made the request (nullable)
`api_key_id`	`text`	—	`YES`	API key used for this request
`timestamp`	`timestamptz`	now()	`YES`	When the request was made
`ip_address`	`text`	—	`YES`	Client IP address
`query_type`	`text`	—	`YES`	Request type: price, multi-price, historical, etc.
`duration_ms`	`integer`	—	`YES`	Total request processing time in milliseconds
`token_count`	`integer`	—	`YES`	Rate limit tokens consumed
`metadata`	`jsonb`	—	`YES`	Additional request metadata as JSON
`latency_ms`	`integer`	0	`YES`	Backend latency in milliseconds
`status`	`integer`	—	`YES`	HTTP status code returned
`error`	`text`	—	`YES`	Error message if request failed
`cached`	`boolean`	false	`YES`	Whether the response was served from cache
`asset`	`text`	—	`YES`	Asset queried (e.g., BTC, ETH)
`cache_age_seconds`	`numeric`	0.0	`YES`	Age of cached price data in seconds
`cluster_node`	`varchar(50)`	BeastMain	`YES`	ECS node that served this request
`aws_region`	`varchar(20)`	us-east-2	`YES`	AWS region
`source`	`text`		`YES`	Price source exchange (e.g., coinbase-ws)
`price`	`float8`	0	`YES`	Price value returned
`api_lang`	`varchar(5)`	en	NO	Language of API response messages

Indexes

idx_usage_logs_asset — on asset (standalone — asset-only queries with no time filter)
idx_usage_logs_ip — on ip_address (trace-ip forensics)
idx_usage_logs_timestamp — on timestamp (pure time-range queries)
idx_usage_logs_timestamp_asset — composite on (timestamp, asset) — time-range + asset analytics
idx_usage_logs_timestamp_api_key_id — composite on (timestamp, api_key_id) — per-key analytics
idx_usage_logs_timestamp_source — composite on (timestamp, source) — per-exchange analytics
idx_usage_logs_timestamp_cluster_node — composite on (timestamp, cluster_node) — per-node analytics
usage_logs_pkey — UNIQUE on id

Composite indexes (Phase 6 optimization): The four (timestamp, dimension) composite indexes were added to match the report/analytics access patterns — every analytics query filters on a time range plus one dimension (asset, key, source, or node). Database self-observability (the /admin/db-insights endpoint) had shown usage_logs doing sequential scans on these filtered queries; the composites let the planner use index scans instead (6–8 ms execution). The three standalone indexes are retained because they still serve queries that filter on a single dimension without a time range.

4.5 mv_usage_daily Materialized View

Purpose: Pre-aggregated daily rollup of usage_logs (Phase 6 optimization). Precomputes per-day totals, latency percentiles, cache-hit rate, and cardinality so the analytics dashboard and the weekly-newsletter pipeline read a small, indexed view instead of scanning the full usage_logs table on every request. Serves the /dashboard/api/admin/daily-rollup endpoint.

13 columns ~48 day rows

Column	Data Type	Description
`day`	`date`	Date bucket (America/New_York timezone). Unique — one row per day.
`total_requests`	`bigint`	Count of all requests that day
`avg_latency_ms`	`numeric`	Average backend latency for the day
`p50_latency_ms`	`double precision`	Median latency (`percentile_cont(0.5)`)
`p95_latency_ms`	`double precision`	95th-percentile latency
`p99_latency_ms`	`double precision`	99th-percentile latency
`cached_requests`	`bigint`	Count of requests served from cache
`cache_hit_rate_pct`	`numeric`	Cache hit rate as a percentage
`distinct_assets`	`bigint`	Number of distinct assets queried that day
`distinct_api_keys`	`bigint`	Number of distinct API keys active that day
`distinct_sources`	`bigint`	Number of distinct price sources used
`distinct_nodes`	`bigint`	Number of distinct cluster nodes that served traffic
`last_refreshed`	`timestamp with time zone`	When the view was last refreshed — exposed to consumers so they know data freshness

Indexes

idx_mv_usage_daily_day — UNIQUE on day. Required to enable REFRESH MATERIALIZED VIEW CONCURRENTLY (concurrent refresh never blocks readers).

Refresh mechanism — pg_cron (native Aurora scheduling): The view is refreshed every 5 minutes by a pg_cron job running inside Aurora. Job name: tbi-mv-usage-daily-refresh (job_id 2), schedule */5 * * * *, command REFRESH MATERIALIZED VIEW CONCURRENTLY mv_usage_daily. Execution history is available in cron.job_run_details. Typical refresh time is under 100ms. The previous external pipeline (EventBridge rule tbi-refresh-mv-schedule → Lambda tbi-ops-refresh-mv) was retired 2026-05-31 when pg_cron v1.6 was installed.

Group 5 — Exchange & Pricing3 tables

5.1 asset_categories

Purpose: Crypto asset category groupings. Organizes the 150 prewarmed assets into display categories (Major Currencies, DeFi, Layer 2, etc.) for the API response grouping.

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Auto-incrementing identifier
`category_name`	`text`	—	NO	Category display name: Major Currencies, DeFi, Layer 2, etc.
`description`	`text`		NO	Human-readable category description
`display_order`	`integer`	100	NO	Sort order for API response grouping
`enabled`	`boolean`	true	NO	Whether this category is active
`created_at`	`timestamptz`	now()	NO	Creation timestamp

Indexes

asset_categories_category_name_key — UNIQUE on category_name
asset_categories_pkey — UNIQUE on id

5.2 exchange_asset_map

Purpose: Exchange-to-asset symbol translations. Maps generic asset names (BTC, ETH) to exchange-specific trading pair symbols for each of the 6 WebSocket feeds.

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Auto-incrementing identifier
`exchange_name`	`text`	—	NO	Exchange identifier (e.g., coinbase, gemini, kraken)
`asset`	`text`	—	NO	Generic asset symbol (e.g., BTC, ETH, SOL)
`exchange_symbol`	`text`	—	NO	Exchange-specific trading pair symbol
`enabled`	`boolean`	true	NO	Whether this mapping is active for WebSocket subscription
`category`	`text`	Infrastructure	NO	Asset category for grouping (e.g., Infrastructure)

Indexes

exchange_asset_map_exchange_name_asset_key — UNIQUE on exchange_name, asset
exchange_asset_map_pkey — UNIQUE on id
idx_exchange_asset_map_asset — on asset
idx_exchange_asset_map_exchange — on exchange_name

5.3 exchange_feeds

Purpose: Exchange WebSocket feed configurations. Complete connection parameters for each exchange — endpoints, subscription formats, message parsing, heartbeat handling, and symbol conventions.

27 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Auto-incrementing identifier
`exchange_name`	`text`	—	NO	Exchange identifier (unique)
`display_name`	`text`	—	NO	Human-readable exchange name
`ws_endpoint`	`text`	—	NO	WebSocket connection URL
`subscribe_format`	`text`	json	NO	Message format for subscriptions: json
`pair_suffix`	`text`	USD	NO	Trading pair quote currency suffix (e.g., USD)
`symbol_template`	`text`	—	NO	Template for constructing subscription symbols
`symbol_separator`	`text`	-	NO	Character separating base/quote in symbols
`parse_regex`	`text`	—	`YES`	Regex for parsing price messages (legacy)
`enabled`	`boolean`	true	NO	Whether this feed is active
`source_tag`	`text`	—	NO	Tag used in price cache keys (e.g., coinbase-ws)
`notes`	`text`	—	`YES`	Admin notes about this feed
`created_at`	`timestamptz`	now()	NO	Creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp
`subscribe_template`	`text`	—	`YES`	Full subscription message template with placeholders
`trade_channel_match`	`text`	—	`YES`	String to match for identifying trade messages
`price_path`	`text`	—	`YES`	JSON path to price value in trade messages
`symbol_path`	`text`	—	`YES`	JSON path to symbol in trade messages
`timestamp_path`	`text`	—	`YES`	JSON path to timestamp in trade messages
`timestamp_format`	`text`	rfc3339	`YES`	Timestamp format: rfc3339, unix, unix_ms
`ping_interval_sec`	`integer`	0	`YES`	Interval for sending ping/keepalive messages
`ping_message`	`text`	—	`YES`	Ping message content
`symbol_style`	`text`	standard	`YES`	Symbol formatting style: standard, lowercase, etc.
`msg_is_array`	`boolean`	false	`YES`	Whether trade messages arrive as JSON arrays
`array_path`	`text`	—	`YES`	JSON path when message is nested in array
`heartbeat_method`	`text`	—	`YES`	How to send heartbeats: ping, message, none
`heartbeat_response_method`	`text`	—	`YES`	How to respond to server heartbeats

Indexes

exchange_feeds_exchange_name_key — UNIQUE on exchange_name
exchange_feeds_pkey — UNIQUE on id
idx_exchange_feeds_enabled — on enabled

Group 6 — Email & Newsletters8 tables

6.1 email_drafts

Purpose: Email composition drafts. Stores in-progress and sent emails with template references, recipients, and scheduling information.

12 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique draft identifier
`template_id`	`uuid`	—	`YES`	Reference to email_templates (nullable for freeform)
`sender_id`	`uuid`	—	`YES`	Reference to email_senders
`subject`	`varchar(500)`		NO	Email subject line
`content`	`text`		NO	Email body content (HTML)
`recipients`	`text`		NO	Recipient list as JSON array
`cc`	`text`		NO	CC recipients
`bcc`	`text`		NO	BCC recipients
`status`	`varchar(20)`	draft	NO	Draft status: draft, scheduled, sent, failed
`created_at`	`timestamptz`	now()	NO	Creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp
`sent_at`	`timestamptz`	—	`YES`	When the email was actually sent

Indexes

email_drafts_pkey — UNIQUE on id

6.2 email_senders

Purpose: Verified SES sender identities. Defines the From addresses available for sending (e.g., CPMP Mission, The Trinity Beast).

5 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique sender identifier
`display_name`	`varchar(255)`	—	NO	Sender display name (e.g., CPMP Mission, The Trinity Beast)
`email`	`varchar(255)`	—	NO	Sender email address (unique, SES verified)
`reply_to`	`varchar(255)`		NO	Reply-to address
`created_at`	`timestamptz`	now()	NO	Creation timestamp

Indexes

email_senders_email_key — UNIQUE on email
email_senders_pkey — UNIQUE on id

6.3 email_templates

Purpose: SES email templates. Reusable email content templates organized by topic (receipts, welcome, notifications).

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique template identifier
`topic`	`varchar(100)`	—	NO	Template category: receipt, welcome, notification, etc.
`name`	`varchar(255)`	—	NO	Template name for lookup
`subject`	`varchar(500)`		NO	Default subject line
`content`	`text`	—	NO	Template body content with placeholders
`created_at`	`timestamptz`	now()	NO	Creation timestamp

Indexes

email_templates_pkey — UNIQUE on id

6.4 email_translations

Purpose: Multi-language email template fields. Stores translated versions of template fields (subject, body) for each supported language.

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique translation identifier
`template_name`	`varchar(100)`	—	NO	Reference to email template name
`lang`	`varchar(5)`	—	NO	ISO 639-1 language code
`field_name`	`varchar(50)`	—	NO	Which field is translated: subject, body, cta_text, etc.
`field_value`	`text`	—	NO	Translated content for this field
`created_at`	`timestamptz`	now()	`YES`	Creation timestamp

Indexes

email_translations_pkey — UNIQUE on id
email_translations_template_name_lang_field_name_key — UNIQUE on template_name, lang, field_name

6.5 newsletter_subscribers

Purpose: Newsletter mailing list. Subscribers with email, name, source (cpmp or tbi), opt-out status, and language preference.

7 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique subscriber identifier
`email`	`text`	—	NO	Subscriber email address
`name`	`text`	—	`YES`	Subscriber display name
`source`	`text`	cpmp	`YES`	Subscription source: cpmp or tbi
`subscribed_at`	`timestamptz`	now()	`YES`	When the subscription was created
`opt_out`	`boolean`	false	`YES`	Whether the subscriber has opted out
`preferred_lang`	`varchar(5)`	en	`YES`	Preferred newsletter language (ISO 639-1)

Indexes

idx_subscribers_source — on source
newsletter_subscribers_email_source_key — UNIQUE on email, source
newsletter_subscribers_pkey — UNIQUE on id

6.6 newsletter_templates

Purpose: Newsletter email templates. Reusable newsletter content templates organized by type and topic.

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique template identifier
`type`	`varchar(20)`	cpmp	NO	Template type: cpmp or tbi
`topic`	`varchar(100)`	general	NO	Template topic/category
`name`	`varchar(255)`	—	NO	Template name for lookup
`content`	`text`	—	NO	Template body content
`created_at`	`timestamptz`	now()	NO	Creation timestamp

Indexes

newsletter_templates_pkey — UNIQUE on id

6.7 newsletter_translations

Purpose: Multi-language newsletter content. Translated subject and body for each newsletter in each supported language.

9 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique translation identifier
`newsletter_id` FK → newsletters.id	`uuid`	—	NO	Reference to newsletters table (FK)
`lang`	`varchar(5)`	—	NO	ISO 639-1 language code
`subject`	`text`	—	NO	Translated newsletter subject
`body_html`	`text`	—	NO	Translated newsletter body (HTML)
`auto_translated`	`boolean`	true	`YES`	Whether this was machine-translated
`reviewed`	`boolean`	false	`YES`	Whether a human has reviewed the translation
`created_at`	`timestamptz`	now()	`YES`	Creation timestamp
`updated_at`	`timestamptz`	now()	`YES`	Last modification timestamp

Indexes

newsletter_translations_newsletter_id_lang_key — UNIQUE on newsletter_id, lang
newsletter_translations_pkey — UNIQUE on id

Foreign Keys

newsletter_id → newsletters.id

6.8 newsletters

Purpose: Sent newsletter campaigns. Records of each newsletter sent with subscriber count, open rate, and metadata.

9 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique newsletter identifier
`title`	`text`	—	NO	Newsletter title/headline
`content`	`text`	—	NO	Newsletter body content (English source)
`sent_at`	`timestamptz`	now()	`YES`	When the newsletter was sent
`subscriber_count`	`integer`	0	`YES`	Number of recipients at send time
`open_rate`	`float8`	0.0	`YES`	Percentage of recipients who opened
`metadata`	`jsonb`	—	`YES`	Additional metadata as JSON
`status`	`text`	active	`YES`	Newsletter status: active, archived
`type`	`text`	cpmp	`YES`	Newsletter type: cpmp or tbi

Indexes

idx_newsletters_sent_at — on sent_at DESC
idx_newsletters_status — on status
newsletters_pkey — UNIQUE on id

Group 7 — Support & Engagement6 tables

7.1 demo_leads

Purpose: Demo registration captures. Stores interest submissions from the demo page with email, platform, and IP for rate limiting.

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique lead identifier
`email`	`text`	—	NO	Submitted email address
`interest`	`text`	general	NO	What the lead is interested in
`platform`	`text`	unknown	NO	Platform/source of the submission
`client_ip`	`text`	—	`YES`	Client IP address (for rate limiting)
`created_at`	`timestamptz`	now()	NO	Submission timestamp

Indexes

demo_leads_pkey — UNIQUE on id
idx_demo_leads_email — on email
idx_demo_leads_ip — on client_ip

7.2 error_messages

Purpose: Multi-language API error messages. Lookup table for translating error message keys into the user's preferred language (UME Phase 2).

5 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Auto-incrementing identifier
`message_key`	`varchar(100)`	—	NO	Error message key (e.g., invalid_api_key, rate_limited)
`lang`	`varchar(5)`	en	NO	ISO 639-1 language code (default: en)
`message`	`text`	—	NO	Translated error message text
`created_at`	`timestamptz`	now()	NO	Creation timestamp

Indexes

error_messages_message_key_lang_key — UNIQUE on message_key, lang
error_messages_pkey — UNIQUE on id

7.3 map_pins

Purpose: CPMP mission impact map markers. Geographic pins for medical camps, provisions, training events, and other ministry activities.

10 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique pin identifier
`type`	`text`	—	NO	Pin type: medical, provisions, training, freedom, wheelchairs, word-of-life
`lat`	`float8`	—	`YES`	Latitude coordinate
`lng`	`float8`	—	`YES`	Longitude coordinate
`region`	`text`	—	`YES`	Geographic region name
`caption`	`text`	—	`YES`	Pin description/caption text
`media_url`	`text`	—	`YES`	Primary media URL (legacy single image)
`date`	`timestamptz`	—	`YES`	Event date
`status`	`text`	active	`YES`	Pin status: active, archived
`media_urls`	`jsonb`	[]	`YES`	Array of media URLs as JSONB (multiple images)

Indexes

map_pins_pkey — UNIQUE on id

7.4 partner_applications

Purpose: AWS Partner application submissions. Tracks companies applying for PrivateLink partner access with their AWS details and review status.

16 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique application identifier
`company_name`	`text`	—	NO	Applying company name
`contact_name`	`text`	—	NO	Primary contact person
`contact_email`	`text`	—	NO	Contact email address
`aws_account_id`	`text`	—	NO	Applicant AWS account ID
`aws_region`	`text`	—	NO	Preferred AWS region for PrivateLink
`vpc_cidr`	`text`	—	`YES`	Applicant VPC CIDR block
`connection_type`	`text`	—	NO	Requested connection type: privatelink, vpn
`data_provided`	`text`	—	`YES`	Description of data/services needed
`estimated_volume`	`text`	—	`YES`	Estimated monthly API call volume
`notes`	`text`	—	`YES`	Additional notes from applicant
`status`	`text`	pending	NO	Application status: pending, approved, rejected
`reviewed_at`	`timestamptz`	—	`YES`	When the application was reviewed
`reviewer_notes`	`text`	—	`YES`	Internal review notes
`api_key_id`	`uuid`	—	`YES`	Issued API key (after approval)
`created_at`	`timestamptz`	now()	NO	Submission timestamp

Indexes

idx_partner_apps_email — on contact_email
idx_partner_apps_status — on status
partner_applications_pkey — UNIQUE on id

7.5 support_replies

Purpose: Support ticket replies. Individual messages within a support ticket thread, from both admin and customer, with optional translation.

8 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique reply identifier
`ticket_id` FK → support_tickets.id	`uuid`	—	NO	Parent ticket (FK → support_tickets.id)
`author`	`varchar(255)`	—	NO	Reply author name or identifier
`author_type`	`varchar(20)`	admin	NO	Author role: admin or customer
`message`	`text`	—	NO	Reply message content
`is_internal`	`boolean`	false	NO	Whether this is an internal note (not visible to customer)
`created_at`	`timestamptz`	now()	NO	Reply timestamp
`message_translated`	`text`	—	`YES`	Auto-translated version of the message (for cross-language support)

Indexes

idx_support_replies_created_at — on created_at
idx_support_replies_ticket_id — on ticket_id
support_replies_pkey — UNIQUE on id

Foreign Keys

ticket_id → support_tickets.id

7.6 support_tickets

Purpose: Customer support system. Tickets submitted via the support form with auto-categorization, status tracking, and language support.

13 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique ticket identifier
`ticket_number`	`varchar(20)`	—	NO	Human-readable ticket number (unique, e.g., TKT-001234)
`name`	`varchar(255)`	—	NO	Submitter name
`email`	`varchar(255)`	—	NO	Submitter email address
`category`	`varchar(100)`	general	NO	Auto-categorized: billing, technical, api-access, rate-limit, feature-request, bug-report, account, general
`subject`	`varchar(500)`	—	NO	Ticket subject line
`message`	`text`	—	NO	Ticket body (original language)
`status`	`varchar(30)`	new	NO	Ticket status: new, open, pending, resolved, closed
`created_at`	`timestamptz`	now()	NO	Submission timestamp
`updated_at`	`timestamptz`	now()	NO	Last activity timestamp
`client_ip`	`varchar(100)`		NO	Submitter IP address
`preferred_lang`	`varchar(5)`	en	`YES`	Submitter preferred language for responses
`message_en`	`text`	—	`YES`	English translation of message (if submitted in another language)

Indexes

idx_support_tickets_category — on category
idx_support_tickets_created_at — on created_at DESC
idx_support_tickets_email — on email
idx_support_tickets_status — on status
idx_support_tickets_ticket_number — on ticket_number
support_tickets_pkey — UNIQUE on id
support_tickets_ticket_number_key — UNIQUE on ticket_number

Group 8 — Operations & Maintenance3 tables

8.1 cron_execution_logs

Purpose: Cron job execution tracking. Logs every scheduled job run (monthly reset, sync, cleanup) with status and row counts.

7 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Unique log entry identifier
`job_name`	`text`	—	NO	Cron job name: monthly_reset, sync, cleanup, etc.
`executed_at`	`timestamptz`	now()	`YES`	When the job ran
`status`	`text`	—	NO	Execution status: success, failed, partial
`rows_affected`	`bigint`	—	`YES`	Number of rows processed
`message`	`text`	—	`YES`	Execution details or error message
`created_at`	`timestamptz`	now()	`YES`	Log entry creation timestamp

Indexes

cron_execution_logs_pkey — UNIQUE on id

8.2 cron_reset_logs

Purpose: Cron job execution log (same schema as cron_execution_logs). Additional logging for reset operations.

7 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Unique log entry identifier
`job_name`	`text`	—	NO	Job name that produced this log
`executed_at`	`timestamptz`	now()	`YES`	When the job ran
`status`	`text`	—	NO	Execution status: success, failed
`rows_affected`	`bigint`	—	`YES`	Number of keys/rows reset
`message`	`text`	—	`YES`	Details or error message
`created_at`	`timestamptz`	now()	`YES`	Log entry creation timestamp

Indexes

cron_reset_logs_executed_at_idx — on executed_at
cron_reset_logs_job_name_idx — on job_name
cron_reset_logs_pkey — UNIQUE on id

8.3 cron_reset_summary

Purpose: Reset summary statistics. Aggregated results of monthly usage reset operations.

6 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`integer`	auto-increment	NO	Unique summary identifier
`job_name`	`text`	—	`YES`	Job that produced this summary
`executed_at`	`timestamptz`	—	`YES`	When the reset ran
`status`	`text`	—	`YES`	Overall status: success, partial, failed
`rows_affected`	`bigint`	—	`YES`	Total rows affected
`message`	`text`	—	`YES`	Summary details

Indexes

cron_reset_summary_pkey — UNIQUE on id
idx_cron_reset_summary_executed_at — on executed_at
idx_cron_reset_summary_job_name — on job_name
idx_cron_reset_summary_job_name_executed_at — on job_name, executed_at
idx_cron_reset_summary_status — on status

Group 9 — Website Analytics2 tables

9.1 page_events

Purpose: Website interaction event tracking. Click events, form submissions, and other user interactions captured by the analytics script.

8 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique event identifier
`page_path`	`varchar(500)`	—	NO	Page URL path where event occurred
`event_type`	`varchar(100)`	—	NO	Event type: click, scroll, form_submit, etc.
`event_target`	`varchar(500)`		NO	DOM element that triggered the event
`event_data`	`jsonb`	{}	NO	Additional event data as JSON
`visitor_id`	`varchar(64)`		NO	Anonymous visitor fingerprint
`session_id`	`varchar(64)`		NO	Browser session identifier
`created_at`	`timestamptz`	now()	NO	Event timestamp

Indexes

idx_page_events_created_at — on created_at DESC
idx_page_events_event_type — on event_type
idx_page_events_page_path — on page_path
page_events_pkey — UNIQUE on id

9.2 page_views

Purpose: Website page view analytics. Every page load with full geo-location data, device info, and session tracking.

19 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique page view identifier
`page_path`	`varchar(500)`	—	NO	Page URL path
`page_title`	`varchar(500)`		NO	HTML page title
`referrer`	`varchar(1000)`		NO	Referring URL
`user_agent`	`varchar(1000)`		NO	Browser user agent string
`visitor_id`	`varchar(64)`		NO	Anonymous visitor fingerprint
`session_id`	`varchar(64)`		NO	Browser session identifier
`screen_width`	`integer`	0	NO	Viewport width in pixels
`screen_height`	`integer`	0	NO	Viewport height in pixels
`country`	`varchar(10)`		NO	ISO country code (2-letter)
`created_at`	`timestamptz`	now()	NO	Page view timestamp
`country_name`	`varchar(100)`		NO	Full country name
`region`	`varchar(10)`		NO	Region/state code
`region_name`	`varchar(100)`		NO	Full region/state name
`city`	`varchar(100)`		NO	City name
`latitude`	`varchar(20)`		NO	Approximate latitude
`longitude`	`varchar(20)`		NO	Approximate longitude
`timezone`	`varchar(50)`		NO	Visitor timezone
`device_type`	`varchar(20)`		NO	Device category: desktop, mobile, tablet

Indexes

idx_page_views_created_at — on created_at DESC
idx_page_views_page_path — on page_path
idx_page_views_visitor_id — on visitor_id
page_views_pkey — UNIQUE on id

Group 10 — Webhook Push2 tables

10.1 webhook_subscriptions

Purpose: Webhook subscriber configurations and delivery state. Defines each subscriber's endpoints, assets, intervals, tier, and billing references.

19 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Unique subscription identifier
`api_key_id` FK → api_keys.id	`uuid`	—	NO	Associated API key (FK → api_keys.id)
`tier`	`text`	webhook_starter	NO	Webhook tier: webhook_starter, webhook_standard, webhook_professional, webhook_enterprise, webhook_partner
`udp_endpoint_ip`	`text`	—	`YES`	Subscriber UDP endpoint IP address
`udp_endpoint_port`	`integer`	—	`YES`	Subscriber UDP endpoint port
`https_endpoint_url`	`text`	—	`YES`	Subscriber HTTPS webhook URL
`delivery_method`	`text`	udp	NO	Delivery protocol: udp or https
`interval_seconds`	`integer`	60	NO	Price push interval in seconds
`assets`	`text[]`	ARRAY[][]	NO	Array of subscribed asset symbols
`max_assets`	`integer`	9	NO	Maximum assets allowed for this tier
`status`	`text`	pending_verification	NO	Subscription status: pending_verification, active, paused, canceled
`verification_token`	`text`	—	`YES`	Token for endpoint ownership verification
`verified_at`	`timestamptz`	—	`YES`	When endpoint was verified
`monthly_push_count`	`integer`	0	NO	Push deliveries this month
`stripe_customer_id`	`text`	—	`YES`	Stripe customer ID for billing
`stripe_subscription_id`	`text`	—	`YES`	Stripe subscription ID
`subscription_status`	`text`	active	NO	Billing status: active, past_due, canceled
`created_at`	`timestamptz`	now()	NO	Creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp

Indexes

idx_webhook_subs_api_key — on api_key_id
idx_webhook_subs_status — on status
idx_webhook_subs_tier — on tier
webhook_subscriptions_pkey — UNIQUE on id

Foreign Keys

api_key_id → api_keys.id

10.2 webhook_delivery_log

Purpose: Webhook delivery attempt log. Every price push delivery is logged with asset, price, method, latency, and status.

12 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Auto-incrementing delivery log identifier
`subscription_id` FK → webhook_subscriptions.id	`uuid`	—	NO	Which subscription this delivery belongs to (FK)
`asset`	`text`	—	NO	Asset symbol delivered (e.g., BTC)
`price`	`numeric`	—	NO	Price value delivered
`source`	`text`	—	NO	Price source exchange (e.g., coinbase-ws)
`delivery_method`	`text`	—	NO	Protocol used: udp or https
`status`	`text`	delivered	NO	Delivery status: delivered, failed, timeout
`latency_ms`	`float8`	—	`YES`	Delivery round-trip time in milliseconds
`attempt`	`integer`	1	NO	Attempt number (1 = first try)
`error`	`text`	—	`YES`	Error message if delivery failed
`sequence_number`	`bigint`	0	NO	Monotonically increasing sequence for ordering
`created_at`	`timestamptz`	now()	NO	Delivery timestamp

Indexes

idx_webhook_delivery_created — on created_at
idx_webhook_delivery_status — on status WHERE status <> 'delivered'
idx_webhook_delivery_sub — on subscription_id
webhook_delivery_log_pkey — UNIQUE on id

Foreign Keys

subscription_id → webhook_subscriptions.id

Group 11 — Translation Engine2 tables

11.1 translation_jobs

Purpose: Translation job orchestration. Tracks every translation batch submitted via the admin API — documents, languages, progress, costs, and Step Function state.

29 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`uuid`	gen_random_uuid()	NO	Internal UUID (auto-generated)
`job_id`	`varchar(64)`	—	NO	Public job identifier (unique, used in API responses)
`state`	`varchar(20)`	queued	NO	Job state: queued, running, completed, failed, cancelled
`submitted_at`	`timestamptz`	now()	NO	When the job was submitted
`started_at`	`timestamptz`	—	`YES`	When processing began
`completed_at`	`timestamptz`	—	`YES`	When processing finished
`submitted_by`	`varchar(50)`	admin-api	`YES`	Who submitted: admin-api, retry, cli
`docs`	`jsonb`	—	NO	Array of document filenames to translate (JSONB)
`langs`	`jsonb`	—	NO	Array of target language codes (JSONB)
`options`	`jsonb`	{}	`YES`	Job options as JSON (chunk size overrides, etc.)
`progress`	`jsonb`	{}	`YES`	Per-pair progress tracking as JSON
`errors`	`jsonb`	[]	`YES`	Array of error details for failed pairs (JSONB)
`bedrock_cost_usd`	`numeric`	0	`YES`	Total Bedrock API cost for this job in USD
`bedrock_invocations`	`integer`	0	`YES`	Total Bedrock API calls made
`total_chunks`	`integer`	0	`YES`	Total document chunks processed
`total_pairs`	`integer`	0	`YES`	Total doc-language pairs in this job
`succeeded_pairs`	`integer`	0	`YES`	Number of pairs that completed successfully
`failed_pairs`	`integer`	0	`YES`	Number of pairs that failed
`elapsed_seconds`	`integer`	—	`YES`	Total job duration in seconds
`step_function_arn`	`text`	—	`YES`	ARN of the Step Function execution
`retry_of`	`varchar(64)`	—	`YES`	Job ID this is a retry of (nullable)
`idempotency_key`	`varchar(128)`	—	`YES`	Client-provided idempotency key
`cloudfront_invalidation_ids`	`jsonb`	[]	`YES`	Array of CloudFront invalidation IDs (JSONB)
`search_index_rebuilt`	`boolean`	false	`YES`	Whether the search index was rebuilt after this job
`notification_sent`	`boolean`	false	`YES`	Whether completion notification was sent
`created_at`	`timestamptz`	now()	NO	Record creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last state change timestamp
`reason`	`text`	—	`YES`	Cancellation or failure reason
`model`	`varchar`	claude-sonnet-4.6	`YES`	Bedrock model used for translation (e.g., claude-sonnet-4.6, claude-haiku-3.5, claude-opus-4)

Indexes

idx_translation_jobs_state — on state
idx_translation_jobs_submitted_at — on submitted_at DESC
translation_jobs_job_id_key — UNIQUE on job_id
translation_jobs_pkey — UNIQUE on id

11.2 translation_job_events

Purpose: Translation job audit log. Granular event stream for each job — per-pair start, success, failure, retry, and completion events.

7 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Auto-incrementing event identifier
`job_id`	`varchar(64)`	—	NO	Parent job identifier (matches translation_jobs.job_id)
`event_type`	`varchar(30)`	—	NO	Event type: pair_start, pair_success, pair_failed, job_start, job_complete, etc.
`doc`	`varchar(200)`	—	`YES`	Document filename (for pair-level events)
`lang`	`varchar(5)`	—	`YES`	Target language code (for pair-level events)
`detail`	`jsonb`	{}	`YES`	Event details as JSON (error messages, timing, chunk counts)
`created_at`	`timestamptz`	now()	NO	Event timestamp

Indexes

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

11.3 translation_quotes

Purpose: Customer-facing translation quotes with Stripe payment integration. When a customer requests a document translation via the public API, a quote is generated with cost breakdown (per-chunk, per-pair, infrastructure, markup). The customer accepts and pays via Stripe; on payment confirmation the quote transitions to accepted and triggers the translation job.

32 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Auto-incrementing identifier
`quote_id`	`varchar(64)`	—	NO	Public-facing quote identifier (ULID)
`api_key_id`	`varchar(128)`	—	NO	API key that requested the quote
`doc_url`	`text`	—	NO	URL of the document to translate
`doc_name`	`varchar(255)`	—	NO	Document filename
`doc_size_bytes`	`integer`	—	NO	Document size in bytes
`estimated_chunks`	`integer`	—	NO	Estimated number of translation chunks
`difficulty`	`varchar(21)`	standard	NO	Document difficulty: standard, technical, complex
`code_blocks`	`integer`	0	NO	Number of code blocks detected in the document
`diagrams`	`integer`	0	NO	Number of Mermaid diagrams detected
`langs`	`jsonb`	—	NO	Target languages as JSON array
`lang_count`	`integer`	—	NO	Number of target languages
`cost_per_chunk`	`numeric(8,4)`	—	NO	Bedrock cost per chunk (USD)
`cost_per_pair`	`numeric(8,4)`	—	NO	Total cost per doc-language pair (USD)
`infra_per_pair`	`numeric(8,4)`	—	NO	Infrastructure cost per pair (S3, CloudFront, compute)
`subtotal`	`numeric(10,4)`	—	NO	Subtotal before markup (USD)
`markup_pct`	`integer`	—	NO	Markup percentage applied
`total_price`	`numeric(10,4)`	—	NO	Final price charged to customer (USD)
`model`	`varchar(50)`	claude-sonnet-4.6	`YES`	AI model used for translation
`state`	`varchar(21)`	pending	NO	Quote lifecycle: pending, accepted, declined, expired, refunded
`expires_at`	`timestamptz`	—	NO	Quote expiration (24 hours from creation)
`accepted_at`	`timestamptz`	—	`YES`	When the customer accepted and paid
`declined_at`	`timestamptz`	—	`YES`	When the customer declined
`stripe_payment_intent_id`	`varchar(128)`	—	`YES`	Stripe PaymentIntent ID for this quote
`stripe_charge_id`	`varchar(128)`	—	`YES`	Stripe Charge ID after successful payment
`job_id`	`varchar(64)`	—	`YES`	Translation job ID created after acceptance
`refunded_at`	`timestamptz`	—	`YES`	When a refund was issued
`refund_amount`	`numeric(10,2)`	NULL	`YES`	Refund amount (USD)
`refund_id`	`varchar(255)`	NULL	`YES`	Stripe Refund ID
`protected_terms`	`jsonb`	[]	`YES`	Customer-supplied brand terms to protect during translation
`created_at`	`timestamptz`	now()	NO	Quote creation timestamp
`updated_at`	`timestamptz`	now()	NO	Last modification timestamp

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
idx_quotes_state — on state

Group 12 — Agent Profiles2 tables

12.1 agent_profiles

Purpose: Registry of all agent identities in the UME (Unified Messaging Envelope) system. Every API response carries an agent_profile_arn field identifying which agent produced it. This table is the Phase 2 backing store for those identities — versioned, with model bindings, task contracts, and policies. Phase 1 uses constants in code; Phase 2 reads from this table via an admin endpoint.

15 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Auto-incrementing identifier
`name`	`varchar(64)`	—	NO	Agent short name (e.g., tbi, raima, webhook-engine)
`version`	`varchar(16)`	—	NO	Semantic version (e.g., v1, v2)
`arn`	`text`	—	NO	Full ARN: arn:tbi:{region}:{account}:agent-profile/{name}/{version}
`display_name`	`varchar(128)`	—	NO	Human-readable agent name
`role`	`text`	—	NO	Agent role description (what it does)
`model_id`	`varchar(128)`	''	NO	Bedrock model ID bound to this agent (empty if no AI backing)
`system_prompt`	`text`	''	NO	System prompt for AI-backed agents
`tasks`	`jsonb`	[]	NO	Task contract — what this agent can do (JSON array)
`policies`	`jsonb`	{}	NO	Access policies and guardrails (JSON object)
`metadata`	`jsonb`	{}	NO	Arbitrary metadata (tags, labels, deployment info)
`active`	`boolean`	true	NO	Whether this agent profile is currently active
`created_at`	`timestamp`	CURRENT_TIMESTAMP	NO	Creation timestamp
`updated_at`	`timestamp`	CURRENT_TIMESTAMP	NO	Last modification timestamp
`bumped_at`	`timestamp`	CURRENT_TIMESTAMP	NO	Last version bump timestamp

Indexes

agent_profiles_pkey — UNIQUE on id
agent_profiles_name_version_key — UNIQUE on (name, version)
idx_agent_profiles_arn — on arn
idx_agent_profiles_name_active — on (name, active)

12.2 agent_profile_events

Purpose: Audit log for agent profile changes. Every create, update, version bump, activate, or deactivate action is recorded with the actor and payload diff.

8 columns

Column	Data Type	Default	Nullable	Description
`id` PK	`bigint`	auto-increment	NO	Auto-incrementing event identifier
`agent_profile_id` FK → agent_profiles.id	`bigint`	—	NO	Parent agent profile
`name`	`varchar(64)`	—	NO	Agent name at time of event (denormalized for history)
`version`	`varchar(16)`	—	NO	Agent version at time of event
`action`	`varchar(32)`	—	NO	Action: created, updated, bumped, activated, deactivated
`actor`	`varchar(128)`	''	NO	Who performed the action (admin email or system)
`payload`	`jsonb`	{}	NO	Event payload — diff of changes or full snapshot
`created_at`	`timestamp`	CURRENT_TIMESTAMP	NO	Event timestamp

Indexes

agent_profile_events_pkey — UNIQUE on id
idx_agent_profile_events_profile_ts — on (agent_profile_id, created_at DESC)
idx_agent_profile_events_name_ts — on (name, created_at DESC)
idx_agent_profile_events_action_ts — on (action, created_at DESC)

Schema Summary

42 tables across 12 logical groups, 491 columns total, plus 1 materialized view (mv_usage_daily) backing the analytics dashboard.

Group	Tables	Columns
Group 1 — Core: Users & Authentication	3	38
Group 2 — Configuration	3	42
Group 3 — Billing & Payments	3	41
Group 4 — Usage Logging	4	45
Group 5 — Exchange & Pricing	3	39
Group 6 — Email & Newsletters	8	60
Group 7 — Support & Engagement	6	58
Group 8 — Operations & Maintenance	3	20
Group 9 — Website Analytics	2	27
Group 10 — Webhook Push	2	31
Group 11 — Translation Engine	3	68
Group 12 — Agent Profiles	2	23

Foreign Key Relationships

api_keys.user_id → users.id
newsletter_translations.newsletter_id → newsletters.id
report_count.api_key → api_keys.id
support_replies.ticket_id → support_tickets.id
webhook_delivery_log.subscription_id → webhook_subscriptions.id
webhook_subscriptions.api_key_id → api_keys.id
agent_profile_events.agent_profile_id → agent_profiles.id

Database Engine Optimization

A round of PostgreSQL optimization pushed work down into the database engine and removed query-level overhead on the hot paths. The schema-visible results are documented above (the four composite indexes on usage_logs and the mv_usage_daily materialized view). The application-side and engine-side pieces are summarized here.

Optimization	What it does
Prepared-statement registry (`dbx`)	Five fixed-shape hot-path queries are registered as named prepared statements at server startup: API-key lookup, API-key last-used touch, application-parameters load, report-count check, and report-count upsert. The registry is nil-safe and fail-soft — if a statement fails to prepare it transparently falls back to an ad-hoc query, so it never blocks startup.
Report-counter upsert	The LRS report counter collapsed a read-then-insert two-step into a single `INSERT … ON CONFLICT DO UPDATE` with month/day rollover logic, removing a round-trip on every report request.
Set-based usage updates	The queued-writer Lambda replaced its per-key `UPDATE` loop with one set-based `UPDATE … FROM (unnest(...))`, turning N round-trips into 1 per SQS batch.
GROUPING SETS analytics	The admin analytics summary replaced six separate aggregation queries with a single `GROUPING SETS` pass that also computes latency percentiles (`percentile_cont` p50/p95/p99) server-side — measured 2.1× faster (87 ms → 41 ms).
Parameterized admin SQL	The `/admin/sql` endpoint gained an optional `args` array for parameterized queries, and the translate-finalize Lambda's interpolated SQL was converted to placeholders — eliminating value-interpolated SQL from the active codebase.
Self-observability	The `/admin/db-insights` endpoint surfaces buffer-cache hit ratio, top queries by total time and call count (from `pg_stat_statements`), and per-table sequential-vs-index scan health — the same data that justified the composite indexes above.

Table of Contents

Logical Data Model — Full Database Diagram

Identity & Access

Configuration

Billing & Usage Tracking

Exchange & Pricing

Email & Newsletters

Support & Engagement

Operations & Maintenance

Website Analytics

Webhook Push Delivery

Translation Engine

1.1 api_keys

API Key Groups — Capability Flag Reference

Behavioral Rules

Live Key Inventory (as of 2026-05-31)

Indexes

Foreign Keys

1.2 api_key_groups

Indexes

Seeded Groups (6 rows)

The JOIN Pattern

1.3 rate_limit_template

Indexes

Current Tier Data (6 LPO + 5 Webhook = 11 rows)

1.3 users

Indexes

2.1 application_parameter_profiles

Indexes

2.2 application_parameters

Indexes

2.3 report_parameters

3.1 payment_links

Indexes

3.2 transactions

Indexes

3.3 tier_catalog

Indexes

4.1 monthly_report_counts

Indexes

4.2 report_count

Indexes

Foreign Keys

4.3 report_usage_logs

Indexes

4.4 usage_logs

Indexes

4.5 mv_usage_daily Materialized View

Indexes

5.1 asset_categories

Indexes

5.2 exchange_asset_map

Indexes

5.3 exchange_feeds

Indexes

6.1 email_drafts

Indexes

6.2 email_senders

Indexes

6.3 email_templates

Indexes

6.4 email_translations

Indexes

6.5 newsletter_subscribers

Indexes

6.6 newsletter_templates

Indexes

6.7 newsletter_translations

Indexes

Foreign Keys

6.8 newsletters

Indexes

7.1 demo_leads

Indexes

7.2 error_messages

Indexes

7.3 map_pins

Indexes

7.4 partner_applications

Indexes