gs-maintainer/adr/2025-03-23-database-design.md.md

ADR 2025-03-23: Database Design

Table of Contents

• Context
• Decision
  • Table: Registered Projects
  • Table: Scala Version
  • Table: SBT Version
  • Table: Project Dependency
  • Table: Version Parser
  • Table: Project Dependency Version Parser
  • Table: Dependency Version
• Out of Scope

Context

The gs-maintainer project is intended to manage dependency version updates, Scala language version updates, and SBT version updates for Scala projects. It is also intended to help provide tracking information over time along with dashboards to help inform and expedite development.

Decision

Establish a small core database that can be expanded upon over time. This database must support:

• The concept of registering a project for maintenance.
• The concept of tracking critical tool versions over time.
• The concept of tracking a dependency over time.

This will be accomplished using 7 tables:

• Table: Registered Projects
• Table: Scala Version
• Table: SBT Version
• Table: Project Dependency
• Table: Version Parser
• Table: Project Dependency Version Parser
• Table: Dependency Version

Table: Registered Project

CREATE TABLE registered_project(
    project_id UUID NOT NULL PRIMARY KEY,
    group TEXT NOT NULL,
    name TEXT NOT NULL,
    registered_at TIMESTAMPTZ NOT NULL,
    git_url TIMESTAMPTZ NOT NULL
);

CREATE UNIQUE INDEX idx_registered_project_group_name
ON registered_project (group, name);

Table: Scala Version

CREATE TABLE scala_version(
    version TEXT NOT NULL PRIMARY KEY,
    major INT NOT NULL,
    minor INT NOT NULL,
    patch INT NOT NULL,
    added_at TIMESTAMPTZ NOT NULL
);

CREATE UNIQUE INDEX idx_scala_version_major_minor_patch
ON scala_version (major, minor, patch);

Table: SBT Version

CREATE TABLE sbt_version(
    version TEXT NOT NULL PRIMARY KEY,
    major INT NOT NULL,
    minor INT NOT NULL,
    patch INT NOT NULL,
    added_at TIMESTAMPTZ NOT NULL
);

CREATE UNIQUE INDEX idx_sbt_version_major_minor_patch
ON sbt_version (major, minor, patch);

Table: Project Dependency

CREATE TABLE project_dependency(
    dependency_id UUID NOT NULL PRIMARY KEY,
    group TEXT NOT NULL,
    name TEXT NOT NULL,
    added_at TIMESTAMPTZ NOT NULL
);

CREATE UNIQUE INDEX idx_project_dependency_group_name
ON project_dependency (group, name);

Table: Version Parser

CREATE TABLE version_parser(
    parser_id UUID NOT NULL PRIMARY KEY,
    parser_name TEXT NOT NULL UNIQUE,
    implementation TEXT NOT NULL,
    added_at TIMESTAMPTZ NOT NULL
);

Table: Project Dependency Version Parser

CREATE TABLE project_dependency_version_parser(
    dependency_id UUID NOT NULL REFERENCES project_dependency,
    parser_id UUID NOT NULL REFERENCES version_parser,
    PRIMARY KEY (dependency_id, parser_id)
);

Table: Dependency Version

CREATE TABLE dependency_version(
    dependency_id UUID NOT NULL REFERENCES project_dependency,
    version TEXT NOT NULL,
    first INT NULL,
    second INT NULL,
    third INT NULL,
    fourth INT NULL,
    added_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (dependency_id, version)
);

CREATE INDEX idx_dependency_version_order 
ON dependency_version (first, second, third, fourth, added_at)
NULLS LAST;

Out of Scope

The following items are considered out of scope:

• Project level upgrade policies.
• Per-dependency upgrade policies.