Usage and database design blueprint

Open
#2 10 comments 0 reactions 1 assignee View on GitHub

@pebeto is already working on this.

Since Aug 18, 2025.

Assessment

This issue has not been assessed yet.

Description

documentation help wanted

Database blueprint

erDiagram
    user ||--o{ user_permission : "is granted"
    project ||--o{ user_permission : grants
    project ||--o{ experiment : contains
    project ||--o{ model : registers
    project ||--o{ project_tag : "tagged by"
    experiment ||--o{ iteration : contains
    experiment ||--o{ resource : stores
    experiment ||--o{ experiment_tag : "tagged by"
    iteration |o--o{ iteration : "parent of"
    iteration ||--o{ parameter : records
    iteration ||--o{ metric : records
    iteration ||--o{ iteration_tag : "tagged by"
    tag ||--o{ project_tag : labels
    tag ||--o{ experiment_tag : labels
    tag ||--o{ iteration_tag : labels
    model ||--o{ model_version : versions
    iteration ||--o{ model_version : "source of"
    resource |o--o{ model_version : "artifact of"

    user {
        VARCHAR id PK
        TEXT first_name
        TEXT last_name
        TEXT username UK
        TEXT password "bcrypt hash"
        TEXT created_date
        BIGINT is_admin "0 or 1"
    }
    user_permission {
        VARCHAR id PK
        VARCHAR user_id FK "unique with project_id"
        VARCHAR project_id FK
        BIGINT create_permission "default 0"
        BIGINT read_permission "default 1"
        BIGINT update_permission "default 0"
        BIGINT delete_permission "default 0"
    }
    project {
        VARCHAR id PK
        TEXT name
        TEXT description
        TEXT created_date
    }
    experiment {
        VARCHAR id PK
        VARCHAR project_id FK
        BIGINT status_id "1 to 3"
        TEXT name
        TEXT description
        TEXT created_date
        TEXT end_date
    }
    iteration {
        VARCHAR id PK
        VARCHAR experiment_id FK
        VARCHAR parent_iteration_id FK "nullable"
        BIGINT status_id "1 to 4, default 1"
        TEXT notes
        TEXT error_message
        TEXT created_date
        TEXT end_date
        TEXT julia_version "snapshot"
        TEXT git_sha "snapshot"
        BIGINT git_dirty "snapshot"
        TEXT entrypoint "snapshot"
        TEXT project_toml "snapshot"
        TEXT manifest_toml "snapshot"
    }
    parameter {
        VARCHAR id PK
        VARCHAR iteration_id FK
        TEXT key
        TEXT value
    }
    metric {
        VARCHAR id PK
        VARCHAR iteration_id FK
        TEXT key
        DOUBLE value
        BIGINT step "default 0"
        TEXT recorded_at
    }
    resource {
        VARCHAR id PK
        VARCHAR experiment_id FK
        TEXT name
        TEXT description
        BLOB data "inline backend only"
        TEXT backend "inline, filesystem or s3"
        TEXT uri "file:// or s3://"
        BIGINT size_bytes
        TEXT content_hash "SHA-256"
        TEXT created_date
        TEXT updated_date
    }
    tag {
        VARCHAR id PK
        TEXT value UK
    }
    project_tag {
        VARCHAR id PK
        VARCHAR project_id FK "unique with tag_id"
        VARCHAR tag_id FK
    }
    experiment_tag {
        VARCHAR id PK
        VARCHAR experiment_id FK "unique with tag_id"
        VARCHAR tag_id FK
    }
    iteration_tag {
        VARCHAR id PK
        VARCHAR iteration_id FK "unique with tag_id"
        VARCHAR tag_id FK
    }
    model {
        VARCHAR id PK
        VARCHAR project_id FK
        TEXT name "unique per project"
        TEXT description
        TEXT created_date
        TEXT updated_date
    }
    model_version {
        VARCHAR id PK
        VARCHAR model_id FK "unique with version"
        BIGINT version "1, 2, 3... per model"
        VARCHAR iteration_id FK
        VARCHAR resource_id FK "nullable"
        BIGINT stage_id "1 to 4"
        TEXT description
        TEXT created_date
        TEXT updated_date
    }
Conventions
  • Every id is a UUID string generated by DuckDB's uuid().
  • Dates are ISO 8601 text. An empty end_date or updated_date means it hasn't been set.
  • Flags (is_admin, git_dirty, and the four permission columns) are BIGINT 0 or 1.
  • DuckDB has no triggers and no ON DELETE actions, so cascades, model name uniqueness, and the default-user rules live in the service layer (src/services/).
Enumerations
Column Values
experiment.status_id 1 IN_PROGRESS, 2 STOPPED, 3 FINISHED
iteration.status_id 1 RUNNING (on creation), 2 SUCCEEDED, 3 FAILED, 4 KILLED
model_version.stage_id 1 NO_STAGE (on registration), 2 STAGING, 3 PRODUCTION, 4 ARCHIVED
Rules enforced by the services
  • Access. Only administrators create, update, or delete projects and manage other users, and they skip permission checks. A regular user works below a project through their user_permission row: create, read, update, and delete. Nothing below a project can be granted on its own. The seeded default administrator can't be deleted.
  • Iterations. Once an iteration has an end_date it is locked: no updates, no new parameters, metrics, or tags, and no single-row deletes. parent_iteration_id links child runs (trials, folds, workers) to the run that launched them. Children skip the environment snapshot by default and use their nearest ancestor's.
  • Artifacts. An inline artifact keeps its bytes in data. A filesystem or s3 artifact keeps a file:// or s3:// URI in uri and leaves data empty. size_bytes and content_hash are filled for every backend.
  • Model registry. Model names are unique per project. model_version.version is numbered 1, 2, 3… per model when the row is inserted. A version's iteration and its optional artifact must belong to the model's project. Promoting a version to PRODUCTION archives the model's previous production version, so a model never has more than one.
  • Tags. tag.value is unique across the database. The three join tables attach a tag to a project, experiment, or iteration at most once.
Deletes
Deleting What happens
Project Its models and their versions are removed first, then its experiments with everything below them, its permissions, and its tag links.
Experiment Refused while a model version references one of its iterations or artifacts. Otherwise its iterations, artifacts, and tag links are removed.
Iteration Refused while a model version references it. Otherwise its parameters, metrics, and tag links are removed, and its children stay as standalone runs (parent_iteration_id becomes NULL).
Artifact Refused while a model version references it. Otherwise the stored file or S3 object is removed with the row.
Model All its versions are removed.
User Their permissions are removed.

A refused delete returns false from the service and 409 CONFLICT from the REST API. Tags stay in tag after their links are removed.

Dominant language
Julia
Stars
25
Forks
2
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from JuliaAI/DearDiary.jl

All issues in JuliaAI/DearDiary.jl

Similar issues

More Julia issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.