authkit/store_verify.go

package authkit

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"

	"git.juancwu.dev/juancwu/errx"
)

// columnSpec describes one expected column. dataType is matched against
// information_schema.columns.data_type (e.g. "uuid", "text", "bytea",
// "timestamp with time zone", "integer"). nullable is matched against
// is_nullable ("YES"/"NO"). Extra columns on the live table are allowed.
type columnSpec struct {
	name     string
	dataType string
	nullable bool
}

// tableSpec is the expected layout for one logical table.
type tableSpec struct {
	logicalKey string // matches a Tables field name; used for error messages
	configured string
	defaultNm  string
	columns    []columnSpec
}

// expectedSchema returns the full per-table column specification matching
// migrations/0001_init.sql. The verifier walks each tableSpec, looking up
// information_schema for the configured name first and falling back to the
// default name when the configured name has no rows.
func expectedSchema(s Schema) []tableSpec {
	def := defaultTables()
	t := s.Tables
	return []tableSpec{
		{
			logicalKey: "Users",
			configured: t.Users,
			defaultNm:  def.Users,
			columns: []columnSpec{
				{"id", "uuid", false},
				{"email", "text", false},
				{"email_normalized", "text", false},
				{"email_verified_at", "timestamp with time zone", true},
				{"password_hash", "text", true},
				{"session_version", "integer", false},
				{"last_login_at", "timestamp with time zone", true},
				{"created_at", "timestamp with time zone", false},
				{"updated_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "Sessions",
			configured: t.Sessions,
			defaultNm:  def.Sessions,
			columns: []columnSpec{
				{"id_hash", "bytea", false},
				{"user_id", "uuid", false},
				{"user_agent", "text", false},
				{"ip", "text", true},
				{"created_at", "timestamp with time zone", false},
				{"last_seen_at", "timestamp with time zone", false},
				{"expires_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "Tokens",
			configured: t.Tokens,
			defaultNm:  def.Tokens,
			columns: []columnSpec{
				{"hash", "bytea", false},
				{"kind", "text", false},
				{"user_id", "uuid", false},
				{"chain_id", "text", true},
				{"chain_started_at", "timestamp with time zone", true},
				{"consumed_at", "timestamp with time zone", true},
				{"attempts_remaining", "integer", true},
				{"created_at", "timestamp with time zone", false},
				{"expires_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "ServiceKeys",
			configured: t.ServiceKeys,
			defaultNm:  def.ServiceKeys,
			columns: []columnSpec{
				{"id_hash", "bytea", false},
				{"name", "text", false},
				{"last_used_at", "timestamp with time zone", true},
				{"created_at", "timestamp with time zone", false},
				{"expires_at", "timestamp with time zone", true},
				{"revoked_at", "timestamp with time zone", true},
			},
		},
		{
			logicalKey: "ServiceKeyAbilities",
			configured: t.ServiceKeyAbilities,
			defaultNm:  def.ServiceKeyAbilities,
			columns: []columnSpec{
				{"service_key_id_hash", "bytea", false},
				{"ability_id", "uuid", false},
				{"granted_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "Roles",
			configured: t.Roles,
			defaultNm:  def.Roles,
			columns: []columnSpec{
				{"id", "uuid", false},
				{"slug", "text", false},
				{"label", "text", true},
				{"created_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "Permissions",
			configured: t.Permissions,
			defaultNm:  def.Permissions,
			columns: []columnSpec{
				{"id", "uuid", false},
				{"slug", "text", false},
				{"label", "text", true},
				{"created_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "Abilities",
			configured: t.Abilities,
			defaultNm:  def.Abilities,
			columns: []columnSpec{
				{"id", "uuid", false},
				{"slug", "text", false},
				{"label", "text", true},
				{"created_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "UserRoles",
			configured: t.UserRoles,
			defaultNm:  def.UserRoles,
			columns: []columnSpec{
				{"user_id", "uuid", false},
				{"role_id", "uuid", false},
				{"granted_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "UserPermissions",
			configured: t.UserPermissions,
			defaultNm:  def.UserPermissions,
			columns: []columnSpec{
				{"user_id", "uuid", false},
				{"permission_id", "uuid", false},
				{"granted_at", "timestamp with time zone", false},
			},
		},
		{
			logicalKey: "RolePermissions",
			configured: t.RolePermissions,
			defaultNm:  def.RolePermissions,
			columns: []columnSpec{
				{"role_id", "uuid", false},
				{"permission_id", "uuid", false},
			},
		},
	}
}

// VerifySchema introspects the live database against the expected layout for
// the given schema. Returns a wrapped ErrSchemaDrift describing every
// missing/mismatched table or column. Extra columns on a table are allowed.
//
// For tables with non-default names, VerifySchema looks up the configured
// name first; if no rows are found, it falls back to the default name. This
// handles the case where a consumer migrated under custom names but later
// removed the overrides — drift is detected against whichever set of names
// actually exists.
func VerifySchema(ctx context.Context, db *sql.DB, schema Schema) error {
	const op = "authkit.VerifySchema"
	if db == nil {
		return errx.New(op, "db is required")
	}
	if err := schema.Validate(); err != nil {
		return errx.Wrap(op, err)
	}

	specs := expectedSchema(schema)
	var problems []string

	for _, spec := range specs {
		live, foundUnder, err := loadTableColumns(ctx, db, spec.configured, spec.defaultNm)
		if err != nil {
			return errx.Wrap(op, err)
		}
		if foundUnder == "" {
			problems = append(problems, fmt.Sprintf(
				"table %q (%s): not found (also tried %q)",
				spec.configured, spec.logicalKey, spec.defaultNm))
			continue
		}
		for _, want := range spec.columns {
			got, ok := live[want.name]
			if !ok {
				problems = append(problems, fmt.Sprintf(
					"table %q column %q: missing", foundUnder, want.name))
				continue
			}
			if got.dataType != want.dataType {
				problems = append(problems, fmt.Sprintf(
					"table %q column %q: data_type=%q, want %q",
					foundUnder, want.name, got.dataType, want.dataType))
			}
			if got.nullable != want.nullable {
				problems = append(problems, fmt.Sprintf(
					"table %q column %q: nullable=%v, want %v",
					foundUnder, want.name, got.nullable, want.nullable))
			}
		}
	}

	if len(problems) > 0 {
		sort.Strings(problems)
		return errx.Wrapf(op, ErrSchemaDrift,
			"%d issue(s):\n  - %s", len(problems), strings.Join(problems, "\n  - "))
	}
	return nil
}

// loadTableColumns queries information_schema for a table's columns. If the
// configured name has no rows AND defaultName differs, it falls back to the
// default. Returns the columns map and the name actually used (empty string
// when neither exists).
func loadTableColumns(ctx context.Context, db *sql.DB, configured, defaultName string) (map[string]columnSpec, string, error) {
	live, err := queryColumns(ctx, db, configured)
	if err != nil {
		return nil, "", err
	}
	if len(live) > 0 {
		return live, configured, nil
	}
	if defaultName != "" && defaultName != configured {
		live, err = queryColumns(ctx, db, defaultName)
		if err != nil {
			return nil, "", err
		}
		if len(live) > 0 {
			return live, defaultName, nil
		}
	}
	return nil, "", nil
}

func queryColumns(ctx context.Context, db *sql.DB, table string) (map[string]columnSpec, error) {
	const q = `SELECT column_name, data_type, is_nullable
		FROM information_schema.columns
		WHERE table_schema = current_schema() AND table_name = $1`
	rows, err := db.QueryContext(ctx, q, table)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]columnSpec)
	for rows.Next() {
		var name, dataType, isNullable string
		if err := rows.Scan(&name, &dataType, &isNullable); err != nil {
			return nil, err
		}
		out[name] = columnSpec{
			name:     name,
			dataType: dataType,
			nullable: isNullable == "YES",
		}
	}
	return out, rows.Err()
}