-- 01_schema.sql: Schema verification — tables, indexes, functions, triggers, roles -- pg_regress test for ulak -- ============================================================================ -- TABLES -- ============================================================================ -- Verify endpoints table exists with expected columns SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'ulak' AND table_name = 'endpoints' ORDER BY ordinal_position; column_name | data_type | is_nullable -----------------------+--------------------------+------------- id | bigint | NO name | text | NO protocol | text | NO config | jsonb | NO retry_policy | jsonb | YES enabled | boolean | NO description | text | YES circuit_state | text | NO circuit_failure_count | integer | NO circuit_opened_at | timestamp with time zone | YES circuit_half_open_at | timestamp with time zone | YES last_success_at | timestamp with time zone | YES last_failure_at | timestamp with time zone | YES created_at | timestamp with time zone | YES updated_at | timestamp with time zone | YES (15 rows) -- Verify queue table exists with expected columns SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'ulak' AND table_name = 'queue' ORDER BY ordinal_position; column_name | data_type | is_nullable -----------------------+--------------------------+------------- id | bigint | NO endpoint_id | bigint | NO payload | jsonb | NO status | text | NO retry_count | integer | NO next_retry_at | timestamp with time zone | YES last_error | text | YES processing_started_at | timestamp with time zone | YES completed_at | timestamp with time zone | YES failed_at | timestamp with time zone | YES priority | smallint | NO scheduled_at | timestamp with time zone | YES idempotency_key | text | YES correlation_id | uuid | YES expires_at | timestamp with time zone | YES payload_hash | text | YES ordering_key | text | YES headers | jsonb | YES metadata | jsonb | YES response | jsonb | YES created_at | timestamp with time zone | YES updated_at | timestamp with time zone | YES (22 rows) -- ============================================================================ -- INDEXES -- ============================================================================ -- Verify indexes exist on queue table SELECT indexname FROM pg_indexes WHERE schemaname = 'ulak' AND tablename = 'queue' ORDER BY indexname; indexname ------------------------------- idx_queue_cleanup idx_queue_created_at idx_queue_endpoint_pending idx_queue_endpoint_processing idx_queue_endpoint_terminal idx_queue_idempotency_key idx_queue_next_retry idx_queue_ordering_pending idx_queue_ordering_processing idx_queue_processing idx_queue_worker_fetch queue_pkey (12 rows) -- Verify indexes exist on endpoints table SELECT indexname FROM pg_indexes WHERE schemaname = 'ulak' AND tablename = 'endpoints' ORDER BY indexname; indexname ----------------------------- endpoints_name_key endpoints_pkey idx_queue_endpoints_enabled (3 rows) -- ============================================================================ -- FUNCTIONS -- ============================================================================ -- Verify all expected functions exist SELECT p.proname, pg_get_function_arguments(p.oid) AS args FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = 'ulak' ORDER BY p.proname; proname | args --------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- _check_backpressure | p_projected_additional bigint DEFAULT 0 _shmem_metrics | alter_endpoint | endpoint_name text, new_config jsonb archive_completed_messages | p_older_than_seconds integer DEFAULT 3600, p_batch_size integer DEFAULT 1000 archive_single_to_dlq | p_message_id bigint cleanup_dlq | cleanup_event_log | cleanup_old_archive_partitions | p_retention_months integer DEFAULT 6 create_endpoint | name text, protocol text, config jsonb create_event_type | p_name text, p_description text DEFAULT NULL::text, p_schema jsonb DEFAULT NULL::jsonb disable_endpoint | p_endpoint_name text dlq_summary | drop_endpoint | endpoint_name text drop_event_type | p_name text enable_endpoint | p_endpoint_name text enable_fast_mode | get_endpoint_health | p_endpoint_name text DEFAULT NULL::text get_worker_status | health_check | maintain_archive_partitions | p_months_ahead integer DEFAULT 3 mark_expired_messages | metrics | notify_new_message | prevent_payload_modification | publish | p_event_type text, p_payload jsonb publish_batch | p_events jsonb redrive_all | redrive_endpoint | p_endpoint_name text redrive_message | p_dlq_id bigint replay_message | p_archive_message_id bigint replay_range | p_endpoint_id bigint, p_from_ts timestamp with time zone, p_to_ts timestamp with time zone, p_status text DEFAULT NULL::text reset_circuit_breaker | p_endpoint_name text send | endpoint_name text, payload jsonb send_batch | p_endpoint_name text, p_payloads jsonb[] send_batch_with_priority | p_endpoint_name text, p_payloads jsonb[], p_priority smallint DEFAULT 0 send_with_options | p_endpoint_name text, p_payload jsonb, p_priority smallint DEFAULT 0, p_scheduled_at timestamp with time zone DEFAULT NULL::timestamp with time zone, p_idempotency_key text DEFAULT NULL::text, p_correlation_id uuid DEFAULT NULL::uuid, p_expires_at timestamp with time zone DEFAULT NULL::timestamp with time zone, p_ordering_key text DEFAULT NULL::text subscribe | p_event_type text, p_endpoint_name text, p_filter jsonb DEFAULT NULL::jsonb unsubscribe | p_subscription_id bigint update_circuit_breaker | p_endpoint_id bigint, p_success boolean update_updated_at | validate_endpoint_config | protocol text, config jsonb (41 rows) -- ============================================================================ -- TRIGGERS -- ============================================================================ -- Verify triggers on endpoints table SELECT tgname FROM pg_trigger WHERE tgrelid = 'ulak.endpoints'::regclass AND NOT tgisinternal ORDER BY tgname; tgname ----------------------------- update_endpoints_updated_at (1 row) -- Verify triggers on queue table SELECT tgname FROM pg_trigger WHERE tgrelid = 'ulak.queue'::regclass AND NOT tgisinternal ORDER BY tgname; tgname ------------------------------ enforce_payload_immutability notify_new_message_trigger update_queue_updated_at (3 rows) -- ============================================================================ -- ROLES -- ============================================================================ -- Verify RBAC roles exist SELECT rolname FROM pg_roles WHERE rolname LIKE 'ulak_%' ORDER BY rolname; rolname ------------------ ulak_admin ulak_application ulak_monitor (3 rows)