56 lines
2.2 KiB
SQL
56 lines
2.2 KiB
SQL
-- Migration: 003_CreateEventSchemasTable
|
|
-- Description: Creates table for storing event schema versions and upcast relationships
|
|
-- Phase: 5 (Schema Evolution & Versioning)
|
|
|
|
-- Create event_schemas table
|
|
CREATE TABLE IF NOT EXISTS event_streaming.event_schemas
|
|
(
|
|
event_type VARCHAR(500) NOT NULL,
|
|
version INTEGER NOT NULL,
|
|
clr_type_name TEXT NOT NULL,
|
|
json_schema TEXT NULL,
|
|
upcast_from_type TEXT NULL,
|
|
upcast_from_version INTEGER NULL,
|
|
registered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
|
|
CONSTRAINT pk_event_schemas PRIMARY KEY (event_type, version),
|
|
CONSTRAINT chk_version_positive CHECK (version > 0),
|
|
CONSTRAINT chk_upcast_version_valid CHECK (
|
|
(version = 1 AND upcast_from_type IS NULL AND upcast_from_version IS NULL) OR
|
|
(version > 1 AND upcast_from_type IS NOT NULL AND upcast_from_version IS NOT NULL AND upcast_from_version = version - 1)
|
|
)
|
|
);
|
|
|
|
-- Create index for getting latest version
|
|
CREATE INDEX IF NOT EXISTS idx_event_schemas_latest_version
|
|
ON event_streaming.event_schemas (event_type, version DESC);
|
|
|
|
-- Create index for looking up by CLR type
|
|
CREATE INDEX IF NOT EXISTS idx_event_schemas_clr_type
|
|
ON event_streaming.event_schemas (clr_type_name);
|
|
|
|
-- Add comment
|
|
COMMENT ON TABLE event_streaming.event_schemas IS
|
|
'Stores event schema versions for automatic upcasting and schema evolution';
|
|
|
|
COMMENT ON COLUMN event_streaming.event_schemas.event_type IS
|
|
'Logical event type name (e.g., UserCreatedEvent)';
|
|
|
|
COMMENT ON COLUMN event_streaming.event_schemas.version IS
|
|
'Schema version number (starts at 1, increments sequentially)';
|
|
|
|
COMMENT ON COLUMN event_streaming.event_schemas.clr_type_name IS
|
|
'Assembly-qualified CLR type name for deserialization';
|
|
|
|
COMMENT ON COLUMN event_streaming.event_schemas.json_schema IS
|
|
'Optional JSON Schema (Draft 7) for external consumers';
|
|
|
|
COMMENT ON COLUMN event_streaming.event_schemas.upcast_from_type IS
|
|
'CLR type of the previous version (null for version 1)';
|
|
|
|
COMMENT ON COLUMN event_streaming.event_schemas.upcast_from_version IS
|
|
'Previous version number this can upcast from (null for version 1)';
|
|
|
|
COMMENT ON COLUMN event_streaming.event_schemas.registered_at IS
|
|
'When this schema version was registered';
|