Complete schema reference for CPMP_Backend_Aurora — Aurora PostgreSQL Serverless v2. All 37 tables, 391 columns, indexes, and foreign keys.
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.
%%{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"
%%{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
}
%%{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
}
%%{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
}
%%{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"
%%{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"
%%{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
}
%%{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
}
%%{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
jsonb assets
text asset
numeric price
text status
float8 latency_ms
}
webhook_delivery_daily {
uuid subscription_id PK
date day PK
text delivery_method PK
int push_events
numeric avg_latency_ms
}
webhook_test_receiver_allowlist {
uuid subscription_id PK
text label
text added_by
timestamptz added_at
}
webhook_subscriptions ||--o{ webhook_delivery_log : "logs"
webhook_subscriptions ||--o{ webhook_delivery_daily : "rolls up into"
webhook_subscriptions ||--o| webhook_test_receiver_allowlist : "may be allow-listed"
api_keys ||--o{ webhook_subscriptions : "subscribes"
api_keys {
uuid id PK
text tier
}
%%{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_parameters {
varchar key PK
text value
text description
varchar category
timestamptz updated_at
}
bedrock_model_catalog {
int id PK
varchar friendly_name
varchar bedrock_model_id
varchar region
varchar inference_type
boolean enabled
boolean blocked
}
translation_quotes {
uuid id PK
varchar api_key_id
varchar state
jsonb docs
jsonb langs
varchar model
numeric quoted_amount
}
translation_jobs ||--o{ translation_job_events : "emits"
37 tables across 11 domain groups (+ 7 translation tables in TBTS Data Dictionary). Lines between entities represent foreign key relationships.
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 Reporting Service) reports are enabled |
response_format | text | tbc | NO | Status indicator format: tbc = emoji (✅🛑⚠️), plain = text (OK/ERROR/WARN). Affects both status and error fields in every UME response. Set via Dashboard Profile panel. |
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. Determines the language field in the UME and which errmsg:{lang}:{key} values are returned. 12 supported values: en, es, pt, fr, de, ru, hi, ur, it, ar, ja, zh. Set via Dashboard Profile panel. Separate from preferred_lang (which controls emails/newsletters). |
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. |
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. |
| 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. |
| 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.
api_keys_api_key_key — UNIQUE on api_keyapi_keys_pkey — UNIQUE on ididx_api_keys_hash — on key_hashidx_api_keys_stripe_customer_id — on stripe_customer_id WHERE stripe_customer_id IS NOT NULLidx_api_keys_stripe_subscription_id — on stripe_subscription_id WHERE stripe_subscription_id IS NOT NULLidx_api_keys_user — on user_iduser_id → users.idgroup_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. |
api_key_groups_name_key — UNIQUE on nameidx_api_key_groups_name — on nameidx_api_key_groups_enabled — on enabled| 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
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 = $1To 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.
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 |
rate_limit_template_pkey — UNIQUE on tier| 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).
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. |
users_email_key — UNIQUE on emailusers_pkey — UNIQUE on id34 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 |
application_parameter_profiles_pkey — UNIQUE on idapplication_parameter_profiles_profile_name_key — UNIQUE on profile_name4 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 |
application_parameters_pkey — UNIQUE on key4 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 |
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 |
idx_payment_links_enabled — on enabledidx_payment_links_type — on link_typepayment_links_link_type_tier_key — UNIQUE on link_type, tierpayment_links_pkey — UNIQUE on id15 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 |
idx_transactions_email — on donor_emailidx_transactions_session — on stripe_session_ididx_transactions_tier — on tiertransactions_pkey — UNIQUE on idunique_stripe_charge_id — UNIQUE on stripe_charge_id/rhema/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 Rhema 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 |
tier_catalog_pkey — UNIQUE on idtier_catalog_product_tier_key — UNIQUE on (product, tier)idx_tier_catalog_product — on productidx_tier_catalog_published — on publishedcus_... customer records to one users.id, by email. Added 2026-08-03 because standalone Stripe Payment Links (the LRS addon, and others) mint a new Stripe customer on every purchase, orphaned from the account's real customer record — this table lets the write path attach a new charge to the existing customer instead of creating a disconnected one, and lets cleanup find orphans.10 columns
| Column | Data Type | Default | Nullable | Description |
|---|---|---|---|---|
stripe_customer_id PK | text | — | NO | The Stripe cus_... identifier |
user_id FK → users.id | uuid | — | YES | The resolved account this Stripe customer belongs to, matched by case-insensitive email. ON DELETE SET NULL — an orphaned xref row survives its user being removed. |
email | text | — | YES | The email on the Stripe customer record at backfill time |
is_primary | boolean | false | NO | True iff this Stripe customer is referenced by a non-revoked api_keys.stripe_customer_id. A user with keys for separate product lines (e.g. LPO + Translation) can legitimately have more than one primary — resolve per-service, don't assume a single global one. |
revoked | boolean | false | NO | Soft-delete flag, set only by manual cleanup passes — the backfill script never touches it, so re-runs can't undo a deliberate revoke |
revoked_at | timestamptz | — | YES | When the row was revoked |
revoked_reason | text | — | YES | Why the row was revoked |
source | text | — | YES | aurora-linked (referenced by some api_key) or stripe-backfill (an orphan discovered only in Stripe, with no matching Aurora key) |
created_at | timestamptz | now() | NO | — |
updated_at | timestamptz | now() | NO | Set to now() on every backfill upsert |
stripe_customer_xref_pkey — UNIQUE on stripe_customer_iduser_idlower(email)Managed via bash scripts/kcc.sh stripe-xref [--view|--apply] (scripts/kcc_helpers/stripe_xref_backfill.py) — idempotent, dry-run by default. Never writes to Stripe, never cancels or refunds anything. Re-runs preserve revoked* columns (the upsert statement omits them on conflict).
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 |
pk_monthly_report_counts_api_key — UNIQUE on api_key7 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 |
idx_report_count_reset_month — on reset_monthreport_count_pkey — UNIQUE on api_keyapi_key → api_keys.id14 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 |
idx_report_usage_logs_api_key — on api_key_ididx_report_usage_logs_created_at — on created_atreport_usage_logs_pkey — UNIQUE on id20 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 |
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 analyticsidx_usage_logs_timestamp_api_key_id — composite on (timestamp, api_key_id) — per-key analyticsidx_usage_logs_timestamp_source — composite on (timestamp, source) — per-exchange analyticsidx_usage_logs_timestamp_cluster_node — composite on (timestamp, cluster_node) — per-node analyticsusage_logs_pkey — UNIQUE on idComposite 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.
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 |
idx_mv_usage_daily_day — UNIQUE on day. Required to enable REFRESH MATERIALIZED VIEW CONCURRENTLY (concurrent refresh never blocks readers).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.
exchange_asset_map for the live count) 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 |
asset_categories_category_name_key — UNIQUE on category_nameasset_categories_pkey — UNIQUE on id11 columns
Category default corrected 2026-08-12. The default was changed live from 'Infrastructure' to 'Uncategorized' on 2026-08-08, after a sweep found 216 of the (then) 300 prewarm assets had silently inherited the old default rather than a real category. The committed migration source that originally created this column has not been updated to match — the live schema is the correct source of truth here, confirmed directly via information_schema.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, bitstamp, kraken, gateio, cryptocom, okx) |
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 asset appears in /asset-categories (subscriber demo dropdown) |
category | text | Uncategorized | NO | Asset category for dropdown grouping — one of the 7 real categories (DeFi, Layer 2, AI & Data, Gaming & NFTs, Meme Coins, Major Currencies, Infrastructure). Uncategorized is a default-only value flagged by prewarm-audit, never a valid final state. |
prewarm | boolean | false | NO | Whether this asset gets a persistent WebSocket subscription at container startup |
icon_url | text | — | YES | CoinGecko coin image URL. Populated by kcc.sh sync-icons. Served to subscribers via /asset-categories response. |
volume_24h_usd | numeric | — | YES | NEW Real, exchange-reported 24-hour USD trading volume — one bulk-ticker API call per exchange, not per asset. Populated by kcc.sh refresh-volume. Powers the "Most Traded" sort toggle on the webhook/partner asset pickers, deliberately distinct from our own query-volume ranking. |
volume_updated_at | timestamptz | — | YES | NEW When volume_24h_usd was last refreshed. |
display_name | text | — | YES | NEW Human-readable asset name (e.g., "Bitcoin" for BTC), backfilled from CoinGecko by kcc.sh sync-icons (221/300 assets covered as of the 2026-08-09 backfill). Served via GET /asset-names for dashboard asset-picker hover tooltips. |
exchange_asset_map_exchange_name_asset_key — UNIQUE on exchange_name, assetexchange_asset_map_pkey — UNIQUE on ididx_exchange_asset_map_asset — on assetidx_exchange_asset_map_exchange — on exchange_name27 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 |
exchange_feeds_exchange_name_key — UNIQUE on exchange_nameexchange_feeds_pkey — UNIQUE on ididx_exchange_feeds_enabled — on enabled12 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 |
email_drafts_pkey — UNIQUE on id5 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 |
email_senders_email_key — UNIQUE on emailemail_senders_pkey — UNIQUE on id6 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 |
email_templates_pkey — UNIQUE on id6 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 |
email_translations_pkey — UNIQUE on idemail_translations_template_name_lang_field_name_key — UNIQUE on template_name, lang, field_name6 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 |
demo_leads_pkey — UNIQUE on ididx_demo_leads_email — on emailidx_demo_leads_ip — on client_ipapi_lang system). Contains 193 message keys × 12 languages = 2,316 rows. Languages: en, es, pt, fr, de, ru, hi, ur, it, ar, ja, zh. Italian replaced Punjabi (2026-06-11). Source of truth for Valkey cache — synced nightly by BeastReconciler and on-demand via POST /admin/reload-errmsg. Cached in Valkey as errmsg:{lang}:{key} keys (no TTL, refreshed nightly). Every handler in the codebase routes through GetErrorMessage(lang, key, fallback, replacements) with a 3-level fallback chain: requested language → English → hardcoded string.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 |
error_messages_message_key_lang_key — UNIQUE on message_key, langerror_messages_pkey — UNIQUE on id10 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) |
map_pins_pkey — UNIQUE on id16 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 |
idx_partner_apps_email — on contact_emailidx_partner_apps_status — on statuspartner_applications_pkey — UNIQUE on id8 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) |
idx_support_replies_created_at — on created_atidx_support_replies_ticket_id — on ticket_idsupport_replies_pkey — UNIQUE on idticket_id → support_tickets.id13 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) |
idx_support_tickets_category — on categoryidx_support_tickets_created_at — on created_at DESCidx_support_tickets_email — on emailidx_support_tickets_status — on statusidx_support_tickets_ticket_number — on ticket_numbersupport_tickets_pkey — UNIQUE on idsupport_tickets_ticket_number_key — UNIQUE on ticket_number7 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 |
cron_execution_logs_pkey — UNIQUE on id7 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 |
cron_reset_logs_executed_at_idx — on executed_atcron_reset_logs_job_name_idx — on job_namecron_reset_logs_pkey — UNIQUE on id6 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 |
cron_reset_summary_pkey — UNIQUE on ididx_cron_reset_summary_executed_at — on executed_atidx_cron_reset_summary_job_name — on job_nameidx_cron_reset_summary_job_name_executed_at — on job_name, executed_atidx_cron_reset_summary_status — on status8 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 |
idx_page_events_created_at — on created_at DESCidx_page_events_event_type — on event_typeidx_page_events_page_path — on page_pathpage_events_pkey — UNIQUE on id19 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 |
idx_page_views_created_at — on created_at DESCidx_page_views_page_path — on page_pathidx_page_views_visitor_id — on visitor_idpage_views_pkey — UNIQUE on id19 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 |
idx_webhook_subs_api_key — on api_key_ididx_webhook_subs_status — on statusidx_webhook_subs_tier — on tierwebhook_subscriptions_pkey — UNIQUE on idapi_key_id → api_keys.idassets JSONB column — see the Schema Note below for why, and for how the two row shapes coexist during the transition.15 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 | — | YES | Legacy column, pre-2026-08-21 rows only. Asset symbol delivered (e.g., BTC) on a one-row-per-asset row. NULL on every row written after the reshape — see assets instead. Made nullable 2026-08-21 (was NOT NULL); never backfilled. |
price | numeric | — | YES | Legacy column, pre-2026-08-21 rows only. Price value delivered. NULL on every row written after the reshape. Made nullable 2026-08-21 (was NOT NULL); never backfilled. |
source | text | — | YES | Legacy column, pre-2026-08-21 rows only. Price source exchange (e.g., coinbase-ws). NULL on every row written after the reshape. Made nullable 2026-08-21 (was NOT NULL); never backfilled. |
assets | jsonb | — | YES | Added 2026-08-21. Per-push asset detail as a JSONB array of {asset, price, source} objects, one element per asset delivered in this push+transport cycle. NULL means this is a legacy (pre-reshape) row — see asset/price/source instead. assets IS NOT NULL is the canonical way every reader distinguishes the two row shapes. |
delivery_method | text | — | NO | Protocol used: udp or https |
status | text | delivered | NO | Delivery status: delivered or failed (see the standing note below on the actual CHECK-constrained values) |
latency_ms | float8 | — | YES | Delivery round-trip time in milliseconds. One value per push+transport, not per asset — never duplicated into the assets array. |
attempt | integer | 1 | NO | Attempt number (1 = first try) |
error | text | — | YES | Error message if delivery failed |
sequence_number | bigint | 0 | NO | Per-container in-memory push counter, resets to 1 on every container restart. NOT a stable push identity on its own — see the Push Identity note below. |
subscription_kind | text | webhook | NO | Distinguishes billed Webhook Push subscribers (webhook) from free/unlimited Partner-tier push (partner) — same delivery engine, same table, kept separate for LRS/Delivery Reports scoping. |
configured_assets | integer | — | YES | Added 2026-08-18. Number of assets the subscription had configured AT THE MOMENT this push was written — a recorded fact, not inferred after the fact from delivery evidence. NULL means the row predates this column; not backfillable (the configured count at a past moment is unrecoverable). |
created_at | timestamptz | now() | NO | Delivery timestamp |
webhook_delivery_log_pkey — UNIQUE on id. Also the id-resolution path for the batched purge procedure's delete-by-id step, not merely a uniqueness guard — see Trinity-Beast-Unified-Logs.html for the measured scan-count evidence.idx_webhook_delivery_created — on created_at. Used by the retention purge's cutoff scan and every time-bounded query.idx_webhook_delivery_sub_created — composite on (subscription_id, created_at DESC), added 2026-08-17. Serves the dashboard Delivery Log panel's newest-rows-first query via a plain Index Scan; superseded and replaced the earlier single-column idx_webhook_delivery_sub (dropped 2026-08-18 as a structurally redundant prefix).idx_webhook_delivery_assets_gin — added 2026-08-21. GIN index on assets, built CONCURRENTLY so the live delivery write path was never locked. Backs JSONB containment queries (jsonb_array_elements(assets) + asset-symbol filtering) used by the per-asset report views on new-shape rows.Two indexes previously listed here no longer exist: idx_webhook_delivery_status (dropped 2026-08-19 — 0 scans across its entire life; every reader touches status inside an aggregate FILTER, never as a WHERE predicate, so it could never have been chosen) and idx_webhook_delivery_sub (dropped 2026-08-18, superseded by the composite index above). Both dropped CONCURRENTLY.
subscription_id → webhook_subscriptions.id ON DELETE CASCADEPrior to 2026-08-21 this table wrote one row per asset per push+transport — a 60-asset push over both transports meant up to 120 INSERTs on the delivery hot path for a single push cycle. It now writes one row per push+transport, with per-asset detail in the assets JSONB array. The real win is the round-trip reduction on the write path (measured 17.4× fewer inserts on real traffic), not disk space — see the space measurement below, which is a materially smaller number and is documented here specifically so it isn't overstated.
This is an additive, no-backfill change (Cory's direction, 2026-08-21 — "we have no users except our test accounts...we can backfill after structure changes if needed"): every row written before the change is left exactly as it was, forever. A row is legacy-shape if assets IS NULL (and carries real asset/price/source values); it is new-shape if assets IS NOT NULL (and asset/price/source are NULL). No conversion job exists or is planned — legacy rows simply age out under the existing 33-day retention purge, which is shape-agnostic (it only ever filters on created_at).
Measured space impact (real 24h of production data, one subscription, 49,666 legacy asset-rows reshaped into the equivalent 2,860 real pushes):
| Old shape (60 rows/push) | New shape (1 row/push) | Ratio | |
|---|---|---|---|
| Rows | 49,666 | 2,860 | 17.4× fewer rows |
| Heap (table data) | 7,168 KB | 4,096 KB | 1.75× |
| Indexes | 1,992 KB | 1,376 KB | 1.45× |
| Total on disk | 9,168 KB | 5,480 KB | 1.67× |
The gap between the row-count ratio (17.4×) and the byte ratio (1.67×) is the point, not an inconsistency: each old-shape row carried fixed per-row overhead (tuple header, index pointers) regardless of payload size, and that overhead existed once per asset. It now exists once per push. The actual payload — every asset's symbol, price, and source — did not shrink; it moved from being spread across many rows into one JSONB array. Fewer rows removes fixed overhead; it does not remove data. Do not extrapolate the 1.67× figure onto the pre-existing ~7.45M legacy rows — they are untouched and occupy exactly the space they always did until purged; the ratio applies only to rows written after 2026-08-21.
refresh_webhook_delivery_rollup() (see 10.3 below) reads both row shapes via a two-arm UNION ALL — one arm for new-shape rows (no push-boundary reconstruction needed, the row already is the push), one arm preserving the original sequence_number+1500ms-adjacency windowing for legacy rows. Once the 33-day purge clears the last legacy row, the legacy arm will permanently return zero rows.
webhook_delivery_log, added 2026-08-11 to back the dashboard's Delivery Reports panel without scanning raw per-asset rows live. Refreshed every 15 minutes by pg_cron, rescanning only the last 2 days of raw log — not a full-history rescan. Reads BOTH row shapes of the reshaped source table (see 10.2) via a two-arm UNION ALL in refresh_webhook_delivery_rollup(), so this rollup's own columns and meaning are unaffected by the 2026-08-21 reshape — only the source query changed.18 columns
| Column | Data Type | Default | Nullable | Description |
|---|---|---|---|---|
subscription_id PK FK → webhook_subscriptions.id | uuid | — | NO | Composite PK part 1 — which subscription this day's rollup belongs to |
day PK | date | — | NO | Composite PK part 2 — the calendar day being rolled up |
delivery_method PK | text | — | NO | Composite PK part 3 — udp or https, rolled up separately per transport |
subscription_kind | text | webhook | NO | Mirrors the source table's subscription_kind — webhook (billed) vs partner (free/unlimited) |
push_events | integer | 0 | NO | Total push events (real deliveries, not per-asset rows) this day/method — combined across both row shapes in the source table |
successful_events | integer | 0 | NO | Push events that delivered successfully |
failed_events | integer | 0 | NO | Push events that failed |
asset_rows | integer | 0 | NO | Total raw asset-price observations underlying this day's push events (one-row-per-asset count pre-reshape, JSONB array-length sum post-reshape) |
distinct_assets | integer | 0 | NO | Distinct asset symbols actually delivered this day (MAX across pushes, i.e. the largest set observed — see typical_assets_per_push for the more representative figure) |
configured_selected_assets | integer | 0 | NO | Live-joined size of the subscription's CURRENT configured asset list at refresh time — an inference, not a recorded fact. See avg_configured_per_push for the recorded equivalent, which supersedes this for coverage math whenever it's available. |
avg_assets_per_push | numeric(8,2) | 0 | NO | Average assets delivered per push this day (push-weighted mean) |
typical_assets_per_push | integer | — | YES | Added 2026-08-18. MODE (most frequent) distinct-asset count across the day's pushes — the coverage denominator signal MAX/distinct_assets can't provide, since a brief spike (e.g. a tier-walk test) shouldn't redefine an otherwise-stable day. NULL only if the day has no pushes. |
avg_configured_per_push | numeric(8,2) | — | YES | Added 2026-08-18. Push-weighted average of the RECORDED webhook_delivery_log.configured_assets value across the day's pushes — a fact, not an inference, whenever available. NULL unless every push that day carries a recorded value (a completeness guard — never a mixed-basis average of some recorded, some inferred). |
min_coverage_pct | numeric(6,2) | — | YES | Added 2026-08-18. Worst single push's coverage that day, as a percentage of THAT push's own recorded configured count — an intentional extreme, not an average. Read together with pushes_at_full_coverage: two days can share an identical average while one had a push collapse and the other was uniformly short. Same completeness guard as avg_configured_per_push. |
pushes_at_full_coverage | integer | — | YES | Added 2026-08-18. Count of pushes that delivered every asset they had configured (asset_count >= configured_assets) — the robust companion to min_coverage_pct; a single bad push can't move a count. Same completeness guard. |
avg_latency_ms | numeric(10,2) | 0 | NO | Average delivery latency this day |
p95_latency_ms | numeric(10,2) | 0 | NO | 95th-percentile delivery latency this day |
last_refreshed | timestamptz | now() | NO | When this row was last recomputed by the pg_cron refresh job |
webhook_delivery_daily_pkey — composite UNIQUE on (subscription_id, day, delivery_method)idx_webhook_delivery_daily_day — on daysubscription_id → webhook_subscriptions.id ON DELETE CASCADErefresh_webhook_delivery_rollup(since timestamptz, subscription_id uuid) — upserts rows for a given subscription (or all subscriptions when called from the pg_cron job) covering the window from since to now. The dashboard's manual "Refresh Now" button calls it directly with a 3-day window rather than a full rescan, since a 93-day scan on a high-volume subscription once exceeded the ALB's 120-second idle timeout. Rewritten 2026-08-21 as a two-arm UNION ALL to read both the legacy (one-row-per-asset) and new (one-row-per-push, JSONB) shapes of the source table — see 10.2's Schema Note. Source of record: trinity-beast-lpo-server/migrations/webhook-delivery-rollup.sql (base version) and webhook-delivery-jsonb-reshape.sql (the 2026-08-21 dual-shape rewrite).
webhook.cpmp-site.org / udp.cpmp-site.org:2681), added 2026-08-09. Replaced a hardcoded Go allow-list map so that adding or removing an Associate's access is a database write, live within 10 seconds, with zero ECS redeploy or restart.4 columns
| Column | Data Type | Default | Nullable | Description |
|---|---|---|---|---|
subscription_id PK FK → webhook_subscriptions.id | uuid | — | NO | The subscription granted access to the test receiver |
label | text | — | NO | Human-readable note on why this subscription was allow-listed |
added_by | text | — | NO | Who added the entry (operator email or automated process name) |
added_at | timestamptz | now() | NO | When the entry was added |
webhook_test_receiver_allowlist_pkey — UNIQUE on subscription_idsubscription_id → webhook_subscriptions.id ON DELETE CASCADERead via a 10-second in-memory cache (testReceiverAllowlistCacheTTL) inside BeastWebhook, so a request never round-trips to Aurora on the hot path. Managed via bash scripts/kcc.sh webhook-test-allowlist [list|add|remove|add-by-email] — see Trinity-Beast-Kiro-Command-Center.html. Applies identically to both delivery transports; a subscription's own delivery_method column is the only per-protocol filter, by design.
The 7 translation engine tables have been moved to their own dedicated document for independent versioning and translation lifecycle management:
Tables: translation_jobs, translation_job_events, translation_quotes, translation_parameters, bedrock_model_catalog, translation_tasks, language_profiles
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, rhema, 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 |
agent_profiles_pkey — UNIQUE on idagent_profiles_name_version_key — UNIQUE on (name, version)idx_agent_profiles_arn — on arnidx_agent_profiles_name_active — on (name, active)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 |
agent_profile_events_pkey — UNIQUE on ididx_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)trinity-beast-receipt Lambda. Each function performs a complete business operation atomically — user upsert, key generation, Stripe ID linking, and transaction recording in a single call. The Lambda invokes these via SELECT * FROM function_name($1, $2, ...) through the admin API. No raw SQL leaves the Lambda.| Function | Parameters | Returns | Purpose |
|---|---|---|---|
record_subscription |
(email, name, tier, amount, stripe_customer_id, stripe_subscription_id, lang, charge_id, metadata) |
user_id, api_key, api_key_id, txn_id |
New LPO subscription: upsert user, generate API key with tier limits from rate_limit_template, link Stripe IDs, record transaction. Auto-enables LRS for Unlimited/Lifetime. Uses NULLIF(charge_id, '') to handle subscription-mode sessions (no payment_intent). |
record_donation |
(email, name, amount, impact_type, lang, charge_id, stripe_customer_id) |
user_id, txn_id |
Donation: upsert user with preferred_lang, record transaction with impact_type and stripe_customer_id. |
record_lrs_addon |
(email, name, amount, stripe_customer_id, stripe_subscription_id, lang, charge_id) |
api_key_id, txn_id |
LRS add-on: find active API key by email, validate eligibility, set lrs_enabled=true, link LRS subscription ID, record transaction. |
record_webhook_subscription |
(email, name, tier, amount, stripe_customer_id, stripe_subscription_id, lang, charge_id, metadata) |
user_id, api_key, api_key_id, txn_id |
New webhook subscription: upsert user, generate API key with webhook tier limits, create webhook_subscriptions config row, link Stripe IDs, record transaction. |
record_refund |
(api_key_id, charge_id, refund_amount, refund_id, customer_id, tier) |
txn_id |
Subscription refund: set revoked=true and subscription_status='refunded' on the API key, record refund transaction. |
record_donation_refund |
(charge_id, refund_amount, refund_id) |
txn_id |
Donation refund: find original donation transaction by charge_id, record refund transaction linked to it. No API key involved. |
Design principle: Each function is a single atomic operation. No partial states, no race conditions, no multi-statement transactions over HTTP. If the function fails, nothing is committed. If the network drops after success, the receipt Lambda's idempotency check prevents re-processing. The Lambda never issues raw INSERT/UPDATE statements — all writes go through these functions.
40 tables across 12 logical groups, 421 columns total, 6 functions, plus 1 materialized view (mv_usage_daily) backing the analytics dashboard. Counts current as of 2026-08-12 — added stripe_customer_xref (2026-08-03), webhook_delivery_daily and webhook_test_receiver_allowlist (2026-08-09/11), and 3 new exchange_asset_map columns since the prior count.
| Group | Tables | Columns |
|---|---|---|
| Group 1 — Core: Users & Authentication | 3 | 38 |
| Group 2 — Configuration | 3 | 42 |
| Group 3 — Billing & Payments | 4 | 51 |
| Group 4 — Usage Logging | 4 | 45 |
| Group 5 — Exchange & Pricing | 3 | 42 |
| 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 | 4 | 48 |
| Group 11 — Translation Engine | → Separate document (7 tables, 118 columns) | |
| Group 12 — Agent Profiles | 2 | 23 |
| Group 13 — PostgreSQL Functions | 6 receipt processing functions (atomic lifecycle operations) | |
api_keys.user_id → users.idnewsletter_translations.newsletter_id → newsletters.idreport_count.api_key → api_keys.idsupport_replies.ticket_id → support_tickets.idwebhook_delivery_log.subscription_id → webhook_subscriptions.idwebhook_subscriptions.api_key_id → api_keys.idagent_profile_events.agent_profile_id → agent_profiles.idA 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. |