Skip to content

Commit 1b47030

Browse files
tlongwell-blocknpub12gtutshhh76rx0jx697f32f9tffd4hhp3hx58fp4x6u4uemkm7sqf8f757
andauthored
Bound NIP-RS retention and search indexing (#1771)
Signed-off-by: npub12gtutshhh76rx0jx697f32f9tffd4hhp3hx58fp4x6u4uemkm7sqf8f757 <5217c5c2f7bfb4333e46d17c98a9255a52dadee18dcd43a43536b95e6776dfa0@sprout-oss.stage.blox.sqprod.co> Co-authored-by: npub12gtutshhh76rx0jx697f32f9tffd4hhp3hx58fp4x6u4uemkm7sqf8f757 <5217c5c2f7bfb4333e46d17c98a9255a52dadee18dcd43a43536b95e6776dfa0@sprout-oss.stage.blox.sqprod.co>
1 parent d75c2e9 commit 1b47030

9 files changed

Lines changed: 1560 additions & 44 deletions

‎crates/buzz-db/src/lib.rs‎

Lines changed: 749 additions & 26 deletions
Large diffs are not rendered by default.

‎crates/buzz-db/src/migration.rs‎

Lines changed: 246 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,10 +12,81 @@ static MIGRATOR: sqlx::migrate::Migrator = sqlx::migrate!("../../migrations");
1212

1313
/// Run all pending Buzz database migrations.
1414
pub 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)]
2091
mod 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

Comments
 (0)