tiny-forge/internal/store/webhook_deliveries.go

package store

import (
	"fmt"
)

// WebhookDelivery is one persisted inbound webhook hit. Recorded after the
// handler decides what to do so the row reflects the final outcome.
type WebhookDelivery struct {
	ID             int64  `json:"id"`
	TargetType     string `json:"target_type"` // "trigger" today; legacy rows may carry "project" or "site"
	TargetID       string `json:"target_id"`
	TargetName     string `json:"target_name"`
	ReceivedAt     string `json:"received_at"`
	SourceIP       string `json:"source_ip"`
	SignatureState string `json:"signature_state"` // "valid" / "invalid" / "missing" / "unconfigured"
	StatusCode     int    `json:"status_code"`
	Outcome        string `json:"outcome"` // "deploy" / "skip" / "rejected" / etc.
	Detail         string `json:"detail"`
	BodySize       int    `json:"body_size"`
}

// InsertWebhookDelivery persists a single webhook delivery record. Best-effort
// — failures here must not block the actual webhook handler, so callers
// should log and continue rather than propagate.
func (s *Store) InsertWebhookDelivery(d WebhookDelivery) error {
	_, err := s.db.Exec(
		`INSERT INTO webhook_deliveries
		 (target_type, target_id, target_name, source_ip, signature_state,
		  status_code, outcome, detail, body_size)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		d.TargetType, d.TargetID, d.TargetName, d.SourceIP, d.SignatureState,
		d.StatusCode, d.Outcome, d.Detail, d.BodySize,
	)
	if err != nil {
		return fmt.Errorf("insert webhook delivery: %w", err)
	}
	return nil
}

// ListWebhookDeliveriesByTarget returns the most recent N deliveries for
// a specific target. Used by the trigger detail panel after the legacy
// project / site detail pages were removed.
func (s *Store) ListWebhookDeliveriesByTarget(targetType, targetID string, limit int) ([]WebhookDelivery, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.db.Query(
		`SELECT id, target_type, target_id, target_name, received_at, source_ip,
		        signature_state, status_code, outcome, detail, body_size
		 FROM webhook_deliveries
		 WHERE target_type = ? AND target_id = ?
		 ORDER BY id DESC
		 LIMIT ?`,
		targetType, targetID, limit,
	)
	if err != nil {
		return nil, fmt.Errorf("query webhook deliveries: %w", err)
	}
	defer rows.Close()

	out := []WebhookDelivery{}
	for rows.Next() {
		var d WebhookDelivery
		if err := rows.Scan(&d.ID, &d.TargetType, &d.TargetID, &d.TargetName, &d.ReceivedAt,
			&d.SourceIP, &d.SignatureState, &d.StatusCode, &d.Outcome, &d.Detail, &d.BodySize); err != nil {
			return nil, fmt.Errorf("scan webhook delivery: %w", err)
		}
		out = append(out, d)
	}
	return out, rows.Err()
}

// PruneWebhookDeliveriesBefore deletes rows older than the given timestamp.
// Returns the number of rows removed. Run on the same daily cron as the
// event log to keep the table from growing without bound.
func (s *Store) PruneWebhookDeliveriesBefore(beforeTS string) (int64, error) {
	res, err := s.db.Exec(`DELETE FROM webhook_deliveries WHERE received_at < ?`, beforeTS)
	if err != nil {
		return 0, fmt.Errorf("prune webhook deliveries: %w", err)
	}
	n, _ := res.RowsAffected()
	return n, nil
}