# ADR 2025-03-23: Database Design

## Table of Contents

- [Context](#context)
- [Decision](#decision)
    - [Table: Registered Projects](#table-registered-project)
    - [Table: Scala Version](#table-scala-version)
    - [Table: SBT Version](#table-sbt-version)
    - [Table: Project Dependency](#table-project-dependency)
    - [Table: Version Parser](#table-version-parser)
    - [Table: Project Dependency Version Parser](#table-project-dependency-version-parser)
    - [Table: Dependency Version](#table-dependency-version)
- [Out of Scope](#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-registered-project)
- [Table: Scala Version](#table-scala-version)
- [Table: SBT Version](#table-sbt-version)
- [Table: Project Dependency](#table-project-dependency)
- [Table: Version Parser](#table-version-parser)
- [Table: Project Dependency Version Parser](#table-project-dependency-version-parser)
- [Table: Dependency Version](#table-dependency-version)

### Table: Registered Project

```sql
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 TEXT NOT NULL
);
```

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

### Table: Scala Version

```sql
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
);
```

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

### Table: SBT Version

```sql
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
);
```

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

### Table: Project Dependency

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

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

### Table: Version Parser

```sql
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

```sql
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

```sql
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)
);
```

```sql
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.