profilarr/docs/backend/schema-bump.md

Schema Bump

How to safely evolve the PCD schema without breaking existing databases or Profilarr installations.

Table of Contents

• Overview
• Golden Rules
• Compatibility Matrix
• Walkthrough
  • 1. Write the schema op
  • 2. Bump the schema version
  • 3. Update Profilarr
  • 4. Update the reference snapshot
  • 5. Update database PCDs
• Safe vs Unsafe Changes
• What About minimum_version?
• Example: Adding position to quality_group_members

Overview

The PCD schema lives in a separate repo (Dictionarry-Hub/schema). It's applied as the first layer during compile - before base ops, tweaks, or user ops. Changing it affects every PCD that depends on it.

schema repo (Dictionarry-Hub/schema)
├── pcd.json          ← version lives here
└── ops/
    ├── 0.schema.sql  ← original DDL (never edited)
    ├── 1.languages.sql
    ├── 2.qualities.sql
    └── 3.xxx.sql     ← new changes go here (additive only)

Schema changes are always new files in ops/. Old files are never edited. Ops are applied in filename order during compile, so the numbering matters.

Golden Rules

1. Never edit existing ops files. Published ops are immutable. Add a new numbered file.
2. Always use defaults for new columns. This is what makes the change backwards compatible. Old ops that insert without the new column still compile.
3. Profilarr code must handle the column not existing. Users may update Profilarr before their database pulls the new schema, or they may be on an older schema version. Reads should fall back gracefully.
4. Test the compile with old ops first. Before publishing, verify that existing published database ops still compile cleanly against the new schema.

Compatibility Matrix

For any additive change with a default value:

Scenario	Result
New schema + old Profilarr	Column exists with default, old code ignores it, compiles fine
New schema + new Profilarr	Profilarr reads and writes the new column, everything works
Old schema + new Profilarr	Column doesn't exist, new code must fall back gracefully (e.g. sort alpha)
Old schema + old Profilarr	Nothing changed, nothing broke

Walkthrough

1. Write the schema op

Create a new numbered .sql file in the schema repo's ops/ folder. The number must be higher than the last existing op.

ops/
├── 0.schema.sql
├── 1.languages.sql
├── 2.qualities.sql
└── 3.your-change.sql   ← new file

Use ALTER TABLE for column additions. Use CREATE TABLE for new tables. The SQL must be valid as a standalone operation - it runs after all previous ops in order.

2. Bump the schema version

Update version in the schema repo's pcd.json. Follow semver:

Change type	Bump	Example
Additive column/table with default (compatible)	Minor	1.0.0 → 1.1.0
Breaking change (rename, drop, NOT NULL no default)	Major	1.0.0 → 2.0.0

Breaking changes should be avoided. If one is truly necessary, coordinate with all known PCD maintainers and bump profilarr.minimum_version.

3. Update Profilarr

In the Profilarr codebase:

• Writer: Update the relevant writer to include the new column in generated ops (e.g. include position in quality_group_members inserts).
• Reader: Update read queries to use the new column, with a fallback for when it doesn't exist (old schema).
• UI: Surface the new data. Handle the fallback state gracefully (e.g. show items in alphabetical order if no position data exists).
• Reference snapshot: Update docs/backend/0.schema.sql to reflect the new schema state. This file is documentation only - it's not used at runtime.

4. Update the reference snapshot

Keep docs/backend/0.schema.sql in the Profilarr repo in sync with the current schema state. This is the file developers read to understand the PCD schema without cloning the schema repo. It's a reference snapshot, not a runtime dependency.

5. Update database PCDs

Each database PCD that uses the affected table:

• Bump dependencies.schema in pcd.json to require the new schema version (e.g. "schema": "^1.1.0").
• Update published ops to include the new column values where appropriate.
• Old ops that don't include the column still work because of the default.

Safe vs Unsafe Changes

Safe (backwards compatible)

These can be deployed without coordination:

• ALTER TABLE ... ADD COLUMN ... DEFAULT value - existing inserts still work
• CREATE TABLE ... - old code ignores tables it doesn't know about
• CREATE INDEX ... - no impact on inserts or reads by old code
• INSERT INTO (new seed data) - additive, old code unaffected

Unsafe (breaking)

These require a major version bump and profilarr.minimum_version update:

• ALTER TABLE ... ADD COLUMN ... NOT NULL (no default) - old inserts fail
• ALTER TABLE ... DROP COLUMN - old reads/writes fail
• ALTER TABLE ... RENAME COLUMN - old queries reference wrong name
• DROP TABLE - old queries fail
• Changing column types or constraints that reject existing data

Avoid breaking changes. The cost is high: every PCD must update, every Profilarr install must update, and the ordering has to be coordinated. If you think you need a breaking change, consider whether an additive approach (new column + deprecate old) achieves the same goal.

What About minimum_version?

The profilarr.minimum_version field in a PCD's pcd.json tells Profilarr "you must be at least this version to use this database." It's the escape hatch for when a schema change requires Profilarr logic that old versions don't have.

For additive changes with graceful fallbacks in Profilarr, you usually don't need to bump this. The fallback (e.g. alphabetical sort) is good enough for older Profilarr versions.

For changes where the fallback would be broken or misleading, bump it. Profilarr checks this at link/sync time and tells the user to update.

Example: Adding position to quality_group_members

Goal: Let users order qualities within groups instead of showing insertion order.

Schema op (ops/3.quality-group-member-position.sql):

ALTER TABLE quality_group_members
ADD COLUMN position INTEGER NOT NULL DEFAULT 0;

Schema pcd.json: bump version from 1.0.0 to 1.1.0.

Profilarr changes:

• Writer (qualityProfiles/qualities/update.ts): include position in quality_group_members inserts, based on UI ordering.
• Reader: sort group members by position, fall back to alphabetical when all positions are 0 (old schema or unordered data).
• UI: allow drag-to-reorder within groups, persist position values.
• Update docs/backend/0.schema.sql with the new column.

Database PCD: bump dependencies.schema to "^1.1.0". New ops include position values. Old ops without position still compile (default 0).

Deployment order:

1. Schema repo: publish v1.1.0 (new op + version bump)
2. Profilarr: update writer/reader/UI + reference snapshot
3. Database PCD: bump schema dep, publish ops with position values

No minimum_version bump needed - old Profilarr ignores the column, new Profilarr falls back to alphabetical when the column is missing or all zeros.