pages/internal/store/keys.go

package store

import (
	"context"
	"database/sql"
	"time"
)

// Scope is what an API key is allowed to touch.
type Scope string

const (
	// ScopeAdmin may manage every project, key and deployment.
	ScopeAdmin Scope = "admin"
	// ScopeProject may manage only the deployments of its own project.
	ScopeProject Scope = "project"
)

// APIKey is a row of the api_keys table. The secret itself is never stored —
// only sha256 of it — and is shown to the operator exactly once, at creation.
type APIKey struct {
	ID         string // public half of the token; safe to display and log
	SecretHash []byte // sha256(secret), 32 bytes
	Scope      Scope
	ProjectID  *int64 // nil for admin keys
	Name       string

	CreatedAt  time.Time
	ExpiresAt  *time.Time
	LastUsedAt *time.Time
	RevokedAt  *time.Time
}

// Usable reports whether the key may authenticate a request at time t.
func (k *APIKey) Usable(t time.Time) bool {
	if k.RevokedAt != nil {
		return false
	}
	if k.ExpiresAt != nil && !t.Before(*k.ExpiresAt) {
		return false
	}
	return true
}

const keyColumns = `id, secret_hash, scope, project_id, name, created_at, expires_at, last_used_at, revoked_at`

func scanKey(row rowScanner) (*APIKey, error) {
	var k APIKey
	var projectID, expires, lastUsed, revoked sql.NullInt64
	var created int64
	if err := row.Scan(&k.ID, &k.SecretHash, &k.Scope, &projectID, &k.Name,
		&created, &expires, &lastUsed, &revoked); err != nil {
		return nil, mapErr(err)
	}
	k.ProjectID = intPtr(projectID)
	k.CreatedAt = time.Unix(created, 0).UTC()
	k.ExpiresAt = timePtr(expires)
	k.LastUsedAt = timePtr(lastUsed)
	k.RevokedAt = timePtr(revoked)
	return &k, nil
}

// CreateKey stores a freshly minted key.
func (d *DB) CreateKey(ctx context.Context, k *APIKey) error {
	if k.CreatedAt.IsZero() {
		k.CreatedAt = time.Unix(unixNow(), 0).UTC()
	}
	return d.Tx(ctx, func(tx *sql.Tx) error {
		_, err := tx.ExecContext(ctx, `
			INSERT INTO api_keys (id, secret_hash, scope, project_id, name, created_at, expires_at)
			VALUES (?,?,?,?,?,?,?)`,
			k.ID, k.SecretHash, string(k.Scope), nullInt(k.ProjectID), k.Name,
			k.CreatedAt.Unix(), nullTime(k.ExpiresAt))
		return mapErr(err)
	})
}

// KeyByID is the authentication lookup: a primary-key hit on a WITHOUT ROWID
// table, never a scan.
func (d *DB) KeyByID(ctx context.Context, id string) (*APIKey, error) {
	return scanKey(d.r.QueryRowContext(ctx, `SELECT `+keyColumns+` FROM api_keys WHERE id = ?`, id))
}

// ListKeys returns the keys for one project, or every key when projectID is nil.
// Revoked keys are included so an operator can see what was revoked and when.
func (d *DB) ListKeys(ctx context.Context, projectID *int64) ([]*APIKey, error) {
	query := `SELECT ` + keyColumns + ` FROM api_keys`
	var args []any
	if projectID != nil {
		query += ` WHERE project_id = ?`
		args = append(args, *projectID)
	}
	query += ` ORDER BY created_at DESC, id`

	rows, err := d.r.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*APIKey
	for rows.Next() {
		k, err := scanKey(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, k)
	}
	return out, rows.Err()
}

// RevokeKey marks a key unusable. It is idempotent: revoking twice keeps the
// first timestamp, because that is when the key actually stopped working.
//
// The caller must invalidate the auth cache afterwards, or the key stays live
// for up to the cache TTL.
func (d *DB) RevokeKey(ctx context.Context, id string) error {
	return d.Tx(ctx, func(tx *sql.Tx) error {
		res, err := tx.ExecContext(ctx,
			`UPDATE api_keys SET revoked_at = ? WHERE id = ? AND revoked_at IS NULL`,
			unixNow(), id)
		if err != nil {
			return mapErr(err)
		}
		n, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if n == 0 {
			// Either it does not exist or it was already revoked; distinguish, so
			// the API can answer 404 versus 204.
			var exists int
			if err := tx.QueryRowContext(ctx, `SELECT count(*) FROM api_keys WHERE id = ?`, id).Scan(&exists); err != nil {
				return err
			}
			if exists == 0 {
				return ErrNotFound
			}
		}
		return nil
	})
}

// CountUsableAdminKeys counts admin keys that could authenticate right now. A
// zero result on startup is what triggers minting the bootstrap key.
func (d *DB) CountUsableAdminKeys(ctx context.Context) (int, error) {
	var n int
	err := d.r.QueryRowContext(ctx, `
		SELECT count(*) FROM api_keys
		WHERE scope = 'admin' AND revoked_at IS NULL AND (expires_at IS NULL OR expires_at > ?)`,
		unixNow()).Scan(&n)
	return n, err
}

// TouchKeys records last-use times in one transaction.
//
// This is deliberately a batch: updating last_used_at on every request would
// funnel every authenticated read through the single write connection, which is
// exactly the contention the two-pool design exists to avoid. The auth layer
// accumulates the timestamps in memory and flushes them periodically, so the
// column is approximate by design — it answers "is this key still in use?", not
// "when exactly was request N".
func (d *DB) TouchKeys(ctx context.Context, seen map[string]time.Time) error {
	if len(seen) == 0 {
		return nil
	}
	return d.Tx(ctx, func(tx *sql.Tx) error {
		stmt, err := tx.PrepareContext(ctx,
			`UPDATE api_keys SET last_used_at = ? WHERE id = ? AND (last_used_at IS NULL OR last_used_at < ?)`)
		if err != nil {
			return err
		}
		defer stmt.Close()
		for id, t := range seen {
			ts := t.Unix()
			if _, err := stmt.ExecContext(ctx, ts, id, ts); err != nil {
				return err
			}
		}
		return nil
	})
}