package backoffice import ( "context" "database/sql" "encoding/json" "errors" "fmt" "strings" "time" "github.com/jackc/pgx/v5" "github.com/jackc/pgx/v5/pgconn" "github.com/jackc/pgx/v5/pgxpool" "github.com/niels/banking-app/backend/internal/domain" ) const ( StatusOpen = "open" StatusAssigned = "assigned" StatusInProgress = "in_progress" StatusWaitingOnCustomer = "waiting_on_customer" StatusWaitingOnProvider = "waiting_on_provider" StatusResolved = "resolved" StatusClosed = "closed" StatusCanceled = "canceled" ApprovalPending = "pending" ApprovalApproved = "approved" ApprovalRejected = "rejected" ApprovalCanceled = "canceled" ApprovalImplemented = "implemented" ) type Repository struct { db *pgxpool.Pool } type Case struct { ID string `json:"id"` Category string `json:"category"` SourceType string `json:"source_type,omitempty"` SourceID string `json:"source_id,omitempty"` CustomerUserID string `json:"customer_user_id,omitempty"` CustomerEmail string `json:"customer_email,omitempty"` Title string `json:"title"` Description string `json:"description"` Priority string `json:"priority"` Status string `json:"status"` OwnerTeam string `json:"owner_team"` AssignedToAdminUserID string `json:"assigned_to_admin_user_id,omitempty"` AssignedAdminEmail string `json:"assigned_admin_email,omitempty"` SLADueAt *time.Time `json:"sla_due_at,omitempty"` ResolvedAt *time.Time `json:"resolved_at,omitempty"` ClosedAt *time.Time `json:"closed_at,omitempty"` CreatedByAdminUserID string `json:"created_by_admin_user_id,omitempty"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` } type CaseNote struct { ID string `json:"id"` CaseID string `json:"case_id"` AdminUserID string `json:"admin_user_id,omitempty"` AdminEmail string `json:"admin_email,omitempty"` NoteType string `json:"note_type"` Body string `json:"body"` CreatedAt time.Time `json:"created_at"` } type CaseAttachment struct { ID string `json:"id"` CaseID string `json:"case_id"` AdminUserID string `json:"admin_user_id,omitempty"` AdminEmail string `json:"admin_email,omitempty"` FileName string `json:"file_name"` ContentType string `json:"content_type"` StorageReference string `json:"storage_reference"` ChecksumSHA256 string `json:"checksum_sha256,omitempty"` Classification string `json:"classification"` CreatedAt time.Time `json:"created_at"` } type CaseHistoryEvent struct { ID string `json:"id"` CaseID string `json:"case_id"` ActorAdminUserID string `json:"actor_admin_user_id,omitempty"` ActorEmail string `json:"actor_email,omitempty"` EventType string `json:"event_type"` FromStatus string `json:"from_status,omitempty"` ToStatus string `json:"to_status,omitempty"` FromAssigneeAdminID string `json:"from_assignee_admin_user_id,omitempty"` ToAssigneeAdminID string `json:"to_assignee_admin_user_id,omitempty"` FromAssigneeEmail string `json:"from_assignee_email,omitempty"` ToAssigneeEmail string `json:"to_assignee_email,omitempty"` Metadata json.RawMessage `json:"metadata"` CreatedAt time.Time `json:"created_at"` } type ConfigChangeRequest struct { ID string `json:"id"` TargetArea string `json:"target_area"` ChangeType string `json:"change_type"` TargetType string `json:"target_type,omitempty"` TargetID string `json:"target_id,omitempty"` Status string `json:"status"` RequesterAdminUserID string `json:"requester_admin_user_id"` RequesterEmail string `json:"requester_email,omitempty"` ReviewerAdminUserID string `json:"reviewer_admin_user_id,omitempty"` ReviewerEmail string `json:"reviewer_email,omitempty"` ProposedPayload json.RawMessage `json:"proposed_payload"` Reason string `json:"reason"` DecisionNote string `json:"decision_note,omitempty"` EvidenceReference string `json:"evidence_reference,omitempty"` DecidedAt *time.Time `json:"decided_at,omitempty"` ImplementedAt *time.Time `json:"implemented_at,omitempty"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` } type ImpersonationSession struct { ID string `json:"id"` AdminUserID string `json:"admin_user_id"` AdminEmail string `json:"admin_email,omitempty"` CustomerUserID string `json:"customer_user_id"` CustomerEmail string `json:"customer_email,omitempty"` Status string `json:"status"` Reason string `json:"reason"` TicketReference string `json:"ticket_reference,omitempty"` AllowedActions []string `json:"allowed_actions"` StartedAt time.Time `json:"started_at"` ExpiresAt time.Time `json:"expires_at"` EndedAt *time.Time `json:"ended_at,omitempty"` RemoteIP string `json:"remote_ip,omitempty"` UserAgent string `json:"user_agent,omitempty"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` } type AdminAction struct { ID string `json:"id"` ActorUserID string `json:"actor_user_id,omitempty"` ActorEmail string `json:"actor_email,omitempty"` EventType string `json:"event_type"` TargetType string `json:"target_type"` TargetID string `json:"target_id"` Metadata json.RawMessage `json:"metadata"` RemoteIP string `json:"remote_ip,omitempty"` UserAgent string `json:"user_agent,omitempty"` PreviousHash string `json:"previous_hash,omitempty"` EventHash string `json:"event_hash,omitempty"` CreatedAt time.Time `json:"created_at"` } type Metrics struct { OpenCases int64 `json:"open_cases"` OverdueCases int64 `json:"overdue_cases"` CriticalCases int64 `json:"critical_cases"` PendingConfigChanges int64 `json:"pending_config_changes"` ActiveImpersonations int64 `json:"active_impersonations"` Notes24h int64 `json:"notes_24h"` Attachments24h int64 `json:"attachments_24h"` ResolvedCases24h int64 `json:"resolved_cases_24h"` } type RoleDashboard struct { RoleName string `json:"role_name"` Metrics Metrics `json:"metrics"` Cases []Case `json:"cases"` ConfigChanges []ConfigChangeRequest `json:"config_changes"` Impersonations []ImpersonationSession `json:"impersonation_sessions"` Widgets []DashboardWidget `json:"widgets"` GeneratedAt time.Time `json:"generated_at"` } type DashboardWidget struct { RoleName string `json:"role_name"` WidgetKey string `json:"widget_key"` Enabled bool `json:"enabled"` SortOrder int `json:"sort_order"` UpdatedAt time.Time `json:"updated_at"` } type ActionExport struct { GeneratedAt time.Time `json:"generated_at"` Filters ExportFilters `json:"filters"` Actions []AdminAction `json:"actions"` Count int `json:"count"` } type ExportFilters struct { ActorUserID string `json:"actor_user_id,omitempty"` TargetType string `json:"target_type,omitempty"` TargetID string `json:"target_id,omitempty"` From *time.Time `json:"from,omitempty"` To *time.Time `json:"to,omitempty"` } type EvidencePackage struct { GeneratedAt time.Time `json:"generated_at"` TargetType string `json:"target_type"` TargetID string `json:"target_id"` Case *Case `json:"case,omitempty"` Notes []CaseNote `json:"notes,omitempty"` Attachments []CaseAttachment `json:"attachments,omitempty"` History []CaseHistoryEvent `json:"history,omitempty"` ConfigChange *ConfigChangeRequest `json:"config_change,omitempty"` AuditActions []AdminAction `json:"audit_actions"` Impersonations []ImpersonationSession `json:"impersonation_sessions,omitempty"` } type CaseParams struct { Category string SourceType string SourceID string CustomerUserID string Title string Description string Priority string Status string OwnerTeam string AssignedTo string SLADueAt *time.Time AdminUserID string } type CaseUpdateParams struct { ID string Status string Priority string OwnerTeam string AssignedTo string SLADueAt *time.Time Description string AdminUserID string } type NoteParams struct { CaseID string AdminUserID string NoteType string Body string } type AttachmentParams struct { CaseID string AdminUserID string FileName string ContentType string StorageReference string ChecksumSHA256 string Classification string } type ConfigChangeParams struct { TargetArea string ChangeType string TargetType string TargetID string ProposedPayload json.RawMessage Reason string AdminUserID string } type ConfigDecisionParams struct { ID string AdminUserID string Action string DecisionNote string EvidenceReference string } type ImpersonationParams struct { AdminUserID string CustomerUserID string Reason string TicketReference string AllowedActions []string ExpiresAt time.Time RemoteIP string UserAgent string } func NewRepository(db *pgxpool.Pool) *Repository { return &Repository{db: db} } func (r *Repository) Dashboard(ctx context.Context, role string, limit int) (RoleDashboard, error) { role = normalizeDashboardRole(role) metrics, err := r.Metrics(ctx) if err != nil { return RoleDashboard{}, err } cases, err := r.ListCases(ctx, roleCategory(role), "", roleOwnerTeam(role), "", limit) if err != nil { return RoleDashboard{}, err } configChanges, err := r.ListConfigChanges(ctx, ApprovalPending, "", limit) if err != nil { return RoleDashboard{}, err } impersonations, err := r.ListImpersonationSessions(ctx, "active", "", limit) if err != nil { return RoleDashboard{}, err } widgets, err := r.ListDashboardWidgets(ctx, role) if err != nil { return RoleDashboard{}, err } return RoleDashboard{ RoleName: role, Metrics: metrics, Cases: cases, ConfigChanges: configChanges, Impersonations: impersonations, Widgets: widgets, GeneratedAt: time.Now().UTC(), }, nil } func (r *Repository) Metrics(ctx context.Context) (Metrics, error) { var metrics Metrics err := r.db.QueryRow(ctx, ` SELECT (SELECT COUNT(*) FROM backoffice_cases WHERE status NOT IN ('resolved', 'closed', 'canceled'))::bigint, (SELECT COUNT(*) FROM backoffice_cases WHERE status NOT IN ('resolved', 'closed', 'canceled') AND sla_due_at IS NOT NULL AND sla_due_at < now())::bigint, (SELECT COUNT(*) FROM backoffice_cases WHERE status NOT IN ('resolved', 'closed', 'canceled') AND priority = 'critical')::bigint, (SELECT COUNT(*) FROM high_risk_config_change_requests WHERE status = 'pending')::bigint, (SELECT COUNT(*) FROM support_impersonation_sessions WHERE status = 'active' AND expires_at > now())::bigint, (SELECT COUNT(*) FROM backoffice_case_notes WHERE created_at >= now() - interval '24 hours')::bigint, (SELECT COUNT(*) FROM backoffice_case_attachments WHERE created_at >= now() - interval '24 hours')::bigint, (SELECT COUNT(*) FROM backoffice_cases WHERE resolved_at >= now() - interval '24 hours')::bigint `).Scan( &metrics.OpenCases, &metrics.OverdueCases, &metrics.CriticalCases, &metrics.PendingConfigChanges, &metrics.ActiveImpersonations, &metrics.Notes24h, &metrics.Attachments24h, &metrics.ResolvedCases24h, ) return metrics, err } func (r *Repository) SyncQueues(ctx context.Context) (map[string]int64, error) { tx, err := r.db.Begin(ctx) if err != nil { return nil, err } defer tx.Rollback(ctx) results := map[string]int64{} statements := []struct { key string sql string }{ {"reconciliation_break", ` INSERT INTO backoffice_cases (category, source_type, source_id, title, description, priority, status, owner_team, assigned_to_admin_user_id, sla_due_at) SELECT 'reconciliation_break', 'reconciliation_break', rb.id::text, 'Reconciliation break: ' || rb.break_type, rb.description, rb.severity, CASE rb.status WHEN 'investigating' THEN 'in_progress' ELSE 'open' END, 'finance', rb.owner_user_id, rb.created_at + interval '1 day' FROM reconciliation_breaks rb WHERE rb.status IN ('open', 'investigating') ON CONFLICT DO NOTHING`}, {"payment_review", ` INSERT INTO backoffice_cases (category, source_type, source_id, customer_user_id, title, description, priority, status, owner_team, assigned_to_admin_user_id, sla_due_at) SELECT 'payment_review', 'payment_review_case', prc.id::text, t.user_id, 'Manual payment review', prc.reason, CASE WHEN prc.reason_code LIKE '%SANCTIONS%' THEN 'critical' ELSE 'high' END, 'open', 'operations', prc.assigned_admin_user_id, prc.opened_at + interval '4 hours' FROM payment_review_cases prc JOIN transfers t ON t.id = prc.transfer_id WHERE prc.status = 'open' ON CONFLICT DO NOTHING`}, {"fraud_review", ` INSERT INTO backoffice_cases (category, source_type, source_id, customer_user_id, title, description, priority, status, owner_team, sla_due_at) SELECT 'fraud_review', 'risk_event', re.id::text, re.user_id, 'Risk event: ' || re.operation, re.reason, re.severity, 'open', 'risk', re.created_at + interval '8 hours' FROM risk_events re WHERE re.decision IN ('review', 'block') ON CONFLICT DO NOTHING`}, {"provider_incident", ` INSERT INTO backoffice_cases (category, source_type, source_id, title, description, priority, status, owner_team, sla_due_at) SELECT 'provider_incident', 'sepa_provider_report', spr.id::text, 'Provider report failed: ' || spr.provider, spr.report_reference, 'high', 'open', 'operations', spr.created_at + interval '4 hours' FROM sepa_provider_reports spr WHERE spr.status = 'failed' ON CONFLICT DO NOTHING`}, {"security_event", ` INSERT INTO backoffice_cases (category, source_type, source_id, customer_user_id, title, description, priority, status, owner_team, sla_due_at) SELECT 'security_event', 'security_event', se.id::text, se.user_id, 'Security event: ' || se.event_type, se.identifier, se.severity, CASE se.status WHEN 'reviewing' THEN 'in_progress' ELSE 'open' END, 'security', se.created_at + interval '8 hours' FROM security_events se WHERE se.status IN ('open', 'reviewing') ON CONFLICT DO NOTHING`}, } for _, statement := range statements { tag, err := tx.Exec(ctx, statement.sql) if err != nil { return nil, err } results[statement.key] = tag.RowsAffected() } return results, tx.Commit(ctx) } func (r *Repository) ListCases(ctx context.Context, category, status, ownerTeam, assignedTo string, limit int) ([]Case, error) { limit = normalizeLimit(limit) category = strings.ToLower(strings.TrimSpace(category)) status = strings.ToLower(strings.TrimSpace(status)) ownerTeam = strings.ToLower(strings.TrimSpace(ownerTeam)) assignedTo = strings.TrimSpace(assignedTo) if category != "" && category != "all" { if err := validateCategory(category); err != nil { return nil, err } } if status != "" && status != "all" && status != "open_work" { if err := validateCaseStatus(status); err != nil { return nil, err } } if ownerTeam != "" && ownerTeam != "all" { if err := validateOwnerTeam(ownerTeam); err != nil { return nil, err } } if assignedTo != "" && assignedTo != "me" { if err := domain.ValidateUUID("assigned_to", assignedTo); err != nil { return nil, err } } rows, err := r.db.Query(ctx, caseSelect+` WHERE ($1 = '' OR $1 = 'all' OR c.category = $1) AND ($2 = '' OR $2 = 'all' OR ($2 = 'open_work' AND c.status NOT IN ('resolved', 'closed', 'canceled')) OR c.status = $2) AND ($3 = '' OR $3 = 'all' OR c.owner_team = $3) AND (NULLIF($4, '') IS NULL OR c.assigned_to_admin_user_id = NULLIF($4, '')::uuid) ORDER BY CASE WHEN c.sla_due_at IS NOT NULL AND c.sla_due_at < now() THEN 0 ELSE 1 END, CASE c.priority WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END, c.updated_at DESC LIMIT $5 `, category, status, ownerTeam, assignedTo, limit) if err != nil { return nil, err } defer rows.Close() cases := []Case{} for rows.Next() { item, err := scanCase(rows) if err != nil { return nil, err } cases = append(cases, item) } return cases, rows.Err() } func (r *Repository) CreateCase(ctx context.Context, params CaseParams) (Case, error) { if err := normalizeCaseParams(¶ms); err != nil { return Case{}, err } tx, err := r.db.Begin(ctx) if err != nil { return Case{}, err } defer tx.Rollback(ctx) var caseID string err = tx.QueryRow(ctx, ` INSERT INTO backoffice_cases ( category, source_type, source_id, customer_user_id, title, description, priority, status, owner_team, assigned_to_admin_user_id, sla_due_at, created_by_admin_user_id, resolved_at, closed_at ) VALUES ($1, $2, $3, NULLIF($4, '')::uuid, $5, $6, $7, $8, $9, NULLIF($10, '')::uuid, $11, NULLIF($12, '')::uuid, CASE WHEN $8 = 'resolved' THEN now() ELSE NULL END, CASE WHEN $8 = 'closed' THEN now() ELSE NULL END) RETURNING id::text `, params.Category, params.SourceType, params.SourceID, params.CustomerUserID, params.Title, params.Description, params.Priority, params.Status, params.OwnerTeam, params.AssignedTo, params.SLADueAt, params.AdminUserID).Scan(&caseID) if err != nil && isForeignKeyViolation(err) { return Case{}, fmt.Errorf("%w: referenced user does not exist", domain.ErrValidation) } if err != nil && isUniqueViolation(err) { return Case{}, fmt.Errorf("%w: case already exists for this source", domain.ErrConflict) } if err != nil { return Case{}, err } if err := insertHistory(ctx, tx, caseID, params.AdminUserID, "created", "", params.Status, "", params.AssignedTo, map[string]any{ "category": params.Category, "source_type": params.SourceType, "source_id": params.SourceID, }); err != nil { return Case{}, err } item, err := findCase(ctx, tx, caseID) if err != nil { return Case{}, err } return item, tx.Commit(ctx) } func (r *Repository) UpdateCase(ctx context.Context, params CaseUpdateParams) (Case, error) { if err := normalizeCaseUpdateParams(¶ms); err != nil { return Case{}, err } tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable}) if err != nil { return Case{}, err } defer tx.Rollback(ctx) before, err := findCase(ctx, tx, params.ID) if err != nil { return Case{}, err } status := params.Status if status == "" { status = before.Status } priority := params.Priority if priority == "" { priority = before.Priority } ownerTeam := params.OwnerTeam if ownerTeam == "" { ownerTeam = before.OwnerTeam } assignee := params.AssignedTo description := params.Description if description == "" { description = before.Description } _, err = tx.Exec(ctx, ` UPDATE backoffice_cases SET status = $2, priority = $3, owner_team = $4, assigned_to_admin_user_id = NULLIF($5, '')::uuid, sla_due_at = $6, description = $7, resolved_at = CASE WHEN $2 = 'resolved' AND resolved_at IS NULL THEN now() WHEN $2 <> 'resolved' THEN NULL ELSE resolved_at END, closed_at = CASE WHEN $2 = 'closed' AND closed_at IS NULL THEN now() WHEN $2 <> 'closed' THEN NULL ELSE closed_at END WHERE id = $1 `, params.ID, status, priority, ownerTeam, assignee, params.SLADueAt, description) if err != nil && isForeignKeyViolation(err) { return Case{}, fmt.Errorf("%w: assigned admin does not exist", domain.ErrValidation) } if err != nil { return Case{}, err } if before.Status != status { if err := insertHistory(ctx, tx, params.ID, params.AdminUserID, "status_changed", before.Status, status, before.AssignedToAdminUserID, assignee, nil); err != nil { return Case{}, err } } if before.AssignedToAdminUserID != assignee { if err := insertHistory(ctx, tx, params.ID, params.AdminUserID, "assigned", before.Status, status, before.AssignedToAdminUserID, assignee, nil); err != nil { return Case{}, err } } if before.Priority != priority { if err := insertHistory(ctx, tx, params.ID, params.AdminUserID, "priority_changed", before.Status, status, before.AssignedToAdminUserID, assignee, map[string]any{"from_priority": before.Priority, "to_priority": priority}); err != nil { return Case{}, err } } after, err := findCase(ctx, tx, params.ID) if err != nil { return Case{}, err } return after, tx.Commit(ctx) } func (r *Repository) AddNote(ctx context.Context, params NoteParams) (CaseNote, error) { if err := normalizeNoteParams(¶ms); err != nil { return CaseNote{}, err } tx, err := r.db.Begin(ctx) if err != nil { return CaseNote{}, err } defer tx.Rollback(ctx) var noteID string err = tx.QueryRow(ctx, ` INSERT INTO backoffice_case_notes (case_id, admin_user_id, note_type, body) VALUES ($1, NULLIF($2, '')::uuid, $3, $4) RETURNING id::text `, params.CaseID, params.AdminUserID, params.NoteType, params.Body).Scan(¬eID) if err != nil && isForeignKeyViolation(err) { return CaseNote{}, fmt.Errorf("%w: case or admin user does not exist", domain.ErrValidation) } if err != nil { return CaseNote{}, err } if err := insertHistory(ctx, tx, params.CaseID, params.AdminUserID, "note_added", "", "", "", "", map[string]any{"note_type": params.NoteType}); err != nil { return CaseNote{}, err } note, err := findNote(ctx, tx, noteID) if err != nil { return CaseNote{}, err } return note, tx.Commit(ctx) } func (r *Repository) ListNotes(ctx context.Context, caseID string, limit int) ([]CaseNote, error) { if err := domain.ValidateUUID("case_id", strings.TrimSpace(caseID)); err != nil { return nil, err } rows, err := r.db.Query(ctx, noteSelect+` WHERE n.case_id = $1 ORDER BY n.created_at DESC LIMIT $2 `, caseID, normalizeLimit(limit)) if err != nil { return nil, err } defer rows.Close() notes := []CaseNote{} for rows.Next() { note, err := scanNote(rows) if err != nil { return nil, err } notes = append(notes, note) } return notes, rows.Err() } func (r *Repository) AddAttachment(ctx context.Context, params AttachmentParams) (CaseAttachment, error) { if err := normalizeAttachmentParams(¶ms); err != nil { return CaseAttachment{}, err } tx, err := r.db.Begin(ctx) if err != nil { return CaseAttachment{}, err } defer tx.Rollback(ctx) var attachmentID string err = tx.QueryRow(ctx, ` INSERT INTO backoffice_case_attachments ( case_id, admin_user_id, file_name, content_type, storage_reference, checksum_sha256, classification ) VALUES ($1, NULLIF($2, '')::uuid, $3, $4, $5, $6, $7) RETURNING id::text `, params.CaseID, params.AdminUserID, params.FileName, params.ContentType, params.StorageReference, params.ChecksumSHA256, params.Classification).Scan(&attachmentID) if err != nil && isForeignKeyViolation(err) { return CaseAttachment{}, fmt.Errorf("%w: case or admin user does not exist", domain.ErrValidation) } if err != nil { return CaseAttachment{}, err } if err := insertHistory(ctx, tx, params.CaseID, params.AdminUserID, "attachment_added", "", "", "", "", map[string]any{"file_name": params.FileName, "classification": params.Classification}); err != nil { return CaseAttachment{}, err } attachment, err := findAttachment(ctx, tx, attachmentID) if err != nil { return CaseAttachment{}, err } return attachment, tx.Commit(ctx) } func (r *Repository) ListAttachments(ctx context.Context, caseID string, limit int) ([]CaseAttachment, error) { if err := domain.ValidateUUID("case_id", strings.TrimSpace(caseID)); err != nil { return nil, err } rows, err := r.db.Query(ctx, attachmentSelect+` WHERE a.case_id = $1 ORDER BY a.created_at DESC LIMIT $2 `, caseID, normalizeLimit(limit)) if err != nil { return nil, err } defer rows.Close() attachments := []CaseAttachment{} for rows.Next() { attachment, err := scanAttachment(rows) if err != nil { return nil, err } attachments = append(attachments, attachment) } return attachments, rows.Err() } func (r *Repository) ListHistory(ctx context.Context, caseID string, limit int) ([]CaseHistoryEvent, error) { if err := domain.ValidateUUID("case_id", strings.TrimSpace(caseID)); err != nil { return nil, err } rows, err := r.db.Query(ctx, historySelect+` WHERE h.case_id = $1 ORDER BY h.created_at DESC LIMIT $2 `, caseID, normalizeLimit(limit)) if err != nil { return nil, err } defer rows.Close() events := []CaseHistoryEvent{} for rows.Next() { event, err := scanHistory(rows) if err != nil { return nil, err } events = append(events, event) } return events, rows.Err() } func (r *Repository) ListConfigChanges(ctx context.Context, status, targetArea string, limit int) ([]ConfigChangeRequest, error) { status = strings.ToLower(strings.TrimSpace(status)) targetArea = strings.ToLower(strings.TrimSpace(targetArea)) if status == "" { status = "all" } if status != "all" { if err := validateApprovalStatus(status); err != nil { return nil, err } } if targetArea != "" && targetArea != "all" { if err := validateTargetArea(targetArea); err != nil { return nil, err } } rows, err := r.db.Query(ctx, configChangeSelect+` WHERE ($1 = 'all' OR req.status = $1) AND ($2 = '' OR $2 = 'all' OR req.target_area = $2) ORDER BY CASE WHEN req.status = 'pending' THEN 0 ELSE 1 END, req.created_at DESC LIMIT $3 `, status, targetArea, normalizeLimit(limit)) if err != nil { return nil, err } defer rows.Close() requests := []ConfigChangeRequest{} for rows.Next() { request, err := scanConfigChange(rows) if err != nil { return nil, err } requests = append(requests, request) } return requests, rows.Err() } func (r *Repository) CreateConfigChange(ctx context.Context, params ConfigChangeParams) (ConfigChangeRequest, error) { if err := normalizeConfigChangeParams(¶ms); err != nil { return ConfigChangeRequest{}, err } row := r.db.QueryRow(ctx, ` INSERT INTO high_risk_config_change_requests ( target_area, change_type, target_type, target_id, requester_admin_user_id, proposed_payload, reason ) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id::text `, params.TargetArea, params.ChangeType, params.TargetType, params.TargetID, params.AdminUserID, params.ProposedPayload, params.Reason) var id string if err := row.Scan(&id); err != nil { if isForeignKeyViolation(err) { return ConfigChangeRequest{}, fmt.Errorf("%w: requester admin does not exist", domain.ErrValidation) } return ConfigChangeRequest{}, err } return r.GetConfigChange(ctx, id) } func (r *Repository) DecideConfigChange(ctx context.Context, params ConfigDecisionParams) (ConfigChangeRequest, error) { if err := normalizeConfigDecisionParams(¶ms); err != nil { return ConfigChangeRequest{}, err } status := map[string]string{ "approve": ApprovalApproved, "reject": ApprovalRejected, "cancel": ApprovalCanceled, "implement": ApprovalImplemented, }[params.Action] row := r.db.QueryRow(ctx, configChangeSelect+` WHERE req.id = $1 FOR UPDATE `, params.ID) current, err := scanConfigChange(row) if errors.Is(err, pgx.ErrNoRows) { return ConfigChangeRequest{}, domain.ErrNotFound } if err != nil { return ConfigChangeRequest{}, err } if current.Status != ApprovalPending && !(current.Status == ApprovalApproved && status == ApprovalImplemented) { return ConfigChangeRequest{}, fmt.Errorf("%w: config change is not pending", domain.ErrConflict) } if current.RequesterAdminUserID == params.AdminUserID && (status == ApprovalApproved || status == ApprovalRejected || status == ApprovalImplemented) { return ConfigChangeRequest{}, fmt.Errorf("%w: requester cannot review their own config change", domain.ErrForbidden) } _, err = r.db.Exec(ctx, ` UPDATE high_risk_config_change_requests SET status = $2, reviewer_admin_user_id = CASE WHEN $2 IN ('approved', 'rejected', 'implemented') THEN $3 ELSE reviewer_admin_user_id END, decision_note = NULLIF($4, ''), evidence_reference = COALESCE(NULLIF($5, ''), evidence_reference), decided_at = CASE WHEN $2 IN ('approved', 'rejected', 'implemented') THEN COALESCE(decided_at, now()) ELSE decided_at END, implemented_at = CASE WHEN $2 = 'implemented' THEN now() ELSE implemented_at END WHERE id = $1 `, params.ID, status, params.AdminUserID, params.DecisionNote, params.EvidenceReference) if err != nil && isForeignKeyViolation(err) { return ConfigChangeRequest{}, fmt.Errorf("%w: reviewer admin does not exist", domain.ErrValidation) } if err != nil { return ConfigChangeRequest{}, err } return r.GetConfigChange(ctx, params.ID) } func (r *Repository) GetConfigChange(ctx context.Context, id string) (ConfigChangeRequest, error) { if err := domain.ValidateUUID("id", strings.TrimSpace(id)); err != nil { return ConfigChangeRequest{}, err } change, err := scanConfigChange(r.db.QueryRow(ctx, configChangeSelect+` WHERE req.id = $1`, id)) if errors.Is(err, pgx.ErrNoRows) { return ConfigChangeRequest{}, domain.ErrNotFound } return change, err } func (r *Repository) ActionExport(ctx context.Context, filters ExportFilters, limit int) (ActionExport, error) { limit = normalizeExportLimit(limit) rows, err := r.db.Query(ctx, actionSelect+` WHERE (NULLIF($1, '') IS NULL OR ae.actor_user_id = NULLIF($1, '')::uuid) AND (NULLIF($2, '') IS NULL OR ae.target_type = $2) AND (NULLIF($3, '') IS NULL OR ae.target_id = $3) AND ($4::timestamptz IS NULL OR ae.created_at >= $4) AND ($5::timestamptz IS NULL OR ae.created_at <= $5) ORDER BY ae.created_at DESC LIMIT $6 `, filters.ActorUserID, filters.TargetType, filters.TargetID, filters.From, filters.To, limit) if err != nil { return ActionExport{}, err } defer rows.Close() actions := []AdminAction{} for rows.Next() { action, err := scanAdminAction(rows) if err != nil { return ActionExport{}, err } actions = append(actions, action) } return ActionExport{GeneratedAt: time.Now().UTC(), Filters: filters, Actions: actions, Count: len(actions)}, rows.Err() } func (r *Repository) EvidencePackage(ctx context.Context, targetType, targetID string, limit int) (EvidencePackage, error) { targetType = strings.ToLower(strings.TrimSpace(targetType)) targetID = strings.TrimSpace(targetID) if targetID == "" { return EvidencePackage{}, fmt.Errorf("%w: target_id is required", domain.ErrValidation) } pkg := EvidencePackage{GeneratedAt: time.Now().UTC(), TargetType: targetType, TargetID: targetID} switch targetType { case "backoffice_case": item, err := r.GetCase(ctx, targetID) if err != nil { return EvidencePackage{}, err } pkg.Case = &item notes, _ := r.ListNotes(ctx, targetID, limit) attachments, _ := r.ListAttachments(ctx, targetID, limit) history, _ := r.ListHistory(ctx, targetID, limit) pkg.Notes = notes pkg.Attachments = attachments pkg.History = history case "config_change": change, err := r.GetConfigChange(ctx, targetID) if err != nil { return EvidencePackage{}, err } pkg.ConfigChange = &change default: return EvidencePackage{}, fmt.Errorf("%w: unsupported evidence target_type", domain.ErrValidation) } export, err := r.ActionExport(ctx, ExportFilters{TargetType: targetType, TargetID: targetID}, limit) if err != nil { return EvidencePackage{}, err } pkg.AuditActions = export.Actions return pkg, nil } func (r *Repository) StartImpersonation(ctx context.Context, params ImpersonationParams) (ImpersonationSession, error) { if err := normalizeImpersonationParams(¶ms); err != nil { return ImpersonationSession{}, err } var id string err := r.db.QueryRow(ctx, ` INSERT INTO support_impersonation_sessions ( admin_user_id, customer_user_id, reason, ticket_reference, allowed_actions, expires_at, remote_ip, user_agent ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id::text `, params.AdminUserID, params.CustomerUserID, params.Reason, params.TicketReference, params.AllowedActions, params.ExpiresAt, params.RemoteIP, params.UserAgent).Scan(&id) if err != nil && isForeignKeyViolation(err) { return ImpersonationSession{}, fmt.Errorf("%w: admin or customer user does not exist", domain.ErrValidation) } if err != nil { return ImpersonationSession{}, err } return r.GetImpersonationSession(ctx, id) } func (r *Repository) EndImpersonation(ctx context.Context, id, adminUserID string) (ImpersonationSession, error) { if err := domain.ValidateUUID("id", strings.TrimSpace(id)); err != nil { return ImpersonationSession{}, err } if err := domain.ValidateUUID("admin_user_id", strings.TrimSpace(adminUserID)); err != nil { return ImpersonationSession{}, err } _, err := r.db.Exec(ctx, ` UPDATE support_impersonation_sessions SET status = 'ended', ended_at = now() WHERE id = $1 AND status = 'active' `, id) if err != nil { return ImpersonationSession{}, err } return r.GetImpersonationSession(ctx, id) } func (r *Repository) ListImpersonationSessions(ctx context.Context, status, customerID string, limit int) ([]ImpersonationSession, error) { status = strings.ToLower(strings.TrimSpace(status)) customerID = strings.TrimSpace(customerID) if status == "" { status = "active" } if status != "all" && !validImpersonationStatus(status) { return nil, fmt.Errorf("%w: invalid impersonation status", domain.ErrValidation) } if customerID != "" { if err := domain.ValidateUUID("customer_user_id", customerID); err != nil { return nil, err } } rows, err := r.db.Query(ctx, impersonationSelect+` WHERE ($1 = 'all' OR s.status = $1) AND (NULLIF($2, '') IS NULL OR s.customer_user_id = NULLIF($2, '')::uuid) ORDER BY s.created_at DESC LIMIT $3 `, status, customerID, normalizeLimit(limit)) if err != nil { return nil, err } defer rows.Close() sessions := []ImpersonationSession{} for rows.Next() { session, err := scanImpersonation(rows) if err != nil { return nil, err } sessions = append(sessions, session) } return sessions, rows.Err() } func (r *Repository) GetCase(ctx context.Context, id string) (Case, error) { if err := domain.ValidateUUID("id", strings.TrimSpace(id)); err != nil { return Case{}, err } item, err := scanCase(r.db.QueryRow(ctx, caseSelect+` WHERE c.id = $1`, id)) if errors.Is(err, pgx.ErrNoRows) { return Case{}, domain.ErrNotFound } return item, err } func (r *Repository) GetImpersonationSession(ctx context.Context, id string) (ImpersonationSession, error) { if err := domain.ValidateUUID("id", strings.TrimSpace(id)); err != nil { return ImpersonationSession{}, err } session, err := scanImpersonation(r.db.QueryRow(ctx, impersonationSelect+` WHERE s.id = $1`, id)) if errors.Is(err, pgx.ErrNoRows) { return ImpersonationSession{}, domain.ErrNotFound } return session, err } func (r *Repository) ListDashboardWidgets(ctx context.Context, role string) ([]DashboardWidget, error) { role = normalizeDashboardRole(role) rows, err := r.db.Query(ctx, ` SELECT role_name, widget_key, enabled, sort_order, updated_at FROM backoffice_dashboard_widgets WHERE role_name = $1 AND enabled = true ORDER BY sort_order, widget_key `, role) if err != nil { return nil, err } defer rows.Close() widgets := []DashboardWidget{} for rows.Next() { var widget DashboardWidget if err := rows.Scan(&widget.RoleName, &widget.WidgetKey, &widget.Enabled, &widget.SortOrder, &widget.UpdatedAt); err != nil { return nil, err } widgets = append(widgets, widget) } return widgets, rows.Err() } const caseSelect = ` SELECT c.id::text, c.category, c.source_type, c.source_id, COALESCE(c.customer_user_id::text, ''), COALESCE(cu.email, ''), c.title, c.description, c.priority, c.status, c.owner_team, COALESCE(c.assigned_to_admin_user_id::text, ''), COALESCE(au.email, ''), c.sla_due_at, c.resolved_at, c.closed_at, COALESCE(c.created_by_admin_user_id::text, ''), c.created_at, c.updated_at FROM backoffice_cases c LEFT JOIN users cu ON cu.id = c.customer_user_id LEFT JOIN users au ON au.id = c.assigned_to_admin_user_id ` const noteSelect = ` SELECT n.id::text, n.case_id::text, COALESCE(n.admin_user_id::text, ''), COALESCE(u.email, ''), n.note_type, n.body, n.created_at FROM backoffice_case_notes n LEFT JOIN users u ON u.id = n.admin_user_id ` const attachmentSelect = ` SELECT a.id::text, a.case_id::text, COALESCE(a.admin_user_id::text, ''), COALESCE(u.email, ''), a.file_name, a.content_type, a.storage_reference, a.checksum_sha256, a.classification, a.created_at FROM backoffice_case_attachments a LEFT JOIN users u ON u.id = a.admin_user_id ` const historySelect = ` SELECT h.id::text, h.case_id::text, COALESCE(h.actor_admin_user_id::text, ''), COALESCE(actor.email, ''), h.event_type, h.from_status, h.to_status, COALESCE(h.from_assignee_admin_user_id::text, ''), COALESCE(h.to_assignee_admin_user_id::text, ''), COALESCE(from_user.email, ''), COALESCE(to_user.email, ''), h.metadata, h.created_at FROM backoffice_case_history h LEFT JOIN users actor ON actor.id = h.actor_admin_user_id LEFT JOIN users from_user ON from_user.id = h.from_assignee_admin_user_id LEFT JOIN users to_user ON to_user.id = h.to_assignee_admin_user_id ` const configChangeSelect = ` SELECT req.id::text, req.target_area, req.change_type, req.target_type, req.target_id, req.status, req.requester_admin_user_id::text, COALESCE(requester.email, ''), COALESCE(req.reviewer_admin_user_id::text, ''), COALESCE(reviewer.email, ''), req.proposed_payload, req.reason, req.decision_note, req.evidence_reference, req.decided_at, req.implemented_at, req.created_at, req.updated_at FROM high_risk_config_change_requests req JOIN users requester ON requester.id = req.requester_admin_user_id LEFT JOIN users reviewer ON reviewer.id = req.reviewer_admin_user_id ` const impersonationSelect = ` SELECT s.id::text, s.admin_user_id::text, COALESCE(admin.email, ''), s.customer_user_id::text, COALESCE(customer.email, ''), s.status, s.reason, s.ticket_reference, s.allowed_actions, s.started_at, s.expires_at, s.ended_at, s.remote_ip, s.user_agent, s.created_at, s.updated_at FROM support_impersonation_sessions s JOIN users admin ON admin.id = s.admin_user_id JOIN users customer ON customer.id = s.customer_user_id ` const actionSelect = ` SELECT ae.id::text, COALESCE(ae.actor_user_id::text, ''), COALESCE(u.email, ''), ae.event_type, ae.target_type, ae.target_id, ae.metadata, ae.remote_ip, ae.user_agent, COALESCE(ae.previous_hash, ''), COALESCE(ae.event_hash, ''), ae.created_at FROM audit_events ae LEFT JOIN users u ON u.id = ae.actor_user_id ` func findCase(ctx context.Context, tx pgx.Tx, id string) (Case, error) { item, err := scanCase(tx.QueryRow(ctx, caseSelect+` WHERE c.id = $1`, id)) if errors.Is(err, pgx.ErrNoRows) { return Case{}, domain.ErrNotFound } return item, err } func findNote(ctx context.Context, tx pgx.Tx, id string) (CaseNote, error) { return scanNote(tx.QueryRow(ctx, noteSelect+` WHERE n.id = $1`, id)) } func findAttachment(ctx context.Context, tx pgx.Tx, id string) (CaseAttachment, error) { return scanAttachment(tx.QueryRow(ctx, attachmentSelect+` WHERE a.id = $1`, id)) } func insertHistory(ctx context.Context, tx pgx.Tx, caseID, adminUserID, eventType, fromStatus, toStatus, fromAssignee, toAssignee string, metadata map[string]any) error { encoded := []byte(`{}`) if metadata != nil { var err error encoded, err = json.Marshal(metadata) if err != nil { return err } } _, err := tx.Exec(ctx, ` INSERT INTO backoffice_case_history ( case_id, actor_admin_user_id, event_type, from_status, to_status, from_assignee_admin_user_id, to_assignee_admin_user_id, metadata ) VALUES ($1, NULLIF($2, '')::uuid, $3, $4, $5, NULLIF($6, '')::uuid, NULLIF($7, '')::uuid, $8) `, caseID, adminUserID, eventType, fromStatus, toStatus, fromAssignee, toAssignee, encoded) return err } type scanner interface { Scan(dest ...any) error } func scanCase(row scanner) (Case, error) { var item Case var slaDueAt, resolvedAt, closedAt sql.NullTime err := row.Scan(&item.ID, &item.Category, &item.SourceType, &item.SourceID, &item.CustomerUserID, &item.CustomerEmail, &item.Title, &item.Description, &item.Priority, &item.Status, &item.OwnerTeam, &item.AssignedToAdminUserID, &item.AssignedAdminEmail, &slaDueAt, &resolvedAt, &closedAt, &item.CreatedByAdminUserID, &item.CreatedAt, &item.UpdatedAt) if slaDueAt.Valid { item.SLADueAt = &slaDueAt.Time } if resolvedAt.Valid { item.ResolvedAt = &resolvedAt.Time } if closedAt.Valid { item.ClosedAt = &closedAt.Time } return item, err } func scanNote(row scanner) (CaseNote, error) { var note CaseNote err := row.Scan(¬e.ID, ¬e.CaseID, ¬e.AdminUserID, ¬e.AdminEmail, ¬e.NoteType, ¬e.Body, ¬e.CreatedAt) return note, err } func scanAttachment(row scanner) (CaseAttachment, error) { var attachment CaseAttachment err := row.Scan(&attachment.ID, &attachment.CaseID, &attachment.AdminUserID, &attachment.AdminEmail, &attachment.FileName, &attachment.ContentType, &attachment.StorageReference, &attachment.ChecksumSHA256, &attachment.Classification, &attachment.CreatedAt) return attachment, err } func scanHistory(row scanner) (CaseHistoryEvent, error) { var event CaseHistoryEvent err := row.Scan(&event.ID, &event.CaseID, &event.ActorAdminUserID, &event.ActorEmail, &event.EventType, &event.FromStatus, &event.ToStatus, &event.FromAssigneeAdminID, &event.ToAssigneeAdminID, &event.FromAssigneeEmail, &event.ToAssigneeEmail, &event.Metadata, &event.CreatedAt) if len(event.Metadata) == 0 { event.Metadata = json.RawMessage(`{}`) } return event, err } func scanConfigChange(row scanner) (ConfigChangeRequest, error) { var req ConfigChangeRequest var decidedAt, implementedAt sql.NullTime err := row.Scan(&req.ID, &req.TargetArea, &req.ChangeType, &req.TargetType, &req.TargetID, &req.Status, &req.RequesterAdminUserID, &req.RequesterEmail, &req.ReviewerAdminUserID, &req.ReviewerEmail, &req.ProposedPayload, &req.Reason, &req.DecisionNote, &req.EvidenceReference, &decidedAt, &implementedAt, &req.CreatedAt, &req.UpdatedAt) if decidedAt.Valid { req.DecidedAt = &decidedAt.Time } if implementedAt.Valid { req.ImplementedAt = &implementedAt.Time } if len(req.ProposedPayload) == 0 { req.ProposedPayload = json.RawMessage(`{}`) } return req, err } func scanImpersonation(row scanner) (ImpersonationSession, error) { var session ImpersonationSession var endedAt sql.NullTime err := row.Scan(&session.ID, &session.AdminUserID, &session.AdminEmail, &session.CustomerUserID, &session.CustomerEmail, &session.Status, &session.Reason, &session.TicketReference, &session.AllowedActions, &session.StartedAt, &session.ExpiresAt, &endedAt, &session.RemoteIP, &session.UserAgent, &session.CreatedAt, &session.UpdatedAt) if endedAt.Valid { session.EndedAt = &endedAt.Time } return session, err } func scanAdminAction(row scanner) (AdminAction, error) { var action AdminAction err := row.Scan(&action.ID, &action.ActorUserID, &action.ActorEmail, &action.EventType, &action.TargetType, &action.TargetID, &action.Metadata, &action.RemoteIP, &action.UserAgent, &action.PreviousHash, &action.EventHash, &action.CreatedAt) if len(action.Metadata) == 0 { action.Metadata = json.RawMessage(`{}`) } return action, err } func normalizeCaseParams(params *CaseParams) error { params.Category = strings.ToLower(strings.TrimSpace(params.Category)) params.SourceType = strings.TrimSpace(params.SourceType) params.SourceID = strings.TrimSpace(params.SourceID) params.CustomerUserID = strings.TrimSpace(params.CustomerUserID) params.Title = strings.TrimSpace(params.Title) params.Description = strings.TrimSpace(params.Description) params.Priority = strings.ToLower(strings.TrimSpace(params.Priority)) params.Status = strings.ToLower(strings.TrimSpace(params.Status)) params.OwnerTeam = strings.ToLower(strings.TrimSpace(params.OwnerTeam)) params.AssignedTo = strings.TrimSpace(params.AssignedTo) if params.Priority == "" { params.Priority = "medium" } if params.Status == "" { params.Status = StatusOpen } if params.OwnerTeam == "" { params.OwnerTeam = "operations" } if err := validateCategory(params.Category); err != nil { return err } if err := validatePriority(params.Priority); err != nil { return err } if err := validateCaseStatus(params.Status); err != nil { return err } if err := validateOwnerTeam(params.OwnerTeam); err != nil { return err } if params.CustomerUserID != "" { if err := domain.ValidateUUID("customer_user_id", params.CustomerUserID); err != nil { return err } } if params.AssignedTo != "" { if err := domain.ValidateUUID("assigned_to_admin_user_id", params.AssignedTo); err != nil { return err } } if params.Title == "" { return fmt.Errorf("%w: title is required", domain.ErrValidation) } if len(params.Title) > 200 || len(params.Description) > 5000 { return fmt.Errorf("%w: case text fields are too long", domain.ErrValidation) } return nil } func normalizeCaseUpdateParams(params *CaseUpdateParams) error { params.ID = strings.TrimSpace(params.ID) params.Status = strings.ToLower(strings.TrimSpace(params.Status)) params.Priority = strings.ToLower(strings.TrimSpace(params.Priority)) params.OwnerTeam = strings.ToLower(strings.TrimSpace(params.OwnerTeam)) params.AssignedTo = strings.TrimSpace(params.AssignedTo) params.Description = strings.TrimSpace(params.Description) if err := domain.ValidateUUID("id", params.ID); err != nil { return err } if params.Status != "" { if err := validateCaseStatus(params.Status); err != nil { return err } } if params.Priority != "" { if err := validatePriority(params.Priority); err != nil { return err } } if params.OwnerTeam != "" { if err := validateOwnerTeam(params.OwnerTeam); err != nil { return err } } if params.AssignedTo != "" { if err := domain.ValidateUUID("assigned_to_admin_user_id", params.AssignedTo); err != nil { return err } } if len(params.Description) > 5000 { return fmt.Errorf("%w: description is too long", domain.ErrValidation) } return nil } func normalizeNoteParams(params *NoteParams) error { params.CaseID = strings.TrimSpace(params.CaseID) params.AdminUserID = strings.TrimSpace(params.AdminUserID) params.NoteType = strings.ToLower(strings.TrimSpace(params.NoteType)) params.Body = strings.TrimSpace(params.Body) if err := domain.ValidateUUID("case_id", params.CaseID); err != nil { return err } if params.NoteType == "" { params.NoteType = "internal" } if !validNoteType(params.NoteType) { return fmt.Errorf("%w: invalid note_type", domain.ErrValidation) } if params.Body == "" { return fmt.Errorf("%w: note body is required", domain.ErrValidation) } if len(params.Body) > 10000 { return fmt.Errorf("%w: note body is too long", domain.ErrValidation) } return nil } func normalizeAttachmentParams(params *AttachmentParams) error { params.CaseID = strings.TrimSpace(params.CaseID) params.AdminUserID = strings.TrimSpace(params.AdminUserID) params.FileName = strings.TrimSpace(params.FileName) params.ContentType = strings.TrimSpace(params.ContentType) params.StorageReference = strings.TrimSpace(params.StorageReference) params.ChecksumSHA256 = strings.TrimSpace(params.ChecksumSHA256) params.Classification = strings.ToLower(strings.TrimSpace(params.Classification)) if err := domain.ValidateUUID("case_id", params.CaseID); err != nil { return err } if params.FileName == "" || params.StorageReference == "" { return fmt.Errorf("%w: file_name and storage_reference are required", domain.ErrValidation) } if params.ContentType == "" { params.ContentType = "application/octet-stream" } if params.Classification == "" { params.Classification = "confidential" } if params.Classification != "internal" && params.Classification != "confidential" && params.Classification != "restricted" { return fmt.Errorf("%w: invalid attachment classification", domain.ErrValidation) } return nil } func normalizeConfigChangeParams(params *ConfigChangeParams) error { params.TargetArea = strings.ToLower(strings.TrimSpace(params.TargetArea)) params.ChangeType = strings.TrimSpace(params.ChangeType) params.TargetType = strings.TrimSpace(params.TargetType) params.TargetID = strings.TrimSpace(params.TargetID) params.Reason = strings.TrimSpace(params.Reason) if err := validateTargetArea(params.TargetArea); err != nil { return err } if params.ChangeType == "" || params.Reason == "" { return fmt.Errorf("%w: change_type and reason are required", domain.ErrValidation) } if params.ProposedPayload == nil || len(params.ProposedPayload) == 0 { params.ProposedPayload = json.RawMessage(`{}`) } if !json.Valid(params.ProposedPayload) { return fmt.Errorf("%w: proposed_payload must be valid JSON", domain.ErrValidation) } return nil } func normalizeConfigDecisionParams(params *ConfigDecisionParams) error { params.ID = strings.TrimSpace(params.ID) params.AdminUserID = strings.TrimSpace(params.AdminUserID) params.Action = strings.ToLower(strings.TrimSpace(params.Action)) params.DecisionNote = strings.TrimSpace(params.DecisionNote) params.EvidenceReference = strings.TrimSpace(params.EvidenceReference) if err := domain.ValidateUUID("id", params.ID); err != nil { return err } if err := domain.ValidateUUID("admin_user_id", params.AdminUserID); err != nil { return err } switch params.Action { case "approve", "reject", "cancel", "implement": default: return fmt.Errorf("%w: invalid config change action", domain.ErrValidation) } if params.Action == "reject" && params.DecisionNote == "" { return fmt.Errorf("%w: decision_note is required when rejecting", domain.ErrValidation) } return nil } func normalizeImpersonationParams(params *ImpersonationParams) error { params.AdminUserID = strings.TrimSpace(params.AdminUserID) params.CustomerUserID = strings.TrimSpace(params.CustomerUserID) params.Reason = strings.TrimSpace(params.Reason) params.TicketReference = strings.TrimSpace(params.TicketReference) if err := domain.ValidateUUID("admin_user_id", params.AdminUserID); err != nil { return err } if err := domain.ValidateUUID("customer_user_id", params.CustomerUserID); err != nil { return err } if params.AdminUserID == params.CustomerUserID { return fmt.Errorf("%w: admin cannot impersonate themselves", domain.ErrValidation) } if params.Reason == "" || params.TicketReference == "" { return fmt.Errorf("%w: reason and ticket_reference are required", domain.ErrValidation) } now := time.Now().UTC() if params.ExpiresAt.IsZero() { params.ExpiresAt = now.Add(30 * time.Minute) } if !params.ExpiresAt.After(now) || params.ExpiresAt.After(now.Add(2*time.Hour)) { return fmt.Errorf("%w: impersonation expires_at must be within 2 hours", domain.ErrValidation) } params.AllowedActions = normalizeAllowedActions(params.AllowedActions) return nil } func validateCategory(value string) error { switch value { case "reconciliation_break", "payment_review", "fraud_review", "provider_incident", "customer_complaint", "config_change", "security_event", "other": return nil default: return fmt.Errorf("%w: invalid case category", domain.ErrValidation) } } func validatePriority(value string) error { switch value { case "low", "medium", "high", "critical": return nil default: return fmt.Errorf("%w: invalid priority", domain.ErrValidation) } } func validateCaseStatus(value string) error { switch value { case StatusOpen, StatusAssigned, StatusInProgress, StatusWaitingOnCustomer, StatusWaitingOnProvider, StatusResolved, StatusClosed, StatusCanceled: return nil default: return fmt.Errorf("%w: invalid case status", domain.ErrValidation) } } func validateOwnerTeam(value string) error { switch value { case "operations", "finance", "compliance", "risk", "support", "security", "engineering": return nil default: return fmt.Errorf("%w: invalid owner_team", domain.ErrValidation) } } func validateTargetArea(value string) error { switch value { case "risk_limits", "routing_codes", "appsec", "cards_pci", "crypto_custody", "privacy", "rate_limit", "provider_config", "other": return nil default: return fmt.Errorf("%w: invalid target_area", domain.ErrValidation) } } func validateApprovalStatus(value string) error { switch value { case ApprovalPending, ApprovalApproved, ApprovalRejected, ApprovalCanceled, ApprovalImplemented: return nil default: return fmt.Errorf("%w: invalid config change status", domain.ErrValidation) } } func validNoteType(value string) bool { switch value { case "internal", "customer_contact", "provider_update", "decision", "system": return true default: return false } } func validImpersonationStatus(value string) bool { switch value { case "active", "ended", "expired", "canceled": return true default: return false } } func normalizeAllowedActions(values []string) []string { allowed := map[string]bool{ "view_profile": true, "view_accounts": true, "view_cases": true, "view_transactions": true, "contact_customer": true, } out := []string{} seen := map[string]bool{} for _, value := range values { value = strings.ToLower(strings.TrimSpace(value)) if allowed[value] && !seen[value] { out = append(out, value) seen[value] = true } } if len(out) == 0 { out = []string{"view_profile", "view_accounts", "view_cases"} } return out } func normalizeDashboardRole(role string) string { role = strings.ToLower(strings.TrimSpace(role)) switch role { case "compliance", "finance", "support", "operations": return role default: return "operations" } } func roleCategory(role string) string { switch role { case "compliance": return "fraud_review" case "finance": return "reconciliation_break" case "support": return "customer_complaint" default: return "all" } } func roleOwnerTeam(role string) string { switch role { case "compliance": return "compliance" case "finance": return "finance" case "support": return "support" default: return "operations" } } func normalizeLimit(limit int) int { if limit <= 0 || limit > 100 { return 50 } return limit } func normalizeExportLimit(limit int) int { if limit <= 0 || limit > 1000 { return 250 } return limit } func isForeignKeyViolation(err error) bool { var pgErr *pgconn.PgError return errors.As(err, &pgErr) && pgErr.Code == "23503" } func isUniqueViolation(err error) bool { var pgErr *pgconn.PgError return errors.As(err, &pgErr) && pgErr.Code == "23505" }