Skip to content

Commit 77729ab

Browse files
wpfleger96Hayt
andauthored
feat(relay): add admin HTTP routes for member restriction management (#7302)
## What Three admin HTTP routes under `GET|DELETE /api/admin/v1/members/...`: - `GET /members/restrictions?communityId=<uuid>[&limit={1-200}][&cursor=<token>]` — lists active bans and timeouts with stable keyset pagination - `DELETE /members/{pubkey}/ban?communityId=<uuid>` — lifts an active ban; 409 if no active restriction - `DELETE /members/{pubkey}/timeout?communityId=<uuid>` — lifts an active timeout; 409 if no active restriction ## Auth All three routes pass through the existing NIP-98 authorization gate. Both DELETEs call `require_mutation_principal`; GET follows the established read-authorization pattern. ## Atomicity Both DELETE routes use transactional DB helpers (`unban_member_with_audit` / `untimeout_member_with_audit`) that perform the conditional lift UPDATE and the `moderation_actions` INSERT in one SQL transaction. If the audit INSERT fails the lift rolls back; the restriction stays active. ## Expired-ban predicate `unban_member` WHERE clause requires `banned AND (ban_expires_at IS NULL OR ban_expires_at > now())` — matches all read paths. An already-expired ban returns `false` → 409. ## Pagination (GET /members/restrictions) `GET /members/restrictions` returns `{ "items": [...], "nextCursor": "<token>"|null }`. - `limit` — page size 1–200 (default 200), enforced as a SQL `LIMIT`. Returns 400 for values outside range. - `cursor` — opaque continuation token from a prior page's `nextCursor`. Omit for the first page. - Order: `ORDER BY updated_at DESC, pubkey DESC`. The `pubkey` tie-breaker ensures a deterministic cursor even when multiple rows share the same `updated_at`. - Cursor encoding: base64url of `{updated_at_micros}_{pubkey_hex}`. Treat as opaque. - `nextCursor` is `null` only when the returned page is smaller than `limit`. An exactly-full final page emits a non-null cursor; the subsequent request returns an empty page with `null`. - The existing `list_community_restrictions` DB method (unbounded) is kept intact for the embedded dashboard bridge consumer (`GET /moderation/restricted`), whose bare-array response contract must not change. ## Tests All Postgres-gated tests are in the `postgres_tests` module (`api/admin/mod.rs`): - `list_restrictions_returns_active_bans_and_timeouts` — seeds a ban + timeout, GET returns both; `nextCursor` is null when all records fit in one page - `list_restrictions_pagination_exhaustive` — seeds 201 rows: 199 with distinct timestamps (1s apart, newest first) plus a tied pair sharing an identical older timestamp (`now()-1000s`), pinned to sort positions 200/201 so the default-200 page boundary splits the tie. Asserts: (1) `limit=201` → 400; (2) a no-`limit` request returns exactly 200 items + non-null `nextCursor` (falsifiable binding of default=200 and SQL cap — reverting the route default to the shared helper's 50 fails this assertion); (3) continuation walk to exhaustion with exactly-once coverage over all 201 pubkeys, exercising the tie-breaker at the page boundary. - `unban_member_returns_204_clears_ban_and_inserts_audit` — 204, ban cleared, timeout survives (restriction independence), audit row verified, tenant isolation confirmed - `untimeout_member_returns_204_clears_timeout_and_inserts_audit` — 204, timeout cleared, ban survives (restriction independence), second-community timeout survives (community_id predicate), audit row verified - `unban_member_returns_409_for_expired_ban` — expired ban → 409, no audit row - `unban_with_audit_rolls_back_lift_when_audit_insert_fails` (buzz-db) — forced CHECK constraint failure proves Err + ban still active (rollback proof) --------- Signed-off-by: Will Pfleger <[email protected]> Signed-off-by: Hayt <9e1c23a3fd83f61da34420e4e88ff1b16e45cafcc0cd9019eb07d4ecfa8ca9b0@buzz.block.builderlab.xyz> Co-authored-by: Hayt <9e1c23a3fd83f61da34420e4e88ff1b16e45cafcc0cd9019eb07d4ecfa8ca9b0@buzz.block.builderlab.xyz>
1 parent ce41be9 commit 77729ab

2 files changed

Lines changed: 1376 additions & 2 deletions

File tree

‎crates/buzz-db/src/store/moderation.rs‎

Lines changed: 249 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -376,7 +376,8 @@ pub async fn unban_member(
376376
UPDATE community_bans
377377
SET banned = false, ban_expires_at = NULL, ban_reason = NULL,
378378
actor_pubkey = $3, updated_at = now()
379-
WHERE community_id = $1 AND pubkey = $2 AND banned = true
379+
WHERE community_id = $1 AND pubkey = $2
380+
AND (banned AND (ban_expires_at IS NULL OR ban_expires_at > now()))
380381
"#,
381382
)
382383
.bind(community.as_uuid())
@@ -388,6 +389,112 @@ pub async fn unban_member(
388389
Ok(result.rows_affected() > 0)
389390
}
390391

392+
/// Lift an active ban and insert the audit row in a single transaction.
393+
///
394+
/// Returns `true` when the ban was active and both the lift and the audit-insert
395+
/// committed. Returns `false` (without inserting an audit row) when no active
396+
/// unexpired ban exists for the member, leaving the caller free to return 409.
397+
///
398+
/// The predicate matches the definition used by all read paths:
399+
/// `banned AND (ban_expires_at IS NULL OR ban_expires_at > now())`.
400+
pub async fn unban_member_with_audit(
401+
pool: &PgPool,
402+
community: CommunityId,
403+
pubkey: &[u8],
404+
actor: &[u8],
405+
actor_authority: &str,
406+
) -> Result<bool> {
407+
let mut tx = pool.begin().await?;
408+
409+
let result = sqlx::query(
410+
r#"
411+
UPDATE community_bans
412+
SET banned = false, ban_expires_at = NULL, ban_reason = NULL,
413+
actor_pubkey = $3, updated_at = now()
414+
WHERE community_id = $1 AND pubkey = $2
415+
AND (banned AND (ban_expires_at IS NULL OR ban_expires_at > now()))
416+
"#,
417+
)
418+
.bind(community.as_uuid())
419+
.bind(pubkey)
420+
.bind(actor)
421+
.execute(&mut *tx)
422+
.await?;
423+
424+
if result.rows_affected() == 0 {
425+
// No active ban — roll back (no-op) and signal 409 to the caller.
426+
tx.rollback().await?;
427+
return Ok(false);
428+
}
429+
430+
sqlx::query(
431+
r#"
432+
INSERT INTO moderation_actions (
433+
community_id, actor_pubkey, action, target_pubkey, actor_authority
434+
) VALUES ($1, $2, 'unban', $3, $4)
435+
"#,
436+
)
437+
.bind(community.as_uuid())
438+
.bind(actor)
439+
.bind(pubkey)
440+
.bind(actor_authority)
441+
.execute(&mut *tx)
442+
.await?;
443+
444+
tx.commit().await?;
445+
Ok(true)
446+
}
447+
448+
/// Lift an active timeout and insert the audit row in a single transaction.
449+
///
450+
/// Returns `true` when the timeout was active and both the lift and the
451+
/// audit-insert committed. Returns `false` when no active timeout exists.
452+
pub async fn untimeout_member_with_audit(
453+
pool: &PgPool,
454+
community: CommunityId,
455+
pubkey: &[u8],
456+
actor: &[u8],
457+
actor_authority: &str,
458+
) -> Result<bool> {
459+
let mut tx = pool.begin().await?;
460+
461+
let result = sqlx::query(
462+
r#"
463+
UPDATE community_bans
464+
SET muted_until = NULL, mute_reason = NULL,
465+
actor_pubkey = $3, updated_at = now()
466+
WHERE community_id = $1 AND pubkey = $2 AND muted_until > now()
467+
"#,
468+
)
469+
.bind(community.as_uuid())
470+
.bind(pubkey)
471+
.bind(actor)
472+
.execute(&mut *tx)
473+
.await?;
474+
475+
if result.rows_affected() == 0 {
476+
tx.rollback().await?;
477+
return Ok(false);
478+
}
479+
480+
sqlx::query(
481+
r#"
482+
INSERT INTO moderation_actions (
483+
community_id, actor_pubkey, action, target_pubkey, actor_authority
484+
) VALUES ($1, $2, 'untimeout', $3, $4)
485+
"#,
486+
)
487+
.bind(community.as_uuid())
488+
.bind(actor)
489+
.bind(pubkey)
490+
.bind(actor_authority)
491+
.execute(&mut *tx)
492+
.await?;
493+
494+
tx.commit().await?;
495+
Ok(true)
496+
}
497+
391498
/// Upsert a timeout: sets `muted_until` + reason.
392499
pub async fn timeout_member(
393500
pool: &PgPool,
@@ -540,6 +647,49 @@ pub async fn list_restricted(pool: &PgPool, community: CommunityId) -> Result<Ve
540647
rows.into_iter().map(row_to_ban).collect()
541648
}
542649

650+
/// List currently-restricted members with stable keyset pagination.
651+
///
652+
/// Returns at most `limit` rows. Pass the `updated_at` + `pubkey` of the last
653+
/// returned row as `cursor` to fetch the next page. The tie-breaker on `pubkey`
654+
/// (`BYTEA` comparison) makes the cursor deterministic even when multiple rows
655+
/// share an identical `updated_at`.
656+
pub async fn list_restricted_page(
657+
pool: &PgPool,
658+
community: CommunityId,
659+
limit: i64,
660+
cursor: Option<(DateTime<Utc>, Vec<u8>)>,
661+
) -> Result<Vec<BanRecord>> {
662+
let (cursor_ts, cursor_pk) = cursor.unzip();
663+
let rows = sqlx::query(
664+
r#"
665+
SELECT pubkey,
666+
(banned AND (ban_expires_at IS NULL OR ban_expires_at > now())) AS banned,
667+
ban_expires_at, ban_reason, muted_until,
668+
mute_reason, actor_pubkey, updated_at
669+
FROM community_bans
670+
WHERE community_id = $1
671+
AND (
672+
(banned AND (ban_expires_at IS NULL OR ban_expires_at > now()))
673+
OR muted_until > now()
674+
)
675+
AND (
676+
$2::timestamptz IS NULL
677+
OR (updated_at, pubkey) < ($2, $3::bytea)
678+
)
679+
ORDER BY updated_at DESC, pubkey DESC
680+
LIMIT $4
681+
"#,
682+
)
683+
.bind(community.as_uuid())
684+
.bind(cursor_ts)
685+
.bind(cursor_pk)
686+
.bind(limit)
687+
.fetch_all(pool)
688+
.await?;
689+
690+
rows.into_iter().map(row_to_ban).collect()
691+
}
692+
543693
/// Insert a moderation audit row, returning its id.
544694
pub async fn insert_action(
545695
pool: &PgPool,
@@ -744,6 +894,37 @@ impl Db {
744894
unban_member(&self.pool, community, pubkey, actor).await
745895
}
746896

897+
/// Lift an active ban and insert the audit row atomically.
898+
///
899+
/// Returns `false` when no active unexpired ban exists (409 signal); the
900+
/// audit row is only inserted when the lift commits. Expired bans
901+
/// (`banned AND ban_expires_at <= now()`) return `false` — read paths
902+
/// already treat them as inactive.
903+
#[datastore_span(name = "unban_community_member_with_audit", system = "postgresql")]
904+
pub async fn unban_community_member_with_audit(
905+
&self,
906+
community: CommunityId,
907+
pubkey: &[u8],
908+
actor: &[u8],
909+
actor_authority: &str,
910+
) -> Result<bool> {
911+
unban_member_with_audit(&self.pool, community, pubkey, actor, actor_authority).await
912+
}
913+
914+
/// Lift an active timeout and insert the audit row atomically.
915+
///
916+
/// Returns `false` when no active timeout exists (409 signal).
917+
#[datastore_span(name = "untimeout_community_member_with_audit", system = "postgresql")]
918+
pub async fn untimeout_community_member_with_audit(
919+
&self,
920+
community: CommunityId,
921+
pubkey: &[u8],
922+
actor: &[u8],
923+
actor_authority: &str,
924+
) -> Result<bool> {
925+
untimeout_member_with_audit(&self.pool, community, pubkey, actor, actor_authority).await
926+
}
927+
747928
/// Upsert a community timeout/write-block for a member pubkey.
748929
#[datastore_span(name = "timeout_community_member", system = "postgresql")]
749930
pub async fn timeout_community_member(
@@ -797,6 +978,20 @@ impl Db {
797978
list_restricted(&self.pool, community).await
798979
}
799980

981+
/// List currently restricted members with stable keyset pagination.
982+
///
983+
/// Returns at most `limit` rows. Supply the `(updated_at, pubkey)` of the
984+
/// last row as `cursor` to advance to the next page.
985+
#[datastore_span(name = "list_community_restrictions_page", system = "postgresql")]
986+
pub async fn list_community_restrictions_page(
987+
&self,
988+
community: CommunityId,
989+
limit: i64,
990+
cursor: Option<(DateTime<Utc>, Vec<u8>)>,
991+
) -> Result<Vec<BanRecord>> {
992+
list_restricted_page(&self.pool, community, limit, cursor).await
993+
}
994+
800995
/// Insert a moderation audit action row.
801996
#[datastore_span(name = "insert_moderation_action", system = "postgresql")]
802997
pub async fn insert_moderation_action(
@@ -1141,6 +1336,59 @@ mod postgres_tests {
11411336
);
11421337
}
11431338

1339+
/// Atomicity guard: `unban_member_with_audit` must roll back the ban lift
1340+
/// when the audit INSERT fails, leaving the restriction active.
1341+
///
1342+
/// This exercises the transactional boundary: the UPDATE and the INSERT share
1343+
/// one SQL transaction; a CHECK violation on the INSERT must roll back both.
1344+
/// Passing `actor_authority = "invalid"` triggers the DB CHECK constraint
1345+
/// on `moderation_actions.actor_authority`.
1346+
#[tokio::test]
1347+
#[ignore = "requires Postgres"]
1348+
async fn unban_with_audit_rolls_back_lift_when_audit_insert_fails() {
1349+
let pool = setup_pool().await;
1350+
let community = make_test_community(&pool).await;
1351+
let pubkey = random_32();
1352+
let actor = random_32();
1353+
1354+
ban_member(&pool, community, &pubkey, &actor, None, None)
1355+
.await
1356+
.expect("insert ban fixture");
1357+
1358+
// Verify the ban is active before we attempt the lift.
1359+
let state_before = restriction_state(&pool, community, &pubkey)
1360+
.await
1361+
.expect("restriction_state before");
1362+
assert!(state_before.banned, "pre-condition: pubkey must be banned");
1363+
1364+
// Use an invalid actor_authority that violates the DB CHECK constraint —
1365+
// this causes the audit INSERT to fail, which must roll back the UPDATE.
1366+
let result =
1367+
unban_member_with_audit(&pool, community, &pubkey, &actor, "invalid_authority").await;
1368+
assert!(
1369+
result.is_err(),
1370+
"unban_with_audit must return Err when audit INSERT violates a constraint"
1371+
);
1372+
1373+
// The ban must still be active — the rolled-back UPDATE must not have committed.
1374+
let state_after = restriction_state(&pool, community, &pubkey)
1375+
.await
1376+
.expect("restriction_state after");
1377+
assert!(
1378+
state_after.banned,
1379+
"ban must remain active after a failed unban_with_audit (rollback)"
1380+
);
1381+
1382+
// No audit row must have been inserted.
1383+
let actions = list_actions(&pool, community, 10)
1384+
.await
1385+
.expect("list actions");
1386+
assert!(
1387+
actions.is_empty(),
1388+
"no audit row must be committed when the transaction rolls back"
1389+
);
1390+
}
1391+
11441392
/// Every non-`illegal` category still lands `open` for community triage; the
11451393
/// auto-escalation branch must not widen to the ordinary report flow.
11461394
#[tokio::test]

0 commit comments

Comments
 (0)