broad-announce/internal/authd/store.go

// Package authd — store.go: Postgres-backed data access. The
// schema is in migrations/009_auth.up.sql. This file is the only
// place in the package that touches pgx directly; the rest of the
// package uses the higher-level methods on *Authd.
//
// All methods take a context and respect cancellation.
package authd

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"

	"git3.techno-world.net/lrosales/broad-announce/internal/postgres"
)

// Store wraps a pgx pool with auth-specific queries. Construct via
// NewStore; do not instantiate directly.
type Store struct {
	pool *postgres.Pool
}

// NewStore constructs a Store.
func NewStore(pool *postgres.Pool) *Store {
	return &Store{pool: pool}
}

// ---------------------------------------------------------------------------
// User types
// ---------------------------------------------------------------------------

// User is the row from auth.users, plus a couple of join fields
// flattened for convenience.
type User struct {
	ID            string
	GlobalID      string
	TenantID      string
	Email         string
	Role          string
	Status        string
	DisplayName   string
	PasswordHash  string
	LastLoginAt   *time.Time
	CreatedAt     time.Time
	UpdatedAt     time.Time
}

// ---------------------------------------------------------------------------
// User CRUD
// ---------------------------------------------------------------------------

// TenantIDBySlug resolves a tenant slug to its id. Returns
// ErrUserNotFound if the slug doesn't exist (the error is
// misnamed; v1.1 should split it).
func (s *Store) TenantIDBySlug(ctx context.Context, slug string) (string, error) {
	if s.pool == nil {
		return "", errors.New("authd: no DB pool (test mode)")
	}
	const q = `SELECT id::text FROM auth.tenants WHERE slug = $1`
	var id string
	err := s.pool.QueryRow(ctx, q, slug).Scan(&id)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return "", ErrUserNotFound
		}
		return "", fmt.Errorf("tenant id by slug: %w", err)
	}
	return id, nil
}

// GetUserByEmail looks up a user by email. Email is unique per
// tenant (or globally for super_admins), so we use the most recent
// matching row if there are multiple.
func (s *Store) GetUserByEmail(ctx context.Context, email string) (*User, error) {
	const q = `
		SELECT id::text, global_id::text, COALESCE(tenant_id::text, ''),
		       email, role, status, COALESCE(display_name, ''),
		       COALESCE(password_hash, ''), last_login_at, created_at, updated_at
		FROM auth.users
		WHERE email = $1
		ORDER BY (tenant_id IS NULL) DESC, created_at DESC
		LIMIT 1
	`
	u := &User{}
	err := s.pool.QueryRow(ctx, q, email).Scan(
		&u.ID, &u.GlobalID, &u.TenantID, &u.Email, &u.Role, &u.Status,
		&u.DisplayName, &u.PasswordHash, &u.LastLoginAt, &u.CreatedAt, &u.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrUserNotFound
		}
		return nil, fmt.Errorf("get user by email: %w", err)
	}
	return u, nil
}

// GetUserByID looks up a user by primary key.
func (s *Store) GetUserByID(ctx context.Context, id string) (*User, error) {
	const q = `
		SELECT id::text, global_id::text, COALESCE(tenant_id::text, ''),
		       email, role, status, COALESCE(display_name, ''),
		       COALESCE(password_hash, ''), last_login_at, created_at, updated_at
		FROM auth.users
		WHERE id = $1
	`
	u := &User{}
	err := s.pool.QueryRow(ctx, q, id).Scan(
		&u.ID, &u.GlobalID, &u.TenantID, &u.Email, &u.Role, &u.Status,
		&u.DisplayName, &u.PasswordHash, &u.LastLoginAt, &u.CreatedAt, &u.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return nil, ErrUserNotFound
		}
		return nil, fmt.Errorf("get user by id: %w", err)
	}
	return u, nil
}

// CreateUser inserts a new pending user. Returns the new id.
// Fails with a wrapped pg error if the email/tenant pair is not
// unique.
func (s *Store) CreateUser(ctx context.Context, tenantID, email, role string) (string, error) {
	const q = `
		INSERT INTO auth.users (tenant_id, email, role, status)
		VALUES (
			CASE WHEN $1 = '' THEN NULL ELSE $1::uuid END,
			$2, $3, 'pending'
		)
		RETURNING id::text
	`
	var id string
	err := s.pool.QueryRow(ctx, q, tenantID, email, role).Scan(&id)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return "", fmt.Errorf("user %q already exists: %w", email, err)
		}
		return "", fmt.Errorf("create user: %w", err)
	}
	return id, nil
}

// SetUserPassword updates password_hash and status. Used by both
// magic-link-first-login and password-reset paths.
func (s *Store) SetUserPassword(ctx context.Context, userID, hash, newStatus string) error {
	const q = `UPDATE auth.users SET password_hash = $2, status = $3 WHERE id = $1`
	tag, err := s.pool.Exec(ctx, q, userID, hash, newStatus)
	if err != nil {
		return fmt.Errorf("set password: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrUserNotFound
	}
	return nil
}

// TouchUserLogin updates last_login_at. Fire-and-forget from caller.
func (s *Store) TouchUserLogin(ctx context.Context, userID string) error {
	const q = `UPDATE auth.users SET last_login_at = NOW() WHERE id = $1`
	_, err := s.pool.Exec(ctx, q, userID)
	return err
}

// ---------------------------------------------------------------------------
// Magic links
// ---------------------------------------------------------------------------

// InsertMagicLink stores the hash of a magic link token. The
// plaintext is never persisted.
func (s *Store) InsertMagicLink(ctx context.Context, userID string, hash []byte, purpose string, expiresAt time.Time) error {
	if s.pool == nil {
		return errors.New("authd: no DB pool (test mode)")
	}
	const q = `
		INSERT INTO auth.magic_links (token_hash, user_id, purpose, expires_at)
		VALUES ($1, $2::uuid, $3, $4)
	`
	_, err := s.pool.Exec(ctx, q, hash, userID, purpose, expiresAt)
	if err != nil {
		return fmt.Errorf("insert magic link: %w", err)
	}
	return nil
}

// ConsumeMagicLink atomically marks a link consumed and returns the
// user_id. Returns ErrMagicLinkInvalid for unknown, expired, or
// already-consumed links.
func (s *Store) ConsumeMagicLink(ctx context.Context, hash []byte, ip, ua string) (string, error) {
	const q = `
		UPDATE auth.magic_links
		   SET consumed_at = NOW(),
		       consumed_ip = $2::inet,
		       consumed_ua = $3
		 WHERE token_hash = $1
		   AND consumed_at IS NULL
		   AND expires_at > NOW()
		RETURNING user_id::text
	`
	var userID string
	err := s.pool.QueryRow(ctx, q, hash, ip, ua).Scan(&userID)
	if err != nil {
		if errors.Is(err, pgx.ErrNoRows) {
			return "", ErrMagicLinkInvalid
		}
		return "", fmt.Errorf("consume magic link: %w", err)
	}
	return userID, nil
}

// ---------------------------------------------------------------------------
// Refresh tokens (delegate to SQL functions)
// ---------------------------------------------------------------------------

// IssueRefreshToken asks Postgres for a new token row. Returns the
// plaintext token (the caller returns it to the client once and
// discards it).
func (s *Store) IssueRefreshToken(ctx context.Context, userID, accessJTI string, ttlSeconds int, ip, ua string) (string, error) {
	const q = `SELECT token FROM auth.issue_refresh_token($1::uuid, $2, $3, $4::inet, $5)`
	var token string
	err := s.pool.QueryRow(ctx, q, userID, accessJTI, ttlSeconds, ip, ua).Scan(&token)
	if err != nil {
		return "", fmt.Errorf("issue refresh token: %w", err)
	}
	return token, nil
}

// RotateRefreshToken swaps a refresh token for a new one. On
// re-use, returns ErrTokenReuse and the whole family is killed in
// the SQL function. Returns the user, the new plaintext token, and
// the JTI to use for the new access JWT.
func (s *Store) RotateRefreshToken(ctx context.Context, presentedToken string, ttlSeconds int, ip, ua string) (*User, string, string, error) {
	// We need the JTI for the new access token. The SQL function
	// returns (id, token, family_id, expires_at, killed_family) but
	// NOT a new JTI. We generate the JTI here and pass it in.
	// Patch: the SQL function signature is fixed (p_new_access_jti
	// is the last text arg), so we generate it client-side and pass
	// it in, then read it back from the returned columns.
	newJTI := newJTI()
	const q = `
		SELECT id::text, token, family_id, expires_at, killed_family
		  FROM auth.rotate_refresh_token($1, $2, $3, $4::inet, $5)
	`
	var (
		id       string
		token    string
		familyID string
		exp      time.Time
		killed   bool
	)
	err := s.pool.QueryRow(ctx, q, presentedToken, newJTI, ttlSeconds, ip, ua).Scan(&id, &token, &familyID, &exp, &killed)
	if err != nil {
		// Distinguish re-use (22023 with refresh_token_reuse) from
		// other failures. The SQL raises different messages; we
		// match on the message.
		msg := err.Error()
		if contains(msg, "refresh_token_reuse") {
			return nil, "", "", ErrTokenReuse
		}
		if contains(msg, "expired_refresh_token") {
			return nil, "", "", errors.New("authd: refresh token expired")
		}
		if contains(msg, "unknown_refresh_token") {
			return nil, "", "", errors.New("authd: refresh token unknown")
		}
		return nil, "", "", fmt.Errorf("rotate refresh token: %w", err)
	}
	_ = killed  // logged separately
	_ = familyID // future: pass back for client-side display
	// Fetch the user (we need tenant + role for the new access JWT)
	user, err := s.getUserByRefreshID(ctx, id)
	if err != nil {
		return nil, "", "", err
	}
	return user, token, newJTI, nil
}

// RevokeRefreshToken revokes a single token by plaintext. Returns
// true if a row was actually revoked.
func (s *Store) RevokeRefreshToken(ctx context.Context, token string) error {
	const q = `SELECT auth.revoke_refresh_token($1)`
	var revoked bool
	err := s.pool.QueryRow(ctx, q, token).Scan(&revoked)
	if err != nil {
		return fmt.Errorf("revoke refresh token: %w", err)
	}
	_ = revoked
	return nil
}

// getUserByRefreshID is a join used by RotateRefreshToken.
func (s *Store) getUserByRefreshID(ctx context.Context, refreshID string) (*User, error) {
	const q = `
		SELECT u.id::text, u.global_id::text, COALESCE(u.tenant_id::text, ''),
		       u.email, u.role, u.status, COALESCE(u.display_name, ''),
		       COALESCE(u.password_hash, ''), u.last_login_at, u.created_at, u.updated_at
		FROM auth.refresh_tokens rt
		JOIN auth.users u ON u.id = rt.user_id
		WHERE rt.id = $1::uuid
	`
	u := &User{}
	err := s.pool.QueryRow(ctx, q, refreshID).Scan(
		&u.ID, &u.GlobalID, &u.TenantID, &u.Email, &u.Role, &u.Status,
		&u.DisplayName, &u.PasswordHash, &u.LastLoginAt, &u.CreatedAt, &u.UpdatedAt,
	)
	if err != nil {
		return nil, fmt.Errorf("get user by refresh id: %w", err)
	}
	return u, nil
}

// ---------------------------------------------------------------------------
// Audit log
// ---------------------------------------------------------------------------

// WriteAudit records an event. targetID and tenantID are optional
// (empty string → NULL in the column). payload may be nil.
func (s *Store) WriteAudit(
	ctx context.Context,
	action string,
	actorUserID string,
	actorIP string,
	actorUA string,
	targetID string,
	tenantID string,
	payload map[string]any,
) error {
	var payloadJSON []byte
	if payload != nil {
		var err error
		payloadJSON, err = json.Marshal(payload)
		if err != nil {
			return fmt.Errorf("marshal audit payload: %w", err)
		}
	} else {
		payloadJSON = []byte("{}")
	}
	// Empty string → NULL for actor_user_id
	var actorArg any
	if actorUserID == "" {
		actorArg = nil
	} else {
		actorArg = actorUserID
	}
	const q = `
		INSERT INTO auth.audit_log
		    (action, actor_user_id, actor_ip, actor_ua, target_id, tenant_id, payload)
		VALUES
		    ($1, $2::uuid, NULLIF($3, '')::inet, NULLIF($4, ''), NULLIF($5, '')::uuid, NULLIF($6, '')::uuid, $7::jsonb)
	`
	_, err := s.pool.Exec(ctx, q, action, actorArg, actorIP, actorUA, targetID, tenantID, payloadJSON)
	if err != nil {
		return fmt.Errorf("write audit: %w", err)
	}
	return nil
}

// ---------------------------------------------------------------------------
// helpers
// ---------------------------------------------------------------------------

func contains(s, substr string) bool {
	for i := 0; i+len(substr) <= len(s); i++ {
		if s[i:i+len(substr)] == substr {
			return true
		}
	}
	return false
}