@@ -261,6 +261,19 @@ pub struct OwnedCommunityRecord {
261261 pub host : String ,
262262 /// When the community row was created.
263263 pub created_at : DateTime < Utc > ,
264+ /// When the community was archived; absent while active.
265+ pub archived_at : Option < DateTime < Utc > > ,
266+ }
267+
268+ /// Community row returned by an owner-authorized archive operation.
269+ #[ derive( Debug , Clone , PartialEq , Eq ) ]
270+ pub struct ArchivedCommunityRecord {
271+ /// Stable server-resolved community id.
272+ pub id : CommunityId ,
273+ /// Reserved canonical host.
274+ pub host : String ,
275+ /// Durable first-archive timestamp.
276+ pub archived_at : DateTime < Utc > ,
264277}
265278
266279/// Token summary returned by [`Db::list_active_tokens`].
@@ -407,6 +420,7 @@ impl Db {
407420 SELECT id, host
408421 FROM communities
409422 WHERE lower(host) = lower($1)
423+ AND archived_at IS NULL
410424 "# ,
411425 )
412426 . bind ( normalized_host)
@@ -425,6 +439,35 @@ impl Db {
425439 . transpose ( )
426440 }
427441
442+ /// Returns whether a community id still exists in the active lifecycle state.
443+ pub async fn is_community_active ( & self , community_id : CommunityId ) -> Result < bool > {
444+ let active = sqlx:: query_scalar :: < _ , bool > (
445+ "SELECT EXISTS(SELECT 1 FROM communities WHERE id = $1 AND archived_at IS NULL)" ,
446+ )
447+ . bind ( community_id. as_uuid ( ) )
448+ . fetch_one ( & self . pool )
449+ . await ?;
450+ Ok ( active)
451+ }
452+
453+ /// Returns a community by host regardless of lifecycle state. Operator-plane only.
454+ pub async fn lookup_community_by_host_for_management (
455+ & self ,
456+ normalized_host : & str ,
457+ ) -> Result < Option < CommunityRecord > > {
458+ let row = sqlx:: query ( "SELECT id, host FROM communities WHERE lower(host) = lower($1)" )
459+ . bind ( normalized_host)
460+ . fetch_optional ( & self . pool )
461+ . await ?;
462+ row. map ( |row| {
463+ Ok ( CommunityRecord {
464+ id : CommunityId :: from_uuid ( row. try_get ( "id" ) ?) ,
465+ host : row. try_get ( "host" ) ?,
466+ } )
467+ } )
468+ . transpose ( )
469+ }
470+
428471 /// Lists communities where `owner_pubkey` currently holds the `owner` role.
429472 ///
430473 /// This is an operator-plane helper, not a tenant-scoped data-plane read:
@@ -436,7 +479,7 @@ impl Db {
436479 let owner_pubkey = owner_pubkey. to_ascii_lowercase ( ) ;
437480 let rows = sqlx:: query (
438481 r#"
439- SELECT c.id, c.host, c.created_at
482+ SELECT c.id, c.host, c.created_at, c.archived_at
440483 FROM communities c
441484 JOIN relay_members rm ON rm.community_id = c.id
442485 WHERE rm.pubkey = $1
@@ -453,10 +496,12 @@ impl Db {
453496 let id: Uuid = row. try_get ( "id" ) ?;
454497 let host: String = row. try_get ( "host" ) ?;
455498 let created_at: DateTime < Utc > = row. try_get ( "created_at" ) ?;
499+ let archived_at: Option < DateTime < Utc > > = row. try_get ( "archived_at" ) ?;
456500 Ok ( OwnedCommunityRecord {
457501 id : CommunityId :: from_uuid ( id) ,
458502 host,
459503 created_at,
504+ archived_at,
460505 } )
461506 } )
462507 . collect ( )
@@ -478,6 +523,7 @@ impl Db {
478523 SELECT host
479524 FROM communities
480525 WHERE id = $1
526+ AND archived_at IS NULL
481527 "# ,
482528 )
483529 . bind ( community_id. as_uuid ( ) )
@@ -632,6 +678,7 @@ impl Db {
632678 WHERE lower(c.host) = lower($1)
633679 AND lower(rm.pubkey) = lower($2)
634680 AND rm.role = 'owner'
681+ AND c.archived_at IS NULL
635682 "# ,
636683 )
637684 . bind ( normalized_host)
@@ -654,6 +701,39 @@ impl Db {
654701 ) )
655702 }
656703
704+ /// Idempotently archives a community when the asserted pubkey is its current owner.
705+ pub async fn archive_community_owned_by (
706+ & self ,
707+ normalized_host : & str ,
708+ owner_pubkey : & str ,
709+ protected_deployment_host : & str ,
710+ ) -> Result < Option < ArchivedCommunityRecord > > {
711+ let row = sqlx:: query (
712+ r#"UPDATE communities c
713+ SET archived_at = COALESCE(c.archived_at, now())
714+ FROM relay_members rm
715+ WHERE lower(c.host) = lower($1)
716+ AND rm.community_id = c.id
717+ AND lower(rm.pubkey) = lower($2)
718+ AND rm.role = 'owner'
719+ AND lower(c.host) <> lower($3)
720+ RETURNING c.id, c.host, c.archived_at"# ,
721+ )
722+ . bind ( normalized_host)
723+ . bind ( owner_pubkey)
724+ . bind ( protected_deployment_host)
725+ . fetch_optional ( & self . pool )
726+ . await ?;
727+ row. map ( |row| {
728+ Ok ( ArchivedCommunityRecord {
729+ id : CommunityId :: from_uuid ( row. try_get ( "id" ) ?) ,
730+ host : row. try_get ( "host" ) ?,
731+ archived_at : row. try_get ( "archived_at" ) ?,
732+ } )
733+ } )
734+ . transpose ( )
735+ }
736+
657737 /// Returns the community that owns a channel, if the channel exists.
658738 ///
659739 /// Internal relay producers use this to derive tenant context from the row
@@ -4128,8 +4208,13 @@ mod tests {
41284208 let community_a = CommunityId :: from_uuid ( make_community ( & db. pool ) . await ) ;
41294209 let community_b = CommunityId :: from_uuid ( make_community ( & db. pool ) . await ) ;
41304210 let community_c = CommunityId :: from_uuid ( make_community ( & db. pool ) . await ) ;
4131- let owner = "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" ;
4132- let other = "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb" ;
4211+ // Unique per run: `list_communities_owned_by` is keyed only by pubkey,
4212+ // so a shared fixed pubkey picks up communities leaked by sibling
4213+ // ignored tests running against the same database.
4214+ let owner = format ! ( "{}{}" , Uuid :: new_v4( ) . simple( ) , Uuid :: new_v4( ) . simple( ) ) ;
4215+ let owner = owner. as_str ( ) ;
4216+ let other = format ! ( "{}{}" , Uuid :: new_v4( ) . simple( ) , Uuid :: new_v4( ) . simple( ) ) ;
4217+ let other = other. as_str ( ) ;
41334218
41344219 db. bootstrap_owner ( community_a, owner)
41354220 . await
0 commit comments