@@ -12,10 +12,81 @@ static MIGRATOR: sqlx::migrate::Migrator = sqlx::migrate!("../../migrations");
1212
1313/// Run all pending Buzz database migrations.
1414pub async fn run_migrations ( pool : & PgPool ) -> Result < ( ) > {
15+ reject_legacy_nip_rs_cardinality_ambiguity ( pool) . await ?;
1516 MIGRATOR . run ( pool) . await ?;
1617 Ok ( ( ) )
1718}
1819
20+ /// Migration 0007 is checksum-frozen and predates exact NIP-RS tag-cardinality
21+ /// enforcement. A populated database still on 0001-0006 must not let 0007
22+ /// irreversibly purge duplicate-tag history. Fail before sqlx starts its
23+ /// migration transaction so an operator can inspect and repair those rows.
24+ async fn reject_legacy_nip_rs_cardinality_ambiguity ( pool : & PgPool ) -> Result < ( ) > {
25+ let migrations_table: Option < String > =
26+ sqlx:: query_scalar ( "SELECT to_regclass('_sqlx_migrations')::text" )
27+ . fetch_one ( pool)
28+ . await ?;
29+ if migrations_table. is_none ( ) {
30+ return Ok ( ( ) ) ;
31+ }
32+ let applied: Option < i64 > =
33+ sqlx:: query_scalar ( "SELECT max(version) FROM _sqlx_migrations WHERE success" )
34+ . fetch_one ( pool)
35+ . await ?;
36+ if applied. is_none_or ( |version| version >= 7 ) {
37+ return Ok ( ( ) ) ;
38+ }
39+
40+ let ambiguous: bool = sqlx:: query_scalar (
41+ "SELECT EXISTS (\
42+ SELECT 1 FROM events e \
43+ WHERE e.kind = 30078 \
44+ AND e.d_tag ~ '^read-state:[0-9a-f]{32}$' \
45+ AND (\
46+ jsonb_typeof(e.tags) IS DISTINCT FROM 'array' \
47+ OR (\
48+ EXISTS (\
49+ SELECT 1 FROM jsonb_array_elements(\
50+ CASE WHEN jsonb_typeof(e.tags) = 'array' THEN e.tags ELSE '[]'::jsonb END\
51+ ) tag \
52+ WHERE tag = '[\" t\" , \" read-state\" ]'::jsonb\
53+ ) \
54+ AND (\
55+ (SELECT count(*) FROM jsonb_array_elements(\
56+ CASE WHEN jsonb_typeof(e.tags) = 'array' THEN e.tags ELSE '[]'::jsonb END\
57+ ) tag \
58+ WHERE jsonb_typeof(tag) = 'array' \
59+ AND tag->0 = '\" d\" '::jsonb) <> 1 \
60+ OR NOT EXISTS (\
61+ SELECT 1 FROM jsonb_array_elements(\
62+ CASE WHEN jsonb_typeof(e.tags) = 'array' THEN e.tags ELSE '[]'::jsonb END\
63+ ) tag \
64+ WHERE jsonb_typeof(tag) = 'array' \
65+ AND jsonb_array_length(tag) >= 2 \
66+ AND jsonb_typeof(tag->1) = 'string' \
67+ AND tag->>0 = 'd' \
68+ AND tag->>1 = e.d_tag\
69+ ) \
70+ OR (SELECT count(*) FROM jsonb_array_elements(\
71+ CASE WHEN jsonb_typeof(e.tags) = 'array' THEN e.tags ELSE '[]'::jsonb END\
72+ ) tag WHERE tag = '[\" t\" , \" read-state\" ]'::jsonb) <> 1\
73+ )\
74+ )\
75+ )\
76+ )",
77+ )
78+ . fetch_one ( pool)
79+ . await ?;
80+
81+ if ambiguous {
82+ return Err ( crate :: DbError :: InvalidData (
83+ "NIP-RS migration blocked: pre-0007 database contains kind-30078 rows with ambiguous d/t tag cardinality; repair or remove those nonconforming rows before retrying"
84+ . into ( ) ,
85+ ) ) ;
86+ }
87+ Ok ( ( ) )
88+ }
89+
1990#[ cfg( test) ]
2091mod tests {
2192 use super :: * ;
@@ -471,7 +542,7 @@ mod tests {
471542 let mut migrations: Vec < _ > = MIGRATOR . iter ( ) . collect ( ) ;
472543 migrations. sort_by_key ( |migration| migration. version ) ;
473544
474- assert_eq ! ( migrations. len( ) , 6 ) ;
545+ assert_eq ! ( migrations. len( ) , 11 ) ;
475546 assert_eq ! ( migrations[ 0 ] . version, 1 ) ;
476547 assert_eq ! ( & * migrations[ 0 ] . description, "initial schema" ) ;
477548 assert ! ( migrations[ 0 ]
@@ -555,6 +626,90 @@ mod tests {
555626 ) ;
556627 }
557628 assert ! ( !migrations[ 0 ] . sql. as_str( ) . contains( "moderation_reports" ) ) ;
629+
630+ // NIP-RS retention is additive and boot-safe: seed replay watermarks
631+ // before deleting payload history, without rewriting search storage.
632+ assert_eq ! ( migrations[ 6 ] . version, 7 ) ;
633+ assert ! ( migrations[ 6 ]
634+ . sql
635+ . as_str( )
636+ . contains( "LOCK TABLE events IN SHARE ROW EXCLUSIVE MODE" ) ) ;
637+ assert ! ( migrations[ 6 ]
638+ . sql
639+ . as_str( )
640+ . contains( "CREATE TABLE parameterized_event_watermarks" ) ) ;
641+ assert ! ( migrations[ 6 ]
642+ . sql
643+ . as_str( )
644+ . contains( "INSERT INTO parameterized_event_watermarks" ) ) ;
645+ assert ! ( migrations[ 6 ]
646+ . sql
647+ . as_str( )
648+ . contains( "CREATE INDEX idx_event_mentions_community_event" ) ) ;
649+ assert ! ( migrations[ 6 ]
650+ . sql
651+ . as_str( )
652+ . contains( "NIP-RS retention blocked: deleted event outranks live head" ) ) ;
653+ assert ! ( migrations[ 6 ]
654+ . sql
655+ . as_str( )
656+ . contains( "DELETE FROM events old" ) ) ;
657+ assert ! ( !migrations[ 6 ]
658+ . sql
659+ . as_str( )
660+ . contains( "ALTER TABLE events DROP COLUMN search_tsv" ) ) ;
661+
662+ // Fresh installs opt into the positive search allowlist without making
663+ // populated databases rewrite their events heap during relay startup.
664+ assert_eq ! ( migrations[ 7 ] . version, 8 ) ;
665+ assert ! ( migrations[ 7 ]
666+ . sql
667+ . as_str( )
668+ . contains( "IF NOT EXISTS (SELECT 1 FROM events LIMIT 1)" ) ) ;
669+ assert ! ( migrations[ 7 ]
670+ . sql
671+ . as_str( )
672+ . contains( "CASE WHEN kind IN (0, 9, 40002, 45001, 45003)" ) ) ;
673+ assert ! ( migrations[ 7 ] . sql. as_str( ) . contains( "ELSE NULL::tsvector" ) ) ;
674+
675+ // Mixed-version guards are additive because 0007/0008 may already be
676+ // recorded by a running relay and their sqlx checksums are immutable.
677+ assert_eq ! ( migrations[ 8 ] . version, 9 ) ;
678+ assert ! ( migrations[ 8 ]
679+ . sql
680+ . as_str( )
681+ . contains( "CREATE TRIGGER trg_events_nip_rs_watermark" ) ) ;
682+ assert ! ( migrations[ 8 ]
683+ . sql
684+ . as_str( )
685+ . contains( "stale NIP-RS event rejected by durable watermark" ) ) ;
686+ assert ! ( migrations[ 8 ]
687+ . sql
688+ . as_str( )
689+ . contains( "CREATE TRIGGER trg_events_purge_soft_deleted_nip_rs" ) ) ;
690+ assert ! ( migrations[ 8 ]
691+ . sql
692+ . as_str( )
693+ . contains( "CREATE TRIGGER trg_event_mentions_require_live_event" ) ) ;
694+
695+ assert_eq ! ( migrations[ 9 ] . version, 10 ) ;
696+ assert ! ( migrations[ 9 ]
697+ . sql
698+ . as_str( )
699+ . contains( "CREATE OR REPLACE FUNCTION guard_nip_rs_watermark" ) ) ;
700+ assert ! ( migrations[ 9 ] . sql. as_str( ) . contains( "RETURN NULL" ) ) ;
701+
702+ assert_eq ! ( migrations[ 10 ] . version, 11 ) ;
703+ assert ! ( migrations[ 10 ]
704+ . sql
705+ . as_str( )
706+ . contains( "CREATE OR REPLACE FUNCTION guard_nip_rs_watermark" ) ) ;
707+ assert ! ( migrations[ 10 ]
708+ . sql
709+ . as_str( )
710+ . contains( "CREATE OR REPLACE FUNCTION purge_soft_deleted_nip_rs" ) ) ;
711+ assert ! ( migrations[ 10 ] . sql. as_str( ) . contains( "tag->>0 = 'd'" ) ) ;
712+ assert ! ( migrations[ 10 ] . sql. as_str( ) . contains( ") = 1" ) ) ;
558713 }
559714
560715 #[ test]
@@ -730,6 +885,76 @@ mod tests {
730885 . expect ( "read applied migrations" )
731886 }
732887
888+ #[ tokio:: test]
889+ #[ ignore = "requires Postgres" ]
890+ async fn pre_0007_ambiguous_nip_rs_data_blocks_without_mutation_and_allows_retry ( ) {
891+ let pool = connect_test_pool ( ) . await ;
892+ reset_public_schema ( & pool) . await ;
893+ MIGRATOR
894+ . run_to ( 6 , & pool)
895+ . await
896+ . expect ( "apply migrations 1-6" ) ;
897+
898+ let community_id = uuid:: Uuid :: new_v4 ( ) ;
899+ sqlx:: query ( "INSERT INTO communities (id, host) VALUES ($1, $2)" )
900+ . bind ( community_id)
901+ . bind ( format ! ( "pre-0007-{}.example" , community_id. simple( ) ) )
902+ . execute ( & pool)
903+ . await
904+ . expect ( "insert community" ) ;
905+ let event_id = vec ! [ 1_u8 ; 32 ] ;
906+ let pubkey = vec ! [ 2_u8 ; 32 ] ;
907+ let d_tag = format ! ( "read-state:{}" , "a" . repeat( 32 ) ) ;
908+ let ambiguous_tags = serde_json:: json!( [ [ "d" , d_tag] , [ "d" , "other" ] , [ "t" , "read-state" ] ] ) ;
909+ sqlx:: query (
910+ "INSERT INTO events \
911+ (community_id, id, pubkey, created_at, kind, tags, content, sig, received_at, d_tag) \
912+ VALUES ($1, $2, $3, NOW(), 30078, $4, 'ambiguous', $5, NOW(), $6)",
913+ )
914+ . bind ( community_id)
915+ . bind ( & event_id)
916+ . bind ( & pubkey)
917+ . bind ( & ambiguous_tags)
918+ . bind ( vec ! [ 3_u8 ; 64 ] )
919+ . bind ( & d_tag)
920+ . execute ( & pool)
921+ . await
922+ . expect ( "insert ambiguous NIP-RS row" ) ;
923+
924+ let before_versions = applied_versions ( & pool) . await ;
925+ let before_row: ( serde_json:: Value , String ) =
926+ sqlx:: query_as ( "SELECT tags, content FROM events WHERE community_id=$1 AND id=$2" )
927+ . bind ( community_id)
928+ . bind ( & event_id)
929+ . fetch_one ( & pool)
930+ . await
931+ . expect ( "read ambiguous row before blocked migration" ) ;
932+ let blocked = run_migrations ( & pool) . await ;
933+ assert ! ( blocked. is_err( ) , "ambiguous pre-0007 data must fail closed" ) ;
934+ assert_eq ! ( applied_versions( & pool) . await , before_versions) ;
935+ let after_row: ( serde_json:: Value , String ) =
936+ sqlx:: query_as ( "SELECT tags, content FROM events WHERE community_id=$1 AND id=$2" )
937+ . bind ( community_id)
938+ . bind ( & event_id)
939+ . fetch_one ( & pool)
940+ . await
941+ . expect ( "blocked migration must preserve source row" ) ;
942+ assert_eq ! ( after_row, before_row) ;
943+
944+ let repaired_tags = serde_json:: json!( [ [ "d" , d_tag] , [ "t" , "read-state" ] ] ) ;
945+ sqlx:: query ( "UPDATE events SET tags=$1 WHERE community_id=$2 AND id=$3" )
946+ . bind ( repaired_tags)
947+ . bind ( community_id)
948+ . bind ( & event_id)
949+ . execute ( & pool)
950+ . await
951+ . expect ( "repair ambiguous row" ) ;
952+ run_migrations ( & pool)
953+ . await
954+ . expect ( "retry succeeds after operator repair" ) ;
955+ assert_eq ! ( applied_versions( & pool) . await . last( ) . copied( ) , Some ( 11 ) ) ;
956+ }
957+
733958 #[ tokio:: test]
734959 #[ ignore = "requires Postgres" ]
735960 async fn run_migrations_applies_consolidated_initial_schema_on_fresh_database ( ) {
@@ -769,5 +994,25 @@ mod tests {
769994 ) ;
770995 assert ! ( exists, "migration should create {table}" ) ;
771996 }
997+
998+ let search_expression: String = sqlx:: query_scalar (
999+ "SELECT pg_get_expr(adbin, adrelid) \
1000+ FROM pg_attrdef \
1001+ WHERE adrelid = 'events'::regclass \
1002+ AND adnum = (SELECT attnum FROM pg_attribute \
1003+ WHERE attrelid = 'events'::regclass \
1004+ AND attname = 'search_tsv')",
1005+ )
1006+ . fetch_one ( & pool)
1007+ . await
1008+ . expect ( "read fresh-install search expression" ) ;
1009+ assert ! (
1010+ search_expression. contains( "ARRAY[0, 9, 40002, 45001, 45003]" ) ,
1011+ "fresh-install search allowlist has the wrong kinds: {search_expression}"
1012+ ) ;
1013+ assert ! (
1014+ search_expression. contains( "ELSE NULL::tsvector" ) ,
1015+ "fresh installs must default non-allowlisted kinds to NULL: {search_expression}"
1016+ ) ;
7721017 }
7731018}
0 commit comments