Database Schema

The database currently has 47 migrations in pkg/storage/migration/sql/. The server binary auto-runs pending migrations on startup.

Timestamps: All timestamp columns use TIMESTAMPTZ (timestamp with time zone). Data is stored in UTC. Migration 035 standardized the 64 columns that previously used TIMESTAMP (without timezone). The database-level timezone is set to UTC via ALTER DATABASE ... SET timezone = 'UTC'.

Scope Annotations: All sqlc queries carry -- scope: annotations validated by CI (TestQueryScopeCoverage). These document the tenant isolation intent of each query (org, global, system, resource, join).

Tables

Core Tables

Job System Tables

Release & Version Tables

Rules & Alerts Tables

Notification System Tables

EOL Sync Tables

Feedback Table

Separate Schema

Key Enums

job_status:                   'pending', 'queued', 'in_progress', 'completed', 'failed'
source_type:                  'github_releases', 'helm_repository', 'endoflife_date', 'oci_registry'
parse_type:                   'yq', 'jq', 'regex', 'manual'
rule_type:                    'days_behind', 'majors_behind', 'minors_behind'
alert_severity:               'moderate', 'high', 'critical'
notification_channel_type:    'webhook', 'pagerduty', 'telegram', 'smtp'
notification_delivery_status: 'pending', 'in_progress', 'succeeded', 'failed', 'dead_letter'
org_role:                     'owner', 'admin', 'member'
feedback_category:            'bug_report', 'feature_request', 'general', 'ui_ux'
audit_entity_type:            'alert'
audit_action_type:            'created', 'escalated', 'acknowledge', 'unacknowledge', 'resolve'
audit_source:                 'api', 'ui', 'webhook', 'system'
eol_product_status:           'approved', 'denied', 'pending'

Warning: Only webhook has an actual sender implementation in pkg/notifications/webhook/. The pagerduty, telegram, and smtp channel types exist in the database enum but have no sender implementation yet. They are schema placeholders for future development.

Global Seed Data

On first startup, the system creates:

-- Default organization for global release data
INSERT INTO organizations (name, description, is_active)
VALUES ('Global', 'Default Organization that houses all default data', TRUE);

-- System users
INSERT INTO users (organization_id, email, first_name, is_system, metadata)
VALUES
    (1, 'system@planekeeper.com', 'Global System', TRUE, '{}'),
    (1, 'agent@planekeeper.com', 'Global Agent', TRUE, '{}');

-- Service registry
INSERT INTO services (id, service_name) OVERRIDING SYSTEM VALUE VALUES
    (1, 'server'),
    (2, 'serveragent'),
    (3, 'taskengine'),
    (4, 'eolsync'),
    (5, 'clientui'),
    (6, 'internalui'),
    (7, 'notifier'),
    (8, 'clientagent');

Pre-Seeded Global Jobs (Migration 034)

Migration 034 seeds ~173 global gather/helm sync jobs covering common infrastructure software. These provide immediate upstream release data for new organizations.

All seeded jobs use organization_id = 0 (global scope), have staggered cron schedules across 1:00-7:38 AM UTC, and carry labels for categorization (e.g., {"category": "kubernetes", "ecosystem": "cncf"}).

The migration also:

• Adds labels JSONB NOT NULL DEFAULT '{}' columns to gather_jobs and helm_sync_jobs
• Migrates existing artifact names to unified host/path format (github.com/owner/repo, endoflife.date/product)
• Creates partial unique indexes per source type for global job deduplication
• Curates EOL products from ~431 to ~65 active products

Upstream Release Metadata

The upstream_releases.metadata JSONB column stores source-specific metadata per release:

Gather Job Sync State

The gather_jobs.sync_state JSONB column (added by migration 038) tracks incremental sync progress for GitHub gather jobs:

The dispatcher uses this state to inject an _incremental_since hint into the agent’s source config, allowing the GitHub gatherer to stop pagination early when it reaches already-known releases. Sync state is reset when a gather job is updated or its releases are cleared.

Source Types (Gatherers)

The agent supports four data source types for upstream releases:

Version Extraction Methods

The agent supports three parsing strategies for scrape jobs, plus a manual entry mode:

Manual scrape jobs use placeholder values (repository_url="manual://", target_file="-", parse_expression="-") and are never assigned to agents. Versions are set via the POST /scrape-jobs/{id}/set-version API endpoint.

Rule Types

All rules support the stable_only parameter to ignore prereleases.