# Annotations Table Refactoring

## Overview

Refactor manifest annotations from individual columns (`title`, `description`, `source_url`, etc.) to a normalized key-value table. This enables flexible annotation storage without schema changes for new OCI annotations.

## Motivation

**Current Problems:**
- Each new annotation (e.g., `org.opencontainers.image.version`) requires schema change
- Many NULL columns in manifests table
- Rigid schema doesn't match OCI's flexible annotation model

**Benefits:**
- ✅ Add any annotation without code/schema changes
- ✅ Normalized database design
- ✅ Easy to query "all repos with annotation X"
- ✅ Simple queries (no joins needed for repository pages)

## Database Schema Changes

### 1. New Table: `repository_annotations`

```sql
CREATE TABLE IF NOT EXISTS repository_annotations (
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(did, repository, key),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_repository_annotations_did_repo ON repository_annotations(did, repository);
CREATE INDEX IF NOT EXISTS idx_repository_annotations_key ON repository_annotations(key);
```

**Key Design Decisions:**
- Primary key: `(did, repository, key)` - one value per annotation per repository
- No `manifest_id` foreign key - annotations are repository-level, not manifest-level
- `updated_at` - track when annotation was last updated (from most recent manifest)
- Stored at repository level because that's where they're displayed

### 2. Drop Columns from `manifests` Table

Remove these columns (migration will preserve data by copying to annotations table):
- `title`
- `description`
- `source_url`
- `documentation_url`
- `licenses`
- `icon_url`
- `readme_url`
- `version`

Keep only core manifest metadata:
- `id`, `did`, `repository`, `digest`
- `hold_endpoint`, `schema_version`, `media_type`
- `config_digest`, `config_size`
- `created_at`

## Migration Strategy

There is no need to migrate data to this new table via sql. on startup, backfill will re-populate the new table with existing annotations.

## Code Changes

### 1. Database Helper Functions

**New file: `pkg/appview/db/annotations.go`**

```go
package db

import (
    "database/sql"
    "time"
)

// GetRepositoryAnnotations retrieves all annotations for a repository
func GetRepositoryAnnotations(db *sql.DB, did, repository string) (map[string]string, error) {
    rows, err := db.Query(`
        SELECT key, value
        FROM repository_annotations
        WHERE did = ? AND repository = ?
    `, did, repository)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    annotations := make(map[string]string)
    for rows.Next() {
        var key, value string
        if err := rows.Scan(&key, &value); err != nil {
            return nil, err
        }
        annotations[key] = value
    }

    return annotations, rows.Err()
}

// UpsertRepositoryAnnotations replaces all annotations for a repository
// Only called when manifest has at least one non-empty annotation
func UpsertRepositoryAnnotations(db *sql.DB, did, repository string, annotations map[string]string) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Delete existing annotations
    _, err = tx.Exec(`
        DELETE FROM repository_annotations
        WHERE did = ? AND repository = ?
    `, did, repository)
    if err != nil {
        return err
    }

    // Insert new annotations
    stmt, err := tx.Prepare(`
        INSERT INTO repository_annotations (did, repository, key, value, updated_at)
        VALUES (?, ?, ?, ?, ?)
    `)
    if err != nil {
        return err
    }
    defer stmt.Close()

    now := time.Now()
    for key, value := range annotations {
        _, err = stmt.Exec(did, repository, key, value, now)
        if err != nil {
            return err
        }
    }

    return tx.Commit()
}

// DeleteRepositoryAnnotations removes all annotations for a repository
func DeleteRepositoryAnnotations(db *sql.DB, did, repository string) error {
    _, err := db.Exec(`
        DELETE FROM repository_annotations
        WHERE did = ? AND repository = ?
    `, did, repository)
    return err
}
```

### 2. Update Backfill Worker

**File: `pkg/appview/jetstream/backfill.go`**

In `processManifestRecord()` function, after extracting annotations:

```go
// Extract OCI annotations from manifest
var title, description, sourceURL, documentationURL, licenses, iconURL, readmeURL string
if manifestRecord.Annotations != nil {
    title = manifestRecord.Annotations["org.opencontainers.image.title"]
    description = manifestRecord.Annotations["org.opencontainers.image.description"]
    sourceURL = manifestRecord.Annotations["org.opencontainers.image.source"]
    documentationURL = manifestRecord.Annotations["org.opencontainers.image.documentation"]
    licenses = manifestRecord.Annotations["org.opencontainers.image.licenses"]
    iconURL = manifestRecord.Annotations["io.atcr.icon"]
    readmeURL = manifestRecord.Annotations["io.atcr.readme"]
}

// Prepare manifest for insertion (WITHOUT annotation fields)
manifest := &db.Manifest{
    DID:           did,
    Repository:    manifestRecord.Repository,
    Digest:        manifestRecord.Digest,
    MediaType:     manifestRecord.MediaType,
    SchemaVersion: manifestRecord.SchemaVersion,
    HoldEndpoint:  manifestRecord.HoldEndpoint,
    CreatedAt:     manifestRecord.CreatedAt,
    // NO annotation fields
}

// Set config fields only for image manifests (not manifest lists)
if !isManifestList && manifestRecord.Config != nil {
    manifest.ConfigDigest = manifestRecord.Config.Digest
    manifest.ConfigSize = manifestRecord.Config.Size
}

// Insert manifest
manifestID, err := db.InsertManifest(b.db, manifest)
if err != nil {
    return fmt.Errorf("failed to insert manifest: %w", err)
}

// Update repository annotations ONLY if manifest has at least one non-empty annotation
if manifestRecord.Annotations != nil {
    hasData := false
    for _, value := range manifestRecord.Annotations {
        if value != "" {
            hasData = true
            break
        }
    }

    if hasData {
        // Replace all annotations for this repository
        err = db.UpsertRepositoryAnnotations(b.db, did, manifestRecord.Repository, manifestRecord.Annotations)
        if err != nil {
            return fmt.Errorf("failed to upsert annotations: %w", err)
        }
    }
}
```

### 3. Update Jetstream Worker

**File: `pkg/appview/jetstream/worker.go`**

Same changes as backfill - in `processManifestCommit()` function:

```go
// Extract OCI annotations from manifest
var title, description, sourceURL, documentationURL, licenses, iconURL, readmeURL string
if manifestRecord.Annotations != nil {
    title = manifestRecord.Annotations["org.opencontainers.image.title"]
    description = manifestRecord.Annotations["org.opencontainers.image.description"]
    sourceURL = manifestRecord.Annotations["org.opencontainers.image.source"]
    documentationURL = manifestRecord.Annotations["org.opencontainers.image.documentation"]
    licenses = manifestRecord.Annotations["org.opencontainers.image.licenses"]
    iconURL = manifestRecord.Annotations["io.atcr.icon"]
    readmeURL = manifestRecord.Annotations["io.atcr.readme"]
}

// Prepare manifest for insertion (WITHOUT annotation fields)
manifest := &db.Manifest{
    DID:              commit.DID,
    Repository:       manifestRecord.Repository,
    Digest:           manifestRecord.Digest,
    MediaType:        manifestRecord.MediaType,
    SchemaVersion:    manifestRecord.SchemaVersion,
    HoldEndpoint:     manifestRecord.HoldEndpoint,
    CreatedAt:        manifestRecord.CreatedAt,
    // NO annotation fields
}

// Set config fields only for image manifests (not manifest lists)
if !isManifestList && manifestRecord.Config != nil {
    manifest.ConfigDigest = manifestRecord.Config.Digest
    manifest.ConfigSize = manifestRecord.Config.Size
}

// Insert manifest
manifestID, err := db.InsertManifest(w.db, manifest)
if err != nil {
    return fmt.Errorf("failed to insert manifest: %w", err)
}

// Update repository annotations ONLY if manifest has at least one non-empty annotation
if manifestRecord.Annotations != nil {
    hasData := false
    for _, value := range manifestRecord.Annotations {
        if value != "" {
            hasData = true
            break
        }
    }

    if hasData {
        // Replace all annotations for this repository
        err = db.UpsertRepositoryAnnotations(w.db, commit.DID, manifestRecord.Repository, manifestRecord.Annotations)
        if err != nil {
            return fmt.Errorf("failed to upsert annotations: %w", err)
        }
    }
}
```

### 4. Update Database Queries

**File: `pkg/appview/db/queries.go`**

Replace `GetRepositoryMetadata()` function:

```go
// GetRepositoryMetadata retrieves metadata for a repository from annotations table
func GetRepositoryMetadata(db *sql.DB, did string, repository string) (title, description, sourceURL, documentationURL, licenses, iconURL, readmeURL, version string, err error) {
    annotations, err := GetRepositoryAnnotations(db, did, repository)
    if err != nil {
        return "", "", "", "", "", "", "", "", err
    }

    title = annotations["org.opencontainers.image.title"]
    description = annotations["org.opencontainers.image.description"]
    sourceURL = annotations["org.opencontainers.image.source"]
    documentationURL = annotations["org.opencontainers.image.documentation"]
    licenses = annotations["org.opencontainers.image.licenses"]
    iconURL = annotations["io.atcr.icon"]
    readmeURL = annotations["io.atcr.readme"]
    version = annotations["org.opencontainers.image.version"]

    return title, description, sourceURL, documentationURL, licenses, iconURL, readmeURL, version, nil
}
```

Update `InsertManifest()` to remove annotation columns:

```go
func InsertManifest(db *sql.DB, manifest *Manifest) (int64, error) {
    _, err := db.Exec(`
        INSERT INTO manifests
        (did, repository, digest, hold_endpoint, schema_version, media_type,
         config_digest, config_size, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(did, repository, digest) DO UPDATE SET
            hold_endpoint = excluded.hold_endpoint,
            schema_version = excluded.schema_version,
            media_type = excluded.media_type,
            config_digest = excluded.config_digest,
            config_size = excluded.config_size
    `, manifest.DID, manifest.Repository, manifest.Digest, manifest.HoldEndpoint,
        manifest.SchemaVersion, manifest.MediaType, manifest.ConfigDigest,
        manifest.ConfigSize, manifest.CreatedAt)

    if err != nil {
        return 0, err
    }

    // Query for the ID (works for both insert and update)
    var id int64
    err = db.QueryRow(`
        SELECT id FROM manifests
        WHERE did = ? AND repository = ? AND digest = ?
    `, manifest.DID, manifest.Repository, manifest.Digest).Scan(&id)

    if err != nil {
        return 0, fmt.Errorf("failed to get manifest ID after upsert: %w", err)
    }

    return id, nil
}
```

Similar updates needed for:
- `GetUserRepositories()` - fetch annotations separately and populate Repository struct
- `GetRecentPushes()` - join with annotations or fetch separately
- `SearchPushes()` - can now search annotations table directly

### 5. Update Models

**File: `pkg/appview/db/models.go`**

Remove annotation fields from `Manifest` struct:

```go
type Manifest struct {
    ID                   int64
    DID                  string
    Repository           string
    Digest               string
    HoldEndpoint         string
    SchemaVersion        int
    MediaType            string
    ConfigDigest         string
    ConfigSize           int64
    CreatedAt            time.Time
    // Removed: Title, Description, SourceURL, DocumentationURL, Licenses, IconURL, ReadmeURL
}
```

Keep annotation fields on `Repository` struct (populated from annotations table):

```go
type Repository struct {
    Name             string
    TagCount         int
    ManifestCount    int
    LastPush         time.Time
    Tags             []Tag
    Manifests        []Manifest
    Title            string
    Description      string
    SourceURL        string
    DocumentationURL string
    Licenses         string
    IconURL          string
    ReadmeURL        string
    Version          string  // NEW
}
```

### 6. Update Schema.sql

**File: `pkg/appview/db/schema.sql`**

Add new table:

```sql
CREATE TABLE IF NOT EXISTS repository_annotations (
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(did, repository, key),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_repository_annotations_did_repo ON repository_annotations(did, repository);
CREATE INDEX IF NOT EXISTS idx_repository_annotations_key ON repository_annotations(key);
```

Update manifests table (remove annotation columns):

```sql
CREATE TABLE IF NOT EXISTS manifests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    did TEXT NOT NULL,
    repository TEXT NOT NULL,
    digest TEXT NOT NULL,
    hold_endpoint TEXT NOT NULL,
    schema_version INTEGER NOT NULL,
    media_type TEXT NOT NULL,
    config_digest TEXT,
    config_size INTEGER,
    created_at TIMESTAMP NOT NULL,
    UNIQUE(did, repository, digest),
    FOREIGN KEY(did) REFERENCES users(did) ON DELETE CASCADE
);
```

## Update Logic Summary

**Key Decision: Only update annotations when manifest has data**

```
For each manifest processed (backfill or jetstream):
  1. Parse manifest.Annotations map
  2. Check if ANY annotation has non-empty value
  3. IF hasData:
       DELETE all annotations for (did, repository)
       INSERT all annotations from manifest (including empty ones)
     ELSE:
       SKIP (don't touch existing annotations)
```

**Why this works:**
- Manifest lists have no annotations or all empty → skip, preserve existing
- Platform manifests have real data → replace everything
- Removing annotation from Dockerfile → it's gone (not in new INSERT)
- Can't accidentally clear data (need at least one non-empty value)

## UI/Template Changes

### Handler Updates

**File: `pkg/appview/handlers/repository.go`**

Update the handler to include version:

```go
// Fetch repository metadata from annotations
title, description, sourceURL, documentationURL, licenses, iconURL, readmeURL, version, err := db.GetRepositoryMetadata(h.DB, owner.DID, repository)
if err != nil {
    log.Printf("Failed to fetch repository metadata: %v", err)
    // Continue without metadata on error
} else {
    repo.Title = title
    repo.Description = description
    repo.SourceURL = sourceURL
    repo.DocumentationURL = documentationURL
    repo.Licenses = licenses
    repo.IconURL = iconURL
    repo.ReadmeURL = readmeURL
    repo.Version = version  // NEW
}
```

### Template Updates

**File: `pkg/appview/templates/pages/repository.html`**

Update the metadata section condition to include version:

```html
<!-- Metadata Section -->
{{ if or .Repository.Licenses .Repository.SourceURL .Repository.DocumentationURL .Repository.Version }}
<div class="repo-metadata">
    <!-- Version Badge (if present) -->
    {{ if .Repository.Version }}
        <span class="metadata-badge version-badge" title="Version">
            {{ .Repository.Version }}
        </span>
    {{ end }}

    <!-- License Badges -->
    {{ if .Repository.Licenses }}
        {{ range parseLicenses .Repository.Licenses }}
            {{ if .IsValid }}
                <a href="{{ .URL }}" target="_blank" rel="noopener noreferrer" class="metadata-badge license-badge" title="{{ .Name }}">
                    {{ .SPDXID }}
                </a>
            {{ else }}
                <span class="metadata-badge license-badge" title="Custom license: {{ .Name }}">
                    {{ .Name }}
                </span>
            {{ end }}
        {{ end }}
    {{ end }}

    <!-- Source Link -->
    {{ if .Repository.SourceURL }}
        <a href="{{ .Repository.SourceURL }}" target="_blank" class="metadata-link">
            Source
        </a>
    {{ end }}

    <!-- Documentation Link -->
    {{ if .Repository.DocumentationURL }}
        <a href="{{ .Repository.DocumentationURL }}" target="_blank" class="metadata-link">
            Documentation
        </a>
    {{ end }}
</div>
{{ end }}
```

### CSS Updates

**File: `pkg/appview/static/css/style.css`**

Add styling for version badge (different color from license badge):

```css
.version-badge {
    background: #0969da;  /* GitHub blue */
    color: white;
    padding: 0.25rem 0.5rem;
    border-radius: 0.25rem;
    font-size: 0.875rem;
    font-weight: 500;
    display: inline-block;
}
```

### Data Flow Summary

**Before refactor:**
```
DB columns → GetRepositoryMetadata() → Handler assigns to Repository struct → Template displays
```

**After refactor:**
```
annotations table → GetRepositoryAnnotations() → GetRepositoryMetadata() extracts known fields →
Handler assigns to Repository struct → Template displays (same as before)
```

**Key point:** Templates still access `.Repository.Title`, `.Repository.Version`, etc. - the source just changed from DB columns to annotations table. The abstraction layer hides this complexity.

## Benefits Recap

1. **Flexible**: Support any OCI annotation without code changes
2. **Clean**: No NULL columns in manifests table
3. **Simple queries**: `SELECT * FROM repository_annotations WHERE did=? AND repo=?`
4. **Safe updates**: Only update when manifest has data
5. **Natural deletion**: Remove annotation from Dockerfile → it's deleted on next push
6. **Extensible**: Future features (annotation search, filtering) are trivial

## Testing Checklist

After migration:
- [ ] Verify existing repositories show annotations correctly
- [ ] Push new manifest with annotations → updates correctly
- [ ] Push manifest list → doesn't clear annotations
- [ ] Remove annotation from Dockerfile and push → annotation deleted
- [ ] Backfill re-run → annotations repopulated correctly
- [ ] Search still works (if implemented)