[Proposal] Gateway Controller DB Schema — Per-Resource-Type Tables

[renuka-fernando]

Summary

Refactor the gateway controller's database schema from a flat two-table design (deployments + deployment_configs) into a base artifacts table with per-resource-type child tables, following the same pattern already used by Platform API. Key changes:

• Rename deployments → artifacts as the shared base table for all resource types
• Replace the single deployment_configs table with 5 type-specific tables: rest_apis, websub_apis, llm_providers, llm_proxies, mcp_proxies
• Store only the source configuration in each type table — the derived api.APIConfiguration is regenerated at load time via the existing Transformer pipeline
• Rename id → uuid across all tables for consistency with Platform API
• Change gateway_id default from 'platform-gateway-id' to 'default'
• Remove context column (no longer unique — multiple resources (Artifacts) can share the same context path)
• Remove deployed_version column (derived xDS state, never queried from DB)
• Fix unique constraints to be scoped per kind

Motivation

1. All resource types share a single table

Today, REST APIs, LLM providers, LLM proxies, MCP proxies, and WebSub APIs all live in the same deployments table, distinguished only by a kind column. The deployment_configs table stores their configuration as a JSON blob with two columns:

CREATE TABLE IF NOT EXISTS deployment_configs (
    id TEXT PRIMARY KEY,
    configuration TEXT NOT NULL,        -- Derived api.APIConfiguration (always)
    source_configuration TEXT,          -- Original typed config (LLM/MCP only)
    FOREIGN KEY(id) REFERENCES deployments(id) ON DELETE CASCADE
);

This makes it impossible to:

• Add type-specific columns or constraints — for example, an LLM proxy depends on an LLM provider, but today this relationship lives only in a JSON blob with no DB-level enforcement. With per-type tables, llm_proxies.provider_uuid can be a proper FK with ON DELETE RESTRICT, preventing deletion of a provider that still has proxies depending on it.
• Enforce type-specific validation at the database level
• Query type-specific fields without deserializing JSON

2. Redundant configuration storage

Both configuration (the derived api.APIConfiguration) and source_configuration (the original typed config) are persisted. But the derived config is always regeneratable from the source config via the existing Transformer pipeline:

Kind	Source Config	Derived Config	Derivable?
RestApi	IS the api.APIConfiguration	Same as source	N/A
WebSubApi	IS the api.APIConfiguration	Same as source	N/A
LlmProvider	LLMProviderConfiguration	Transformed APIConfiguration	Yes (deterministic)
LlmProxy	LLMProxyConfiguration	Transformed APIConfiguration	Yes (needs provider loaded first)
Mcp	MCPProxyConfiguration	Transformed APIConfiguration	Yes (deterministic)

Storing the derived config wastes space and creates a sync risk if the transformation logic changes.

3. Incorrect unique constraints

The current constraints don't account for resource kind:

UNIQUE(display_name, version, gateway_id)  -- Wrong: different kinds can share display_name+version
UNIQUE(handle, gateway_id)                 -- Wrong: different kinds can share the same handle

A RestApi named "my-service" and an LlmProvider named "my-service" are different resources and should be allowed to coexist.

4. Unnecessary columns

• context: Was used for conflict detection (unique context paths). Context is no longer unique — multiple resources can share the same context path. The value already lives in the configuration JSON.
• deployed_version: Tracks the xDS snapshot version a config was last deployed under. This is derived state managed in-memory by the xDS server — it's written to DB but never queried or used for any business logic.

5. Inconsistent naming

• Primary key is id in gateway controller but uuid in Platform API
• gateway_id defaults to 'platform-gateway-id' which is oddly specific
• api_keys.apiId uses camelCase while all other columns use snake_case

Proposed Design

Target Schema

artifacts (renamed from deployments)
├── uuid TEXT PK                              (renamed from id)
├── gateway_id TEXT DEFAULT 'default'         (changed default)
├── kind TEXT NOT NULL
├── handle TEXT NOT NULL
├── display_name TEXT NOT NULL
├── version TEXT NOT NULL
├── status TEXT NOT NULL                      (pending/deployed/failed/undeployed)
├── created_at TIMESTAMP
├── updated_at TIMESTAMP
├── deployed_at TIMESTAMP
├── UNIQUE(gateway_id, kind, display_name, version)
└── UNIQUE(gateway_id, kind, handle)

rest_apis
├── uuid TEXT PK → artifacts(uuid) ON DELETE CASCADE
└── configuration TEXT NOT NULL               (source config JSON)

websub_apis
├── uuid TEXT PK → artifacts(uuid) ON DELETE CASCADE
└── configuration TEXT NOT NULL

llm_providers
├── uuid TEXT PK → artifacts(uuid) ON DELETE CASCADE
└── configuration TEXT NOT NULL

llm_proxies
├── uuid TEXT PK → artifacts(uuid) ON DELETE CASCADE
├── configuration TEXT NOT NULL
└── provider_uuid TEXT NOT NULL → llm_providers(uuid) ON DELETE RESTRICT

mcp_proxies
├── uuid TEXT PK → artifacts(uuid) ON DELETE CASCADE
└── configuration TEXT NOT NULL

Removed tables: deployment_configs
Removed columns: context, deployed_version

Referential integrity via ON DELETE strategy:

FK	ON DELETE	Rationale
rest_apis.uuid → artifacts	CASCADE	Deleting an artifact removes its type-specific row automatically
llm_proxies.uuid → artifacts	CASCADE	Same — artifact deletion cleans up the proxy row
llm_proxies.provider_uuid → llm_providers	RESTRICT	Prevents deleting an LLM provider that has proxies depending on it. The provider must have all its proxies removed first. This enforces the dependency at the DB level rather than relying on application-level checks.
api_keys.artifact_uuid → artifacts	CASCADE	Deleting an artifact removes its API keys

Other table renames:

• certificates.id → certificates.uuid
• llm_provider_templates.id → llm_provider_templates.uuid
• api_keys.id → api_keys.uuid
• api_keys.apiId → api_keys.artifact_uuid (FK → artifacts(uuid))
• All gateway_id defaults changed to 'default'

Data Flow Changes

Write path (create/update):

1. Parse raw input → type-specific source config
2. Transform via Transformer → derived api.APIConfiguration (for in-memory use)
3. Save only source config to type-specific table + base metadata to artifacts
4. Add fully-hydrated StoredConfig (with both source and derived) to in-memory ConfigStore

Read path (startup loader):

1. GetAllConfigs() reads from artifacts JOIN type tables
2. For RestApi/WebSubApi: source config IS the api.APIConfiguration — unmarshal directly
3. For LlmProvider/LlmProxy/Mcp: unmarshal source config, run Transformer to regenerate derived api.APIConfiguration
4. Add fully-hydrated config to in-memory cache

Read path (REST handlers — unchanged):

• GetAPIById: Returns cfg.Configuration from in-memory cache (or DB for RestApi)
• GetLLMProviderById: Returns cfg.SourceConfiguration from in-memory cache

SQL Query Strategy

Single-record reads (GetConfig, GetConfigByHandle, GetConfigByNameVersion):
Two-step approach — query artifacts for base record (including kind), then query the correct type table.

Bulk reads (GetAllConfigs):
UNION ALL across all 5 type tables joined with artifacts.

Kind-filtered reads (GetAllConfigsByKind):
Single JOIN between artifacts and the one relevant type table.

Writes: Insert into artifacts + correct type table in a transaction.

Deletes: Delete from artifacts — CASCADE handles the type table row.

What Does NOT Change

• models.StoredConfig struct — keeps both Configuration and SourceConfiguration fields. They're populated differently (DB returns source only; create/update populates both in memory).
• Storage interface — generic methods (SaveConfig, GetConfig, etc.) stay unchanged
• ConfigStore (in-memory cache) — unchanged
• REST API contract — all handler responses remain the same
• xDS translator — consumes StoredConfig from in-memory cache, unaffected

Migration

• SQLite: Breaking change allowed (clean restart per project convention). Schema version 9 → 10. Migration renames table, creates type tables, migrates data from deployment_configs to the correct type table per kind, drops deployment_configs.
• PostgreSQL: Same logic with ALTER TABLE RENAME, INSERT...SELECT for data migration.

Alignment with Platform API

This brings the gateway controller schema in line with Platform API's existing pattern:

Concept	Platform API	Gateway Controller (proposed)
Base table	artifacts	artifacts
PK column	uuid VARCHAR(40)	uuid TEXT
Type tables	rest_apis, llm_providers, llm_proxies	rest_apis, websub_apis, llm_providers, llm_proxies, mcp_proxies
Config storage	configuration TEXT in type table	configuration TEXT in type table
Identity	UNIQUE(name, version, organization_uuid)	UNIQUE(gateway_id, kind, display_name, version)

[malinthaprasan]

1. configuration in each API type table contains the original yaml config or a processed one? Are we using a single format? (eg always yaml).

Read path (startup loader):

1. GetAllConfigs() reads from artifacts JOIN type tables
2. For RestApi/WebSubApi: source config IS the api.APIConfiguration — unmarshal directly
3. For LlmProvider/LlmProxy/Mcp: unmarshal source config, run Transformer to regenerate derived api.APIConfiguration
4. Add fully-hydrated config to in-memory cache

When there are many APIs, will this runtime parsing cause any slowness during startup? (In other words, is there a need to store any processed entities in DB? If there is no issue in startup, its okay to avoid this.)

3. Migration: I think we don't need to migrate existing databases at this point and complicate the code. Open to discuss.

+1 to the overal proposal.

[renuka-fernando]

1. configuration in each API type table contains the original yaml config or a processed one? Are we using a single format? (eg always yaml).

The original one is parsed to the Resource DTO and store the JSON Marshalled config.

Read path (startup loader):

1. GetAllConfigs() reads from artifacts JOIN type tables
2. For RestApi/WebSubApi: source config IS the api.APIConfiguration — unmarshal directly
3. For LlmProvider/LlmProxy/Mcp: unmarshal source config, run Transformer to regenerate derived api.APIConfiguration
4. Add fully-hydrated config to in-memory cache

When there are many APIs, will this runtime parsing cause any slowness during startup? (In other words, is there a need to store any processed entities in DB? If there is no issue in startup, its okay to avoid this.)

This is a just go logic which translate the actual source into internal structure. This should not get more time to start the gateway.

3. Migration: I think we don't need to migrate existing databases at this point and complicate the code. Open to discuss.

Yes, I will remove that. I wanted to remove that, but wanted to do that as a separate effort (PR).

[renuka-fernando]

Regarding

The original one is parsed to the Resource DTO and store the JSON Marshalled config.

Do we need to have another internal model for resources instead of using DTO?
DTO -> parse to JSON -> Store in DB

Instead
DTO -> Model -> parse to JSON -> Store in DB

So we can safely do changes to DTO
@malinthaprasan wdyt?

[dushaniw]

+1 for the proposal.

The status filed in artifacts, will no longer have failed or pending. It will be always the desired state. Shall we rename that as well to desired_state. Also, shall we add the deployment_id as well to the schema with this change.