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#

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

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:

// 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:

// 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:

// 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:

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:

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):

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:

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):

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:

// 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:

<!-- 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):

.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)