Files
pages/internal/store/publication_approval.go
2026-11-15 05:47:14 +09:00

209 lines
11 KiB
Go

package store
import (
"context"
"crypto/sha256"
"database/sql"
"encoding/hex"
"encoding/json"
"strings"
"time"
"github.com/google/uuid"
"pageswiki/internal/db"
"pageswiki/internal/schutzaria"
)
var ErrApprovalTransition = schutzaria.ErrApprovalTransition
type ApprovalRequest struct {
UUID string `json:"uuid"`
DomainUUID string `json:"domain_uuid"`
ScopeKind string `json:"scope_kind"`
ScopeUUID string `json:"scope_uuid"`
SubjectHash string `json:"subject_hash"`
SubjectJSON string `json:"subject_json,omitempty"`
PolicyVersion int64 `json:"policy_version"`
RequesterUUID string `json:"requester_uuid"`
State string `json:"state"`
Generation int64 `json:"generation"`
IdempotencyKey string `json:"idempotency_key"`
ExpiresAtMS int64 `json:"expires_at_ms"`
CreatedAtMS int64 `json:"created_at_ms"`
UpdatedAtMS int64 `json:"updated_at_ms"`
}
type ApprovalDecision struct {
UUID string `json:"uuid"`
RequestUUID string `json:"request_uuid"`
ReviewerUUID string `json:"reviewer_uuid"`
Generation int64 `json:"generation"`
Decision string `json:"decision"`
Justification string `json:"justification,omitempty"`
CreatedAtMS int64 `json:"created_at_ms"`
}
func (s *Store) EnsurePublicationApprovalSchema() error {
if s.sqlBackend == storePostgreSQL {
return db.ApplyPostgreSQLMigrations(context.Background(), s.DB, "store-publication-approval", []db.PostgreSQLMigration{{Version: 1, Name: "approval requests and decisions", Statements: []string{
`CREATE TABLE publication_requests(uuid TEXT COLLATE "C" PRIMARY KEY,domain_uuid TEXT COLLATE "C" NOT NULL,scope_kind TEXT NOT NULL,scope_uuid TEXT COLLATE "C" NOT NULL,subject_hash TEXT NOT NULL,subject_json TEXT NOT NULL,policy_version BIGINT NOT NULL,requester_uuid TEXT COLLATE "C" NOT NULL,state TEXT NOT NULL CHECK(state IN ('pending','approved','rejected','withdrawn','expired','superseded')),generation BIGINT NOT NULL,idempotency_key TEXT COLLATE "C" NOT NULL,expires_at_ms BIGINT NOT NULL DEFAULT 0,created_at_ms BIGINT NOT NULL,updated_at_ms BIGINT NOT NULL,UNIQUE(domain_uuid,idempotency_key))`,
`CREATE INDEX idx_publication_requests_state ON publication_requests(domain_uuid,state,updated_at_ms DESC,uuid DESC)`,
`CREATE TABLE publication_decisions(uuid TEXT COLLATE "C" PRIMARY KEY,request_uuid TEXT COLLATE "C" NOT NULL REFERENCES publication_requests(uuid),reviewer_uuid TEXT COLLATE "C" NOT NULL,generation BIGINT NOT NULL,decision TEXT NOT NULL CHECK(decision IN ('approved','rejected','changes_requested')),justification TEXT NOT NULL DEFAULT '',created_at_ms BIGINT NOT NULL,UNIQUE(request_uuid,reviewer_uuid,generation))`,
}, Assertions: []string{`SELECT ` + approvalRequestColumns + ` FROM publication_requests WHERE 1=0`, `SELECT uuid,request_uuid,reviewer_uuid,generation,decision,justification,created_at_ms FROM publication_decisions WHERE 1=0`}}})
}
for _, statement := range []string{
`CREATE TABLE IF NOT EXISTS publication_requests (uuid TEXT PRIMARY KEY, domain_uuid TEXT NOT NULL, scope_kind TEXT NOT NULL, scope_uuid TEXT NOT NULL, subject_hash TEXT NOT NULL, subject_json TEXT NOT NULL, policy_version INTEGER NOT NULL, requester_uuid TEXT NOT NULL, state TEXT NOT NULL CHECK(state IN ('pending','approved','rejected','withdrawn','expired','superseded')), generation INTEGER NOT NULL, idempotency_key TEXT NOT NULL, expires_at_ms INTEGER NOT NULL DEFAULT 0, created_at_ms INTEGER NOT NULL, updated_at_ms INTEGER NOT NULL, UNIQUE(domain_uuid,idempotency_key))`,
`CREATE INDEX IF NOT EXISTS idx_publication_requests_state ON publication_requests(domain_uuid,state,updated_at_ms DESC,uuid DESC)`,
`CREATE TABLE IF NOT EXISTS publication_decisions (uuid TEXT PRIMARY KEY, request_uuid TEXT NOT NULL REFERENCES publication_requests(uuid), reviewer_uuid TEXT NOT NULL, generation INTEGER NOT NULL, decision TEXT NOT NULL CHECK(decision IN ('approved','rejected','changes_requested')), justification TEXT NOT NULL DEFAULT '', created_at_ms INTEGER NOT NULL, UNIQUE(request_uuid,reviewer_uuid,generation))`,
} {
if _, err := s.DB.Exec(statement); err != nil {
return err
}
}
return nil
}
func ApprovalSubjectHash(subject any) (string, string, error) {
b, err := json.Marshal(subject)
if err != nil {
return "", "", err
}
h := sha256.Sum256(b)
return hex.EncodeToString(h[:]), string(b), nil
}
func ApprovalSubjectHashJSON(b []byte) (string, string, error) {
var raw json.RawMessage
if err := json.Unmarshal(b, &raw); err != nil {
return "", "", err
}
h := sha256.Sum256(raw)
return hex.EncodeToString(h[:]), string(raw), nil
}
const approvalRequestColumns = `uuid,domain_uuid,scope_kind,scope_uuid,subject_hash,subject_json,policy_version,requester_uuid,state,generation,idempotency_key,expires_at_ms,created_at_ms,updated_at_ms`
func scanApprovalRequest(scanner interface{ Scan(...any) error }, item *ApprovalRequest) error {
return scanner.Scan(&item.UUID, &item.DomainUUID, &item.ScopeKind, &item.ScopeUUID, &item.SubjectHash, &item.SubjectJSON, &item.PolicyVersion, &item.RequesterUUID, &item.State, &item.Generation, &item.IdempotencyKey, &item.ExpiresAtMS, &item.CreatedAtMS, &item.UpdatedAtMS)
}
func (s *Store) CreateApprovalRequestTx(tx *sql.Tx, request ApprovalRequest) (ApprovalRequest, bool, error) {
request.DomainUUID = strings.TrimSpace(request.DomainUUID)
request.ScopeKind = strings.TrimSpace(request.ScopeKind)
request.ScopeUUID = strings.TrimSpace(request.ScopeUUID)
request.RequesterUUID = strings.TrimSpace(request.RequesterUUID)
request.IdempotencyKey = strings.TrimSpace(request.IdempotencyKey)
if request.DomainUUID == "" || request.ScopeKind == "" || request.ScopeUUID == "" || request.RequesterUUID == "" || request.IdempotencyKey == "" || request.SubjectHash == "" {
return ApprovalRequest{}, false, ErrNotificationInput
}
if request.UUID == "" {
request.UUID = uuid.NewString()
}
if request.State == "" {
request.State = "pending"
}
if request.Generation == 0 {
request.Generation = 1
}
if request.CreatedAtMS == 0 {
request.CreatedAtMS = time.Now().UnixMilli()
}
if request.UpdatedAtMS == 0 {
request.UpdatedAtMS = request.CreatedAtMS
}
result, err := tx.Exec(`INSERT INTO publication_requests(`+approvalRequestColumns+`) VALUES(`+s.sqlValues(14)+`) ON CONFLICT(domain_uuid,idempotency_key) DO NOTHING`, request.UUID, request.DomainUUID, request.ScopeKind, request.ScopeUUID, request.SubjectHash, request.SubjectJSON, request.PolicyVersion, request.RequesterUUID, request.State, request.Generation, request.IdempotencyKey, request.ExpiresAtMS, request.CreatedAtMS, request.UpdatedAtMS)
if err != nil {
return ApprovalRequest{}, false, s.sqlError("approval request insert", err)
}
n, err := result.RowsAffected()
if err != nil {
return ApprovalRequest{}, false, s.sqlError("approval request insert", err)
}
if n == 0 {
var existing ApprovalRequest
err = scanApprovalRequest(tx.QueryRow(`SELECT `+approvalRequestColumns+` FROM publication_requests WHERE domain_uuid=`+s.sqlParam(1)+` AND idempotency_key=`+s.sqlParam(2), request.DomainUUID, request.IdempotencyKey), &existing)
return existing, false, s.sqlError("approval request lookup", err)
}
return request, true, nil
}
func (s *Store) DecideApprovalRequest(ctx context.Context, requestUUID, reviewerUUID, decision, justification string, generation int64) error {
return s.decideApprovalRequest(ctx, "", requestUUID, reviewerUUID, decision, justification, generation)
}
func (s *Store) DecideApprovalRequestScoped(ctx context.Context, domainUUID, requestUUID, reviewerUUID, decision, justification string, generation int64) error {
return s.decideApprovalRequest(ctx, domainUUID, requestUUID, reviewerUUID, decision, justification, generation)
}
func (s *Store) decideApprovalRequest(ctx context.Context, domainUUID, requestUUID, reviewerUUID, decision, justification string, generation int64) error {
if requestUUID == "" || reviewerUUID == "" || generation < 1 || !schutzaria.ValidApprovalDecision(decision) {
return ErrApprovalTransition
}
return s.administrativeWrite(ctx, `UPDATE publication_requests SET generation=generation WHERE 0`, func(ctx context.Context, tx *sql.Tx) error {
var state string
var currentGeneration int64
var deadline int64
query := `SELECT state,generation,expires_at_ms FROM publication_requests WHERE uuid=` + s.sqlParam(1)
args := []any{requestUUID}
if strings.TrimSpace(domainUUID) != "" {
query += ` AND domain_uuid=` + s.sqlParam(2)
args = append(args, domainUUID)
}
if s.sqlBackend == storePostgreSQL {
query += ` FOR UPDATE`
}
if err := tx.QueryRowContext(ctx, query, args...).Scan(&state, &currentGeneration, &deadline); err != nil {
return err
}
if deadline > 0 && deadline <= time.Now().UnixMilli() {
return ErrApprovalTransition
}
newState, err := schutzaria.ApprovalNextState(state, currentGeneration, generation, decision)
if err != nil {
return err
}
now := time.Now().UnixMilli()
if _, err := tx.ExecContext(ctx, `INSERT INTO publication_decisions(uuid,request_uuid,reviewer_uuid,generation,decision,justification,created_at_ms) VALUES(`+s.sqlValues(7)+`)`, uuid.NewString(), requestUUID, reviewerUUID, generation, decision, justification, now); err != nil {
return err
}
result, err := tx.ExecContext(ctx, `UPDATE publication_requests SET state=`+s.sqlParam(1)+`,updated_at_ms=`+s.sqlParam(2)+` WHERE uuid=`+s.sqlParam(3)+` AND state='pending' AND generation=`+s.sqlParam(4), newState, now, requestUUID, generation)
if err != nil {
return err
}
n, err := result.RowsAffected()
if err != nil {
return err
}
if n != 1 {
return ErrApprovalTransition
}
return nil
})
}
func (s *Store) GetApprovalRequest(ctx context.Context, domainUUID, requestUUID string) (ApprovalRequest, error) {
var request ApprovalRequest
if strings.TrimSpace(domainUUID) == "" || strings.TrimSpace(requestUUID) == "" {
return request, ErrApprovalTransition
}
err := scanApprovalRequest(s.DB.QueryRowContext(ctx, `SELECT `+approvalRequestColumns+` FROM publication_requests WHERE domain_uuid=`+s.sqlParam(1)+` AND uuid=`+s.sqlParam(2), domainUUID, requestUUID), &request)
return request, s.sqlError("approval request read", err)
}
// ExpireApprovalRequests moves only pending requests whose explicit deadline
// has passed. A zero deadline means no automatic expiry. The bounded update
// keeps scheduler cleanup from monopolizing the SQLite writer.
func (s *Store) ExpireApprovalRequests(ctx context.Context, nowMS int64, limit int) (int64, error) {
if nowMS <= 0 {
nowMS = time.Now().UnixMilli()
}
if limit < 1 || limit > 1000 {
limit = 100
}
result, err := s.DB.ExecContext(ctx, `UPDATE publication_requests SET state='expired',updated_at_ms=`+s.sqlParam(1)+` WHERE state='pending' AND expires_at_ms>0 AND expires_at_ms<=`+s.sqlParam(2)+` AND uuid IN (SELECT uuid FROM publication_requests WHERE state='pending' AND expires_at_ms>0 AND expires_at_ms<=`+s.sqlParam(3)+` ORDER BY expires_at_ms,uuid LIMIT `+s.sqlParam(4)+`)`, nowMS, nowMS, nowMS, limit)
if err != nil {
return 0, s.sqlError("approval request expiry", err)
}
return result.RowsAffected()
}