1 files changed, 147 insertions, 0 deletions
diff --git a/internal/storage/roleconfig.go b/internal/storage/roleconfig.go
new file mode 100644
index 0000000..6ae043b
--- /dev/null
+++ b/internal/storage/roleconfig.go
@@ -0,0 +1,147 @@
+package storage
+
+import (
+	"database/sql"
+	"fmt"
+	"time"
+
+	"github.com/google/uuid"
+)
+
+// RoleConfigRow is one versioned row in the role_configs table. ConfigJSON
+// holds the raw JSON-encoded role.RoleConfig; storage intentionally does not
+// import internal/role (no need to — it never inspects the payload, only
+// stores/retrieves it), so callers (internal/executor, internal/scheduler,
+// internal/api) decode it themselves via encoding/json.
+type RoleConfigRow struct {
+	ID          string
+	Role        string
+	Version     int
+	Status      string // "draft" | "active" | "retired"
+	ConfigJSON  string
+	CreatedAt   time.Time
+	ActivatedAt *time.Time
+	RetiredAt   *time.Time
+	ProposedBy  string
+}
+
+// CreateRoleConfig inserts a new draft version for role, auto-assigning the
+// next version number for that role (1 if none exist yet).
+func (s *DB) CreateRoleConfig(roleName, configJSON, proposedBy string) (*RoleConfigRow, error) {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return nil, err
+	}
+	defer tx.Rollback() //nolint:errcheck
+
+	var maxVersion sql.NullInt64
+	if err := tx.QueryRow(`SELECT MAX(version) FROM role_configs WHERE role = ?`, roleName).Scan(&maxVersion); err != nil {
+		return nil, err
+	}
+	version := int(maxVersion.Int64) + 1
+
+	id := uuid.NewString()
+	now := time.Now().UTC()
+	if _, err := tx.Exec(`
+		INSERT INTO role_configs (id, role, version, status, config_json, created_at, proposed_by)
+		VALUES (?, ?, ?, 'draft', ?, ?, ?)`,
+		id, roleName, version, configJSON, now, proposedBy,
+	); err != nil {
+		return nil, err
+	}
+	if err := tx.Commit(); err != nil {
+		return nil, err
+	}
+
+	return &RoleConfigRow{
+		ID:         id,
+		Role:       roleName,
+		Version:    version,
+		Status:     "draft",
+		ConfigJSON: configJSON,
+		CreatedAt:  now,
+		ProposedBy: proposedBy,
+	}, nil
+}
+
+// GetActiveRoleConfig returns the currently active role_configs row for
+// role. Returns sql.ErrNoRows (unwrapped, matching GetTask/GetProject
+// convention in this package) if no version is active.
+func (s *DB) GetActiveRoleConfig(roleName string) (*RoleConfigRow, error) {
+	row := s.db.QueryRow(`
+		SELECT id, role, version, status, config_json, created_at, activated_at, retired_at, proposed_by
+		FROM role_configs WHERE role = ? AND status = 'active' LIMIT 1`, roleName)
+	return scanRoleConfigRow(row)
+}
+
+// ListRoleConfigVersions returns all versions for role, oldest first.
+func (s *DB) ListRoleConfigVersions(roleName string) ([]*RoleConfigRow, error) {
+	rows, err := s.db.Query(`
+		SELECT id, role, version, status, config_json, created_at, activated_at, retired_at, proposed_by
+		FROM role_configs WHERE role = ? ORDER BY version ASC`, roleName)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	var out []*RoleConfigRow
+	for rows.Next() {
+		r, err := scanRoleConfigRow(rows)
+		if err != nil {
+			return nil, err
+		}
+		out = append(out, r)
+	}
+	return out, rows.Err()
+}
+
+// ActivateRoleConfigVersion promotes the given version of role to active,
+// atomically retiring whatever version is currently active for that role (if
+// any) in the same transaction. This enforces "at most one active row per
+// role" without a DB-level constraint, the same way UpdateTaskState enforces
+// the task state machine in a transaction rather than in schema.
+func (s *DB) ActivateRoleConfigVersion(roleName string, version int) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback() //nolint:errcheck
+
+	var exists int
+	if err := tx.QueryRow(`SELECT COUNT(*) FROM role_configs WHERE role = ? AND version = ?`, roleName, version).Scan(&exists); err != nil {
+		return err
+	}
+	if exists == 0 {
+		return fmt.Errorf("role %q version %d not found", roleName, version)
+	}
+
+	now := time.Now().UTC()
+	if _, err := tx.Exec(`UPDATE role_configs SET status = 'retired', retired_at = ? WHERE role = ? AND status = 'active'`, now, roleName); err != nil {
+		return err
+	}
+	if _, err := tx.Exec(`UPDATE role_configs SET status = 'active', activated_at = ? WHERE role = ? AND version = ?`, now, roleName, version); err != nil {
+		return err
+	}
+	return tx.Commit()
+}
+
+func scanRoleConfigRow(row scanner) (*RoleConfigRow, error) {
+	var r RoleConfigRow
+	var createdAt time.Time
+	var activatedAt, retiredAt sql.NullTime
+	var proposedBy sql.NullString
+	if err := row.Scan(&r.ID, &r.Role, &r.Version, &r.Status, &r.ConfigJSON, &createdAt, &activatedAt, &retiredAt, &proposedBy); err != nil {
+		return nil, err
+	}
+	r.CreatedAt = createdAt
+	if activatedAt.Valid {
+		t := activatedAt.Time
+		r.ActivatedAt = &t
+	}
+	if retiredAt.Valid {
+		t := retiredAt.Time
+		r.RetiredAt = &t
+	}
+	r.ProposedBy = proposedBy.String
+	return &r, nil
+}