@@ -5,6 +5,9 @@ use sqlx::{PgPool, Row};
55use uuid:: Uuid ;
66
77use crate :: error:: { DbError , Result } ;
8+ use crate :: Db ;
9+ use buzz_core:: CommunityId ;
10+ use buzz_datastore_tracing:: datastore_span;
811
912/// Create a new API token record. The caller is responsible for generating
1013/// the raw token and computing its SHA-256 hash.
@@ -324,6 +327,284 @@ pub async fn revoke_all_tokens(
324327 Ok ( result. rows_affected ( ) )
325328}
326329
330+ /// Token summary returned by [`Db::list_active_tokens`].
331+ #[ derive( Debug , Clone ) ]
332+ pub struct TokenSummary {
333+ /// Unique token identifier.
334+ pub id : Uuid ,
335+ /// Human-readable token name.
336+ pub name : String ,
337+ /// Compressed public key bytes of the token owner.
338+ pub owner_pubkey : Vec < u8 > ,
339+ /// Permission scopes granted to this token.
340+ pub scopes : Vec < String > ,
341+ /// When the token was created.
342+ pub created_at : DateTime < Utc > ,
343+ /// Optional expiry timestamp; `None` means no expiry.
344+ pub expires_at : Option < DateTime < Utc > > ,
345+ }
346+
347+ /// A full API token record.
348+ #[ derive( Debug , Clone ) ]
349+ pub struct ApiTokenRecord {
350+ /// Unique token identifier.
351+ pub id : Uuid ,
352+ /// SHA-256 hash of the raw token value.
353+ pub token_hash : Vec < u8 > ,
354+ /// Compressed public key bytes of the token owner.
355+ pub owner_pubkey : Vec < u8 > ,
356+ /// Human-readable token name.
357+ pub name : String ,
358+ /// Permission scopes granted to this token.
359+ pub scopes : Vec < String > ,
360+ /// Optional channel ID restrictions.
361+ pub channel_ids : Option < Vec < Uuid > > ,
362+ /// When the token was created.
363+ pub created_at : DateTime < Utc > ,
364+ /// Optional expiry timestamp.
365+ pub expires_at : Option < DateTime < Utc > > ,
366+ /// When the token was last used.
367+ pub last_used_at : Option < DateTime < Utc > > ,
368+ /// When the token was revoked.
369+ pub revoked_at : Option < DateTime < Utc > > ,
370+ }
371+
372+ fn parse_api_token_row ( row : sqlx:: postgres:: PgRow ) -> Result < ApiTokenRecord > {
373+ let id: Uuid = row. try_get ( "id" ) ?;
374+
375+ let scopes_json: serde_json:: Value = row. try_get ( "scopes" ) ?;
376+ let scopes: Vec < String > = serde_json:: from_value ( scopes_json)
377+ . map_err ( |e| DbError :: InvalidData ( format ! ( "scopes JSON: {e}" ) ) ) ?;
378+
379+ let channel_ids: Option < Vec < Uuid > > = {
380+ let raw: Option < serde_json:: Value > = row. try_get ( "channel_ids" ) ?;
381+ match raw {
382+ None => None ,
383+ Some ( v) => {
384+ let strings: Vec < String > = serde_json:: from_value ( v)
385+ . map_err ( |e| DbError :: InvalidData ( format ! ( "channel_ids JSON: {e}" ) ) ) ?;
386+ let uuids: std:: result:: Result < Vec < Uuid > , _ > =
387+ strings. iter ( ) . map ( |s| s. parse :: < Uuid > ( ) ) . collect ( ) ;
388+ Some ( uuids. map_err ( |e| DbError :: InvalidData ( format ! ( "channel_ids UUID: {e}" ) ) ) ?)
389+ }
390+ }
391+ } ;
392+
393+ Ok ( ApiTokenRecord {
394+ id,
395+ token_hash : row. try_get ( "token_hash" ) ?,
396+ owner_pubkey : row. try_get ( "owner_pubkey" ) ?,
397+ name : row. try_get ( "name" ) ?,
398+ scopes,
399+ channel_ids,
400+ created_at : row. try_get ( "created_at" ) ?,
401+ expires_at : row. try_get ( "expires_at" ) ?,
402+ last_used_at : row. try_get ( "last_used_at" ) ?,
403+ revoked_at : row. try_get ( "revoked_at" ) ?,
404+ } )
405+ }
406+
407+ impl Db {
408+ /// Create a new API token record.
409+ #[ allow( clippy:: too_many_arguments) ]
410+ #[ datastore_span( name = "create_api_token" , system = "postgresql" ) ]
411+ pub async fn create_api_token (
412+ & self ,
413+ community_id : CommunityId ,
414+ token_hash : & [ u8 ] ,
415+ owner_pubkey : & [ u8 ] ,
416+ name : & str ,
417+ scopes : & [ String ] ,
418+ channel_ids : Option < & [ Uuid ] > ,
419+ expires_at : Option < DateTime < Utc > > ,
420+ ) -> Result < Uuid > {
421+ create_api_token (
422+ & self . pool ,
423+ * community_id. as_uuid ( ) ,
424+ token_hash,
425+ owner_pubkey,
426+ name,
427+ scopes,
428+ channel_ids,
429+ expires_at,
430+ )
431+ . await
432+ }
433+
434+ /// Atomic conditional INSERT with 10-token limit (per (community, owner)).
435+ #[ allow( clippy:: too_many_arguments) ]
436+ #[ datastore_span( name = "create_api_token_if_under_limit" , system = "postgresql" ) ]
437+ pub async fn create_api_token_if_under_limit (
438+ & self ,
439+ community_id : CommunityId ,
440+ token_hash : & [ u8 ] ,
441+ owner_pubkey : & [ u8 ] ,
442+ name : & str ,
443+ scopes : & [ String ] ,
444+ channel_ids : Option < & [ Uuid ] > ,
445+ expires_at : Option < DateTime < Utc > > ,
446+ ) -> Result < Option < Uuid > > {
447+ create_api_token_if_under_limit (
448+ & self . pool ,
449+ * community_id. as_uuid ( ) ,
450+ token_hash,
451+ owner_pubkey,
452+ name,
453+ scopes,
454+ channel_ids,
455+ expires_at,
456+ )
457+ . await
458+ }
459+
460+ /// Look up an active (non-revoked) API token by its SHA-256 hash,
461+ /// scoped to the request's community.
462+ ///
463+ /// See [`get_api_token_by_hash_including_revoked`] for the
464+ /// row-44 conformance rationale — the `(community_id, token_hash)` key
465+ /// is enforced both by the storage UNIQUE index and by this WHERE clause.
466+ #[ datastore_span( name = "get_api_token_by_hash" , system = "postgresql" ) ]
467+ pub async fn get_api_token_by_hash (
468+ & self ,
469+ community_id : CommunityId ,
470+ hash : & [ u8 ] ,
471+ ) -> Result < Option < ApiTokenRecord > > {
472+ let row = sqlx:: query (
473+ r#"
474+ SELECT id, token_hash, owner_pubkey, name, scopes, channel_ids,
475+ created_at, expires_at, last_used_at, revoked_at
476+ FROM api_tokens
477+ WHERE community_id = $1 AND token_hash = $2 AND revoked_at IS NULL
478+ "# ,
479+ )
480+ . bind ( community_id. as_uuid ( ) )
481+ . bind ( hash)
482+ . fetch_optional ( & self . pool )
483+ . await ?;
484+
485+ match row {
486+ None => Ok ( None ) ,
487+ Some ( r) => parse_api_token_row ( r) . map ( Some ) ,
488+ }
489+ }
490+
491+ /// Look up an API token by hash, including revoked, scoped to community.
492+ #[ datastore_span(
493+ name = "get_api_token_by_hash_including_revoked" ,
494+ system = "postgresql"
495+ ) ]
496+ pub async fn get_api_token_by_hash_including_revoked (
497+ & self ,
498+ community_id : CommunityId ,
499+ hash : & [ u8 ] ,
500+ ) -> Result < Option < ApiTokenRecord > > {
501+ get_api_token_by_hash_including_revoked ( & self . pool , * community_id. as_uuid ( ) , hash) . await
502+ }
503+
504+ /// Record a token usage (update `last_used_at`), scoped to community.
505+ #[ datastore_span( name = "touch_api_token" , system = "postgresql" ) ]
506+ pub async fn touch_api_token ( & self , community_id : CommunityId , hash : & [ u8 ] ) -> Result < ( ) > {
507+ sqlx:: query (
508+ "UPDATE api_tokens SET last_used_at = NOW() WHERE community_id = $1 AND token_hash = $2" ,
509+ )
510+ . bind ( community_id. as_uuid ( ) )
511+ . bind ( hash)
512+ . execute ( & self . pool )
513+ . await ?;
514+ Ok ( ( ) )
515+ }
516+
517+ /// Alias for [`Self::touch_api_token`].
518+ pub async fn update_token_last_used (
519+ & self ,
520+ community_id : CommunityId ,
521+ hash : & [ u8 ] ,
522+ ) -> Result < ( ) > {
523+ self . touch_api_token ( community_id, hash) . await
524+ }
525+
526+ /// List all active (non-revoked) tokens in a community, newest first.
527+ #[ datastore_span( name = "list_active_tokens" , system = "postgresql" ) ]
528+ pub async fn list_active_tokens ( & self , community_id : CommunityId ) -> Result < Vec < TokenSummary > > {
529+ let rows = sqlx:: query (
530+ r#"
531+ SELECT id, name, owner_pubkey, scopes, created_at, expires_at
532+ FROM api_tokens
533+ WHERE community_id = $1 AND revoked_at IS NULL
534+ ORDER BY created_at DESC
535+ LIMIT 1000
536+ "# ,
537+ )
538+ . bind ( community_id. as_uuid ( ) )
539+ . fetch_all ( & self . pool )
540+ . await ?;
541+
542+ let mut out = Vec :: with_capacity ( rows. len ( ) ) ;
543+ for row in rows {
544+ let id: Uuid = row. try_get ( "id" ) ?;
545+ let scopes_json: serde_json:: Value = row. try_get ( "scopes" ) ?;
546+ let scopes: Vec < String > = serde_json:: from_value ( scopes_json)
547+ . map_err ( |e| DbError :: InvalidData ( format ! ( "scopes JSON: {e}" ) ) ) ?;
548+
549+ out. push ( TokenSummary {
550+ id,
551+ name : row. try_get ( "name" ) ?,
552+ owner_pubkey : row. try_get ( "owner_pubkey" ) ?,
553+ scopes,
554+ created_at : row. try_get ( "created_at" ) ?,
555+ expires_at : row. try_get ( "expires_at" ) ?,
556+ } ) ;
557+ }
558+ Ok ( out)
559+ }
560+
561+ /// List all tokens for a (community, owner) pair (including revoked).
562+ #[ datastore_span( name = "list_tokens_by_owner" , system = "postgresql" ) ]
563+ pub async fn list_tokens_by_owner (
564+ & self ,
565+ community_id : CommunityId ,
566+ pubkey : & [ u8 ] ,
567+ ) -> Result < Vec < ApiTokenRecord > > {
568+ list_tokens_by_owner ( & self . pool , * community_id. as_uuid ( ) , pubkey) . await
569+ }
570+
571+ /// Revoke a single token by ID, scoped to (community, owner).
572+ #[ datastore_span( name = "revoke_token" , system = "postgresql" ) ]
573+ pub async fn revoke_token (
574+ & self ,
575+ community_id : CommunityId ,
576+ id : Uuid ,
577+ owner_pubkey : & [ u8 ] ,
578+ revoked_by : & [ u8 ] ,
579+ ) -> Result < bool > {
580+ revoke_token (
581+ & self . pool ,
582+ * community_id. as_uuid ( ) ,
583+ id,
584+ owner_pubkey,
585+ revoked_by,
586+ )
587+ . await
588+ }
589+
590+ /// Revoke all active tokens for a (community, owner) pair.
591+ #[ datastore_span( name = "revoke_all_tokens" , system = "postgresql" ) ]
592+ pub async fn revoke_all_tokens (
593+ & self ,
594+ community_id : CommunityId ,
595+ owner_pubkey : & [ u8 ] ,
596+ revoked_by : & [ u8 ] ,
597+ ) -> Result < u64 > {
598+ revoke_all_tokens (
599+ & self . pool ,
600+ * community_id. as_uuid ( ) ,
601+ owner_pubkey,
602+ revoked_by,
603+ )
604+ . await
605+ }
606+ }
607+
327608#[ cfg( test) ]
328609mod tests {
329610 //! Row-44 conformance: API token lookups MUST be keyed on
@@ -344,7 +625,7 @@ mod tests {
344625 use crate :: { ApiTokenRecord , Db } ;
345626 use sqlx:: PgPool ;
346627
347- const TEST_DB_URL : & str = "postgres://buzz:buzz_dev@localhost:5432/buzz" ;
628+ const TEST_DB_URL : & str = "postgres://buzz:buzz_dev@localhost:5432/buzz" ; // sadscan:disable np.postgres.1 -- local test-only credentials
348629
349630 async fn setup_db ( ) -> Db {
350631 let pool = PgPool :: connect ( TEST_DB_URL )
0 commit comments