姚章浩
374 lines
14 KiB
Go
374 lines
14 KiB
Go
// Package migrations_test contains source-level contract tests for the
|
|
// 000003_message_audit migration. No real database connection is used.
|
|
package migrations_test
|
|
|
|
import (
|
|
"os"
|
|
"path/filepath"
|
|
"regexp"
|
|
"strings"
|
|
"testing"
|
|
)
|
|
|
|
func read003UpMigration(t *testing.T) string {
|
|
t.Helper()
|
|
data, err := os.ReadFile(filepath.Join(".", "000003_message_audit.up.sql"))
|
|
if err != nil {
|
|
t.Fatalf("read up migration: %v", err)
|
|
}
|
|
return string(data)
|
|
}
|
|
|
|
func read003DownMigration(t *testing.T) string {
|
|
t.Helper()
|
|
data, err := os.ReadFile(filepath.Join(".", "000003_message_audit.down.sql"))
|
|
if err != nil {
|
|
t.Fatalf("read down migration: %v", err)
|
|
}
|
|
return string(data)
|
|
}
|
|
|
|
// TestUp003UsesIfNotExists ensures every CREATE uses IF NOT EXISTS.
|
|
func TestUp003UsesIfNotExists(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
|
|
for _, stmt := range splitStatements(up) {
|
|
upper := strings.ToUpper(stmt)
|
|
if strings.Contains(upper, "CREATE TABLE") && !strings.Contains(upper, "CREATE TABLE IF NOT EXISTS") {
|
|
t.Errorf("CREATE TABLE without IF NOT EXISTS:\n%s", strings.TrimSpace(stmt))
|
|
}
|
|
if strings.Contains(upper, "CREATE INDEX") && !strings.Contains(upper, "CREATE INDEX IF NOT EXISTS") {
|
|
t.Errorf("CREATE INDEX without IF NOT EXISTS:\n%s", strings.TrimSpace(stmt))
|
|
}
|
|
if strings.Contains(upper, "CREATE UNIQUE INDEX") && !strings.Contains(upper, "IF NOT EXISTS") {
|
|
t.Errorf("CREATE UNIQUE INDEX without IF NOT EXISTS:\n%s", strings.TrimSpace(stmt))
|
|
}
|
|
}
|
|
|
|
// We expect 3 CREATE TABLE IF NOT EXISTS (channel_actors, channel_conversations, messages)
|
|
if got := countMatches(up, `(?i)CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS`); got < 3 {
|
|
t.Errorf("expected at least 3 CREATE TABLE IF NOT EXISTS statements, got %d", got)
|
|
}
|
|
// We expect at least 3 CREATE INDEX IF NOT EXISTS
|
|
if got := countMatches(up, `(?i)CREATE\s+(UNIQUE\s+)?INDEX\s+IF\s+NOT\s+EXISTS`); got < 3 {
|
|
t.Errorf("expected at least 3 CREATE INDEX IF NOT EXISTS statements, got %d", got)
|
|
}
|
|
}
|
|
|
|
// TestUp003CreatesChannelActorsTable checks the channel_actors table structure.
|
|
func TestUp003CreatesChannelActorsTable(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
if !strings.Contains(upper, "CREATE TABLE IF NOT EXISTS CHANNEL_ACTORS") {
|
|
t.Fatal("up migration must create channel_actors table")
|
|
}
|
|
if !strings.Contains(upper, "ID TEXT PRIMARY KEY") {
|
|
t.Error("channel_actors must have id TEXT PRIMARY KEY")
|
|
}
|
|
if !strings.Contains(upper, "TENANT_KEY") {
|
|
t.Error("channel_actors must have tenant_key column")
|
|
}
|
|
if !strings.Contains(upper, "CHANNEL") {
|
|
t.Error("channel_actors must have channel column")
|
|
}
|
|
if !strings.Contains(upper, "EXTERNAL_ACTOR_ID") {
|
|
t.Error("channel_actors must have external_actor_id column")
|
|
}
|
|
if !strings.Contains(upper, "ACTOR_TYPE") {
|
|
t.Error("channel_actors must have actor_type column")
|
|
}
|
|
if !strings.Contains(upper, "DISPLAY_NAME") {
|
|
t.Error("channel_actors must have display_name column")
|
|
}
|
|
if !strings.Contains(upper, "ACTOR_TYPE IN ('USER','BOT','SUPPORT')") {
|
|
t.Error("channel_actors actor_type must have CHECK (user, bot, support)")
|
|
}
|
|
if !strings.Contains(upper, "UNIQUE") && strings.Contains(upper, "TENANT_KEY") && strings.Contains(upper, "CHANNEL") && strings.Contains(upper, "EXTERNAL_ACTOR_ID") {
|
|
t.Error("channel_actors must have UNIQUE(tenant_key, channel, external_actor_id)")
|
|
}
|
|
}
|
|
|
|
// TestUp003CreatesChannelConversationsTable checks the channel_conversations table structure.
|
|
func TestUp003CreatesChannelConversationsTable(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
if !strings.Contains(upper, "CREATE TABLE IF NOT EXISTS CHANNEL_CONVERSATIONS") {
|
|
t.Fatal("up migration must create channel_conversations table")
|
|
}
|
|
if !strings.Contains(upper, "ID TEXT PRIMARY KEY") {
|
|
t.Error("channel_conversations must have id TEXT PRIMARY KEY")
|
|
}
|
|
if !strings.Contains(upper, "EXTERNAL_CONVERSATION_ID") {
|
|
t.Error("channel_conversations must have external_conversation_id column")
|
|
}
|
|
if !strings.Contains(upper, "CONVERSATION_TYPE") {
|
|
t.Error("channel_conversations must have conversation_type column")
|
|
}
|
|
if !strings.Contains(upper, "TITLE") {
|
|
t.Error("channel_conversations must have title column")
|
|
}
|
|
if !strings.Contains(upper, "STATUS") {
|
|
t.Error("channel_conversations must have status column")
|
|
}
|
|
if !strings.Contains(upper, "CONVERSATION_TYPE IN ('DIRECT','GROUP','SUPPORT','WEB')") {
|
|
t.Error("channel_conversations conversation_type must have CHECK (direct, group, support, web)")
|
|
}
|
|
if !strings.Contains(upper, "STATUS IN ('ACTIVE','ARCHIVED')") {
|
|
t.Error("channel_conversations status must have CHECK (active, archived)")
|
|
}
|
|
}
|
|
|
|
// TestUp003CreatesMessagesTable checks the messages table structure.
|
|
func TestUp003CreatesMessagesTable(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
if !strings.Contains(upper, "CREATE TABLE IF NOT EXISTS MESSAGES") {
|
|
t.Fatal("up migration must create messages table")
|
|
}
|
|
if !strings.Contains(upper, "ID TEXT PRIMARY KEY") {
|
|
t.Error("messages must have id TEXT PRIMARY KEY")
|
|
}
|
|
|
|
required := []string{
|
|
"TENANT_KEY", "CHANNEL", "CONVERSATION_ID", "ACTOR_ID",
|
|
"EXTERNAL_MESSAGE_ID", "CORRELATION_ID", "DIRECTION",
|
|
"ROLE", "MESSAGE_TYPE", "TEXT_CONTENT", "MEDIA_JSON",
|
|
"REPLY_TO_MESSAGE_ID", "STATUS", "ACTOR_NAME_SNAPSHOT",
|
|
"PROVIDER_CREATED_AT", "SAFE_METADATA_JSON",
|
|
}
|
|
for _, col := range required {
|
|
if !strings.Contains(upper, col) {
|
|
t.Errorf("messages must have %s column", col)
|
|
}
|
|
}
|
|
|
|
// Check constraints
|
|
if !strings.Contains(upper, "DIRECTION IN ('INBOUND','OUTBOUND','INTERNAL')") {
|
|
t.Error("messages direction must have CHECK (inbound, outbound, internal)")
|
|
}
|
|
if !strings.Contains(upper, "ROLE IN ('USER','ASSISTANT','SUPPORT','SYSTEM')") {
|
|
t.Error("messages role must have CHECK (user, assistant, support, system)")
|
|
}
|
|
if !strings.Contains(upper, "MESSAGE_TYPE IN ('TEXT','IMAGE','RICH_TEXT','CARD','FEEDBACK')") {
|
|
t.Error("messages message_type must have CHECK (text, image, rich_text, card, feedback)")
|
|
}
|
|
if !strings.Contains(upper, "STATUS IN ('RECEIVED','PROCESSING','GENERATED','SENT','FAILED')") {
|
|
t.Error("messages status must have CHECK (received, processing, generated, sent, failed)")
|
|
}
|
|
}
|
|
|
|
// TestUp003MessagesHasNoForbiddenColumns ensures secret/protocol columns are absent.
|
|
func TestUp003MessagesHasNoForbiddenColumns(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
forbidden := []string{
|
|
"RAW_PAYLOAD", "SESSIONWEBHOOK", "DOWNLOAD_CODE",
|
|
"DATAURI", "DATA_URI", "TOKEN", "REASONING",
|
|
}
|
|
for _, col := range forbidden {
|
|
if strings.Contains(upper, col) {
|
|
t.Errorf("messages must not contain forbidden column %q", col)
|
|
}
|
|
}
|
|
}
|
|
|
|
// TestUp003MessagesPartialUniqueIndex ensures external_message_id has a partial unique index.
|
|
func TestUp003MessagesPartialUniqueIndex(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
if !strings.Contains(upper, "EXTERNAL_MESSAGE_ID") {
|
|
t.Fatal("messages must have external_message_id")
|
|
}
|
|
// Partial unique index: UNIQUE (tenant_key, channel, external_message_id) WHERE external_message_id != ''
|
|
// The pattern should appear somewhere in the migration
|
|
hasPartial := strings.Contains(upper, "WHERE") &&
|
|
strings.Contains(upper, "EXTERNAL_MESSAGE_ID") &&
|
|
(strings.Contains(upper, "!=''") || strings.Contains(upper, "!= ''") || strings.Contains(upper, "IS NOT NULL"))
|
|
if !hasPartial {
|
|
t.Error("messages must have partial unique index on external_message_id (non-empty only)")
|
|
}
|
|
}
|
|
|
|
// TestUp003MessagesIndexes checks required indexes on messages.
|
|
func TestUp003MessagesIndexes(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
conversationIndex := regexp.MustCompile(`(?is)CREATE\s+INDEX\s+IF\s+NOT\s+EXISTS\s+idx_messages_tenant_conv_created\s+ON\s+messages\s*\(\s*tenant_key\s*,\s*conversation_id\s*,\s*created_at\s+DESC\s*\)`)
|
|
if !conversationIndex.MatchString(up) {
|
|
t.Error("messages must have index on tenant_key, conversation_id, created_at DESC")
|
|
}
|
|
correlationIndex := regexp.MustCompile(`(?is)CREATE\s+INDEX\s+IF\s+NOT\s+EXISTS\s+idx_messages_correlation\s+ON\s+messages\s*\(\s*tenant_key\s*,\s*correlation_id\s*\)`)
|
|
if !correlationIndex.MatchString(up) {
|
|
t.Error("messages correlation index must be tenant-scoped: (tenant_key, correlation_id)")
|
|
}
|
|
}
|
|
|
|
func TestUp003AvoidsRedundantLookupIndexes(t *testing.T) {
|
|
up := strings.ToLower(read003UpMigration(t))
|
|
for _, redundant := range []string{"idx_channel_actors_lookup", "idx_channel_conversations_lookup"} {
|
|
if strings.Contains(up, redundant) {
|
|
t.Errorf("migration must not create redundant index %s; UNIQUE already provides it", redundant)
|
|
}
|
|
}
|
|
}
|
|
|
|
// TestUp003AltersTurnsColumns ensures ALTER TABLE turns uses ADD COLUMN IF NOT EXISTS.
|
|
func TestUp003AltersTurnsColumns(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
// Must add request_message_id, answer_message_id, agent_id to turns
|
|
if !strings.Contains(upper, "ALTER TABLE TURNS") {
|
|
t.Fatal("up migration must ALTER TABLE turns")
|
|
}
|
|
if !strings.Contains(upper, "ADD COLUMN IF NOT EXISTS REQUEST_MESSAGE_ID") {
|
|
t.Error("turns must add request_message_id column with IF NOT EXISTS")
|
|
}
|
|
if !strings.Contains(upper, "ADD COLUMN IF NOT EXISTS ANSWER_MESSAGE_ID") {
|
|
t.Error("turns must add answer_message_id column with IF NOT EXISTS")
|
|
}
|
|
if !strings.Contains(upper, "ADD COLUMN IF NOT EXISTS AGENT_ID") {
|
|
t.Error("turns must add agent_id column with IF NOT EXISTS")
|
|
}
|
|
// agent_id must have NOT NULL DEFAULT ''
|
|
if !strings.Contains(upper, "AGENT_ID TEXT NOT NULL DEFAULT ''") {
|
|
t.Error("turns agent_id must be TEXT NOT NULL DEFAULT ''")
|
|
}
|
|
}
|
|
|
|
// TestDown003DropsInReverseOrder ensures tables are dropped in reverse dependency order.
|
|
func TestDown003DropsInReverseOrder(t *testing.T) {
|
|
down := read003DownMigration(t)
|
|
upper := strings.ToUpper(down)
|
|
|
|
if !strings.Contains(upper, "DROP TABLE IF EXISTS MESSAGES") {
|
|
t.Error("down migration must drop messages")
|
|
}
|
|
if !strings.Contains(upper, "DROP TABLE IF EXISTS CHANNEL_CONVERSATIONS") {
|
|
t.Error("down migration must drop channel_conversations")
|
|
}
|
|
if !strings.Contains(upper, "DROP TABLE IF EXISTS CHANNEL_ACTORS") {
|
|
t.Error("down migration must drop channel_actors")
|
|
}
|
|
|
|
// Verify reverse order: messages before conversations before actors
|
|
msgPos := strings.Index(upper, "DROP TABLE IF EXISTS MESSAGES")
|
|
convPos := strings.Index(upper, "DROP TABLE IF EXISTS CHANNEL_CONVERSATIONS")
|
|
actorPos := strings.Index(upper, "DROP TABLE IF EXISTS CHANNEL_ACTORS")
|
|
|
|
if msgPos < 0 || convPos < 0 || actorPos < 0 {
|
|
t.Fatal("down migration missing DROP statements")
|
|
}
|
|
if msgPos > convPos || convPos > actorPos {
|
|
t.Error("down migration must drop messages first, then channel_conversations, then channel_actors (reverse dependency order)")
|
|
}
|
|
}
|
|
|
|
// TestDown003DropsTurnColumns ensures down removes the added turn columns.
|
|
func TestDown003DropsTurnColumns(t *testing.T) {
|
|
down := read003DownMigration(t)
|
|
upper := strings.ToUpper(down)
|
|
|
|
if !strings.Contains(upper, "DROP COLUMN IF EXISTS REQUEST_MESSAGE_ID") {
|
|
t.Error("down migration must drop request_message_id from turns")
|
|
}
|
|
if !strings.Contains(upper, "DROP COLUMN IF EXISTS ANSWER_MESSAGE_ID") {
|
|
t.Error("down migration must drop answer_message_id from turns")
|
|
}
|
|
if !strings.Contains(upper, "DROP COLUMN IF EXISTS AGENT_ID") {
|
|
t.Error("down migration must drop agent_id from turns")
|
|
}
|
|
}
|
|
|
|
// TestUp003DoesNotModify000001Or000002 ensures migration 003 doesn't recreate existing tables.
|
|
func TestUp003DoesNotModifySessionMappingsOrTurnsTables(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
// Must not re-create session_mappings or turns tables
|
|
if strings.Contains(upper, "CREATE TABLE IF NOT EXISTS SESSION_MAPPINGS") {
|
|
t.Error("migration 003 must not recreate session_mappings table")
|
|
}
|
|
if strings.Contains(upper, "CREATE TABLE IF NOT EXISTS TURNS") {
|
|
t.Error("migration 003 must not recreate turns table")
|
|
}
|
|
|
|
// Must not create or modify 000002's media_json (already exists)
|
|
// It's fine if it doesn't mention media_json at all
|
|
}
|
|
|
|
// TestUp003JSONBDefaults ensures JSONB columns use NOT NULL DEFAULT '[]' or '{}'.
|
|
func TestUp003JSONBDefaults(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
if !strings.Contains(upper, "MEDIA_JSON JSONB NOT NULL DEFAULT '[]'::JSONB") {
|
|
t.Error("messages media_json must be JSONB NOT NULL DEFAULT '[]'::JSONB")
|
|
}
|
|
if !strings.Contains(upper, "SAFE_METADATA_JSON JSONB NOT NULL DEFAULT '{}'::JSONB") {
|
|
t.Error("messages safe_metadata_json must be JSONB NOT NULL DEFAULT '{}'::JSONB")
|
|
}
|
|
}
|
|
|
|
// TestUp003MessagesSelfReference ensures reply_to_message_id is nullable and self-referencing.
|
|
func TestUp003MessagesSelfReference(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
upper := strings.ToUpper(up)
|
|
|
|
if !strings.Contains(upper, "REPLY_TO_MESSAGE_ID") {
|
|
t.Error("messages must have reply_to_message_id column")
|
|
}
|
|
}
|
|
|
|
func TestUp003TurnMessageForeignKeys(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
for _, field := range []string{"request_message_id", "answer_message_id"} {
|
|
pattern := `(?i)` + field + `\s+text\s+references\s+messages\s*\(\s*id\s*\)`
|
|
if !regexp.MustCompile(pattern).MatchString(up) {
|
|
t.Errorf("turns.%s must reference messages(id)", field)
|
|
}
|
|
}
|
|
}
|
|
|
|
// TestUp003MessagesForeignKeys ensures core relations are enforced by PostgreSQL.
|
|
func TestUp003MessagesForeignKeys(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
checks := []struct {
|
|
name string
|
|
pattern string
|
|
}{
|
|
{"conversation", `(?i)conversation_id\s+text\s+not\s+null\s+references\s+channel_conversations\s*\(\s*id\s*\)`},
|
|
{"actor", `(?i)actor_id\s+text\s+references\s+channel_actors\s*\(\s*id\s*\)`},
|
|
{"reply", `(?i)reply_to_message_id\s+text\s+references\s+messages\s*\(\s*id\s*\)`},
|
|
}
|
|
for _, check := range checks {
|
|
if !regexp.MustCompile(check.pattern).MatchString(up) {
|
|
t.Errorf("messages missing %s foreign key", check.name)
|
|
}
|
|
}
|
|
}
|
|
|
|
// TestUp003NoReservedWords ensures no bare reserved words are used as column names.
|
|
func TestUp003NoReservedWords(t *testing.T) {
|
|
up := read003UpMigration(t)
|
|
|
|
// Check for common reserved words used as column identifiers
|
|
// "references" is the most dangerous in PostgreSQL
|
|
bareRefs := regexp.MustCompile(`(?im)^\s*REFERENCES\s+`)
|
|
if bareRefs.MatchString(up) {
|
|
// Exclude FOREIGN KEY ... REFERENCES pattern
|
|
lines := strings.Split(up, "\n")
|
|
for _, line := range lines {
|
|
trimmed := strings.TrimSpace(line)
|
|
upperLine := strings.ToUpper(trimmed)
|
|
if strings.HasPrefix(upperLine, "REFERENCES ") && !strings.Contains(upperLine, "FOREIGN KEY") {
|
|
t.Errorf("reserved word 'references' used as column name: %s", trimmed)
|
|
}
|
|
}
|
|
}
|
|
}
|