at-container-registry/pkg/appview/db/export.go

package db

import (
	"database/sql"
	"fmt"
	"time"
)

// UserDataExport represents the GDPR-compliant data export for a user
// Contains only data we originate, not cached PDS data
type UserDataExport struct {
	ExportedAt      time.Time             `json:"exported_at"`
	ExportVersion   string                `json:"export_version"`
	DID             string                `json:"did"`
	Devices         []DeviceExport        `json:"devices"`
	OAuthSessions   []OAuthSessionExport  `json:"oauth_sessions"`
	UISessions      []UISessionExport     `json:"ui_sessions"`
	HoldMemberships HoldMembershipsExport `json:"hold_memberships"`
	CachedDataNote  CachedDataNote        `json:"cached_data_note"`
}

// DeviceExport is a sanitized device record (no secret hash)
type DeviceExport struct {
	ID        string     `json:"id"`
	Name      string     `json:"name"`
	IPAddress string     `json:"ip_address"`
	Location  string     `json:"location,omitempty"`
	UserAgent string     `json:"user_agent"`
	CreatedAt time.Time  `json:"created_at"`
	LastUsed  *time.Time `json:"last_used,omitempty"`
}

// OAuthSessionExport is a sanitized OAuth session record (no tokens)
type OAuthSessionExport struct {
	SessionID string    `json:"session_id"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

// UISessionExport is a sanitized UI session record
type UISessionExport struct {
	ID        string    `json:"id"`
	ExpiresAt time.Time `json:"expires_at"`
	CreatedAt time.Time `json:"created_at"`
}

// HoldMembershipsExport contains hold approval and denial records
type HoldMembershipsExport struct {
	Approvals []HoldApprovalExport `json:"approvals"`
	Denials   []HoldDenialExport   `json:"denials"`
}

// HoldApprovalExport represents a hold crew approval
type HoldApprovalExport struct {
	HoldDID    string    `json:"hold_did"`
	ApprovedAt time.Time `json:"approved_at"`
	ExpiresAt  time.Time `json:"expires_at"`
}

// HoldDenialExport represents a hold crew denial (rate limiting)
type HoldDenialExport struct {
	HoldDID      string    `json:"hold_did"`
	DenialCount  int       `json:"denial_count"`
	NextRetryAt  time.Time `json:"next_retry_at"`
	LastDeniedAt time.Time `json:"last_denied_at"`
}

// CachedDataNote explains what cached data exists and how to access it
type CachedDataNote struct {
	Message            string   `json:"message"`
	DeletionNotice     string   `json:"deletion_notice"`
	YourPDSCollections []string `json:"your_pds_collections"`
	HowToAccess        string   `json:"how_to_access"`
}

// ExportUserData gathers all user data for GDPR export
// Only includes data we originate, not cached PDS data
func ExportUserData(db DBTX, did string) (*UserDataExport, error) {
	export := &UserDataExport{
		ExportedAt:    time.Now().UTC(),
		ExportVersion: "1.0",
		DID:           did,
	}

	// Get devices (sanitized - no secret hash)
	devices, err := getDevicesForExport(db, did)
	if err != nil {
		return nil, fmt.Errorf("failed to get devices: %w", err)
	}
	export.Devices = devices

	// Get OAuth sessions (sanitized - no tokens)
	oauthSessions, err := getOAuthSessionsForExport(db, did)
	if err != nil {
		return nil, fmt.Errorf("failed to get OAuth sessions: %w", err)
	}
	export.OAuthSessions = oauthSessions

	// Get UI sessions
	uiSessions, err := getUISessionsForExport(db, did)
	if err != nil {
		return nil, fmt.Errorf("failed to get UI sessions: %w", err)
	}
	export.UISessions = uiSessions

	// Get hold memberships (approvals and denials)
	memberships, err := getHoldMembershipsForExport(db, did)
	if err != nil {
		return nil, fmt.Errorf("failed to get hold memberships: %w", err)
	}
	export.HoldMemberships = memberships

	// Add cached data note
	export.CachedDataNote = CachedDataNote{
		Message:        "We cache data from your PDS for performance. This cached data is NOT included in this export as it is under your direct control on your PDS.",
		DeletionNotice: "If you delete your account, ALL data including cached data will be permanently removed from our servers.",
		YourPDSCollections: []string{
			"io.atcr.manifest - Your container image manifests",
			"io.atcr.tag - Your image tags",
			"io.atcr.sailor.profile - Your profile preferences",
			"io.atcr.sailor.star - Your starred repositories",
			"io.atcr.repo.page - Your repository pages (description, avatar)",
		},
		HowToAccess: "Use your PDS provider's tools or ATProto client libraries to export this data directly.",
	}

	return export, nil
}

// getDevicesForExport retrieves sanitized device records
func getDevicesForExport(db DBTX, did string) ([]DeviceExport, error) {
	rows, err := db.Query(`
		SELECT id, name, ip_address, location, user_agent, created_at, last_used
		FROM devices
		WHERE did = ?
		ORDER BY created_at DESC
	`, did)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var devices []DeviceExport
	for rows.Next() {
		var d DeviceExport
		var lastUsed sql.NullTime
		var location sql.NullString

		err := rows.Scan(&d.ID, &d.Name, &d.IPAddress, &location, &d.UserAgent, &d.CreatedAt, &lastUsed)
		if err != nil {
			return nil, err
		}

		if lastUsed.Valid {
			d.LastUsed = &lastUsed.Time
		}
		if location.Valid {
			d.Location = location.String
		}

		devices = append(devices, d)
	}

	if devices == nil {
		devices = []DeviceExport{}
	}

	return devices, rows.Err()
}

// getOAuthSessionsForExport retrieves sanitized OAuth session records
func getOAuthSessionsForExport(db DBTX, did string) ([]OAuthSessionExport, error) {
	rows, err := db.Query(`
		SELECT session_id, created_at, updated_at
		FROM oauth_sessions
		WHERE account_did = ?
		ORDER BY created_at DESC
	`, did)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var sessions []OAuthSessionExport
	for rows.Next() {
		var s OAuthSessionExport
		err := rows.Scan(&s.SessionID, &s.CreatedAt, &s.UpdatedAt)
		if err != nil {
			return nil, err
		}
		sessions = append(sessions, s)
	}

	if sessions == nil {
		sessions = []OAuthSessionExport{}
	}

	return sessions, rows.Err()
}

// getUISessionsForExport retrieves sanitized UI session records
func getUISessionsForExport(db DBTX, did string) ([]UISessionExport, error) {
	rows, err := db.Query(`
		SELECT id, expires_at, created_at
		FROM ui_sessions
		WHERE did = ?
		ORDER BY created_at DESC
	`, did)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var sessions []UISessionExport
	for rows.Next() {
		var s UISessionExport
		err := rows.Scan(&s.ID, &s.ExpiresAt, &s.CreatedAt)
		if err != nil {
			return nil, err
		}
		sessions = append(sessions, s)
	}

	if sessions == nil {
		sessions = []UISessionExport{}
	}

	return sessions, rows.Err()
}

// getHoldMembershipsForExport retrieves hold approval and denial records
func getHoldMembershipsForExport(db DBTX, did string) (HoldMembershipsExport, error) {
	memberships := HoldMembershipsExport{
		Approvals: []HoldApprovalExport{},
		Denials:   []HoldDenialExport{},
	}

	// Get approvals
	approvalRows, err := db.Query(`
		SELECT hold_did, approved_at, expires_at
		FROM hold_crew_approvals
		WHERE user_did = ?
		ORDER BY approved_at DESC
	`, did)
	if err != nil {
		return memberships, err
	}
	defer approvalRows.Close()

	for approvalRows.Next() {
		var a HoldApprovalExport
		err := approvalRows.Scan(&a.HoldDID, &a.ApprovedAt, &a.ExpiresAt)
		if err != nil {
			return memberships, err
		}
		memberships.Approvals = append(memberships.Approvals, a)
	}
	if err := approvalRows.Err(); err != nil {
		return memberships, err
	}

	// Get denials
	denialRows, err := db.Query(`
		SELECT hold_did, denial_count, next_retry_at, last_denied_at
		FROM hold_crew_denials
		WHERE user_did = ?
		ORDER BY last_denied_at DESC
	`, did)
	if err != nil {
		return memberships, err
	}
	defer denialRows.Close()

	for denialRows.Next() {
		var d HoldDenialExport
		err := denialRows.Scan(&d.HoldDID, &d.DenialCount, &d.NextRetryAt, &d.LastDeniedAt)
		if err != nil {
			return memberships, err
		}
		memberships.Denials = append(memberships.Denials, d)
	}

	return memberships, denialRows.Err()
}