# Safe Database Schema Migration Strategy in AgentsView: Non-Destructive SQLite and PostgreSQL Management

> Learn AgentsView's safe database schema migration strategy for non-destructive SQLite and PostgreSQL management. Protect your archive data with version tracking and idempotent additions.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: how-to-guide
- Published: 2026-07-04

---

**AgentsView implements a conservative, non-destructive migration strategy that guarantees existing archive data is never lost through version tracking, idempotent column additions, and conditional database rebuilds while maintaining parity between SQLite and PostgreSQL backends.**

The open-source AgentsView project (kenn-io/agentsview) implements robust **safe database schema migrations** designed to handle evolving parser requirements without destroying user data. By leveraging SQLite's `user_version` pragma and PostgreSQL sentinel tables, the system detects schema staleness and applies targeted, non-destructive alterations—or triggers full re-ingestion only when the database is treated as a disposable cache.

## Version Tracking and Schema Staleness Detection

The migration system centers on a compiled **data version** constant defined in [`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go) at line 79:

```go
const dataVersion = 59

```

When the binary initializes, the `readUserVersion` function reads the SQLite `user_version` pragma and compares it against this compiled value (lines 53–55). The `probeDatabase` function (lines 24–30) performs comprehensive validation: verifying the database file exists, checking for required columns via `needsSchemaRebuild`, and confirming the stored data version matches expectations.

### Detecting Schema Drift

If `probeDatabase` discovers missing required columns, the system flags the database for a **schema rebuild**. However, if the schema exists but merely lacks newer columns, the system proceeds to idempotent column migrations instead.

## Non-Destructive Migration Execution Paths

The migration logic follows a hierarchical decision tree with three distinct outcomes based on detected state.

### Path 1: Idempotent Column Additions

When the base schema is intact but missing newer columns, `migrateColumns` (lines 18–23) executes `ALTER TABLE … ADD COLUMN …` statements. Each migration first queries `pragma_table_info` to confirm column absence, ensuring the operation is idempotent. Because new columns include default values, existing rows remain valid without data loss.

### Path 2: Full Database Rebuild

If `needsSchemaRebuild` detects fundamentally missing required columns, the system performs a **destructive rebuild** by dropping and recreating the database from the embedded `schemaSQL`. This path is safe because AgentsView treats the database as a cache—the daemon will re-ingest all data from source session files during the next sync cycle.

### Path 3: Data Version Resync

When the schema is current but the stored `user_version` is lower than the compiled `dataVersion`, the system sets `dataStale` to true (lines 66–73). This triggers a **non-destructive full resync**: file modification times are reset, skip-caches cleared, and the parser re-writes rows to populate new fields while preserving all existing data.

## Backfills and Index Management

After column additions complete, the migration system creates necessary partial indexes and populates legacy rows. The `createPartialIndexesLocked` function builds indexes optimized for query patterns, while backfill jobs like `backfillIsAutomatedLocked` and `backfillToolCallFieldsLocked` (lines 66–71) populate new columns for historical data. These operations are idempotent and can resume safely if interrupted.

## Read-Only Safety Mechanisms

Opening a database in read-only mode via `OpenReadOnly` never triggers migrations. If the schema is outdated, the function returns a `SchemaUpgradeRequiredError` (lines 68–74), prompting users to restart the daemon with write permissions to apply pending changes. This prevents partial migrations on read-only filesystems or concurrent access scenarios.

## PostgreSQL Migration Parity

The same migration principles apply to the optional PostgreSQL backend. Schema migration logic resides in [`internal/postgres/schema.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/schema.go), where column migrations are expressed as DDL statements guarded by existence checks. A sentinel table `pg_sync_state` tracks migration progress (lines 71–78), ensuring PostgreSQL deployments maintain exact parity with SQLite's behavior.

## Practical Implementation Examples

Opening a database automatically runs the migration checks:

```go
db, err := db.Open("/path/to/archive.db")
if err != nil {
    // Handles schema-stale, data-stale, or too-new-binary errors.
    log.Fatalf("failed to open DB: %v", err)
}

```

The core migration loop follows this simplified logic:

```go
if schemaStale {
    // Drop & recreate – safe because the archive will be repopulated.
    dropDatabase(path)
}
d, _ := openAndInit(path)   // create writer/reader pools
d.migrateColumns()          // idempotent ALTERs
if dataStale && !schemaStale {
    d.dataStale.Store(true) // trigger full resync on next sync cycle
}

```

Detecting a read-only open that requires writable migration:

```go
db, err := db.OpenReadOnly("/path/to/archive.db")
if internal/db.IsSchemaUpgradeRequired(err) {
    fmt.Println("Schema outdated – restart the daemon to apply migrations.")
}

```

## Summary

- **Version tracking** via `user_version` pragma and compiled `dataVersion` constants enables precise schema staleness detection.
- **Idempotent column migrations** using `ALTER TABLE … ADD COLUMN` with existence checks prevent data loss during incremental updates.
- **Conditional rebuilds** only occur when the schema is fundamentally incompatible, treating the database as a cache that can be re-ingested from source files.
- **Read-only safety** prevents partial migrations by returning `SchemaUpgradeRequiredError` when writable access is unavailable.
- **PostgreSQL parity** ensures consistent behavior across SQLite and PostgreSQL backends through guarded DDL and sentinel tables.

## Frequently Asked Questions

### What happens if I open an outdated database in read-only mode?

AgentsView returns a `SchemaUpgradeRequiredError` with a clear message indicating the schema is outdated. According to the source code in [`internal/db/db.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/db.go) (lines 68–74), the system never applies migrations in read-only mode, preventing partial upgrades or corruption on read-only filesystems. You must restart the daemon with write permissions to allow the migration to proceed.

### Will I lose my data when AgentsView updates its schema?

No. The migration strategy is explicitly **non-destructive** for existing data. When new columns are required, the system uses `ALTER TABLE … ADD COLUMN` with default values. Only when the schema is fundamentally broken (missing required columns) does the system drop and recreate the database, which is safe because AgentsView treats the database as a cache—the original data remains in source session files and will be re-ingested automatically.

### How does the system handle interrupted migrations?

All migration steps are **idempotent**. Column additions check `pragma_table_info` before executing DDL, and backfill jobs can safely resume from where they left off. If the process crashes during migration, simply restarting the daemon will continue the migration from the appropriate step without corrupting the database.

### Does the PostgreSQL backend use the same migration logic as SQLite?

Yes, the principles are identical. According to the source code in [`internal/postgres/schema.go`](https://github.com/kenn-io/agentsview/blob/main/internal/postgres/schema.go), PostgreSQL migrations use existence-guarded DDL statements and a `pg_sync_state` sentinel table to track progress (lines 71–78). Both backends follow the same detect-then-apply model, ensuring consistent behavior whether using local SQLite archives or centralized PostgreSQL stores.