go-sqlite-substr-offset-sequence-collision

Answer

Root cause (1-based SQLite offsets). An incident ref is INC-YYYY-MM-DD-0001; the prefix INC-YYYY-MM-DD- is exactly 15 characters, so the 4 sequence digits start at 1-based byte 16. substr(incident_ref, 15) started at the separator dash:

substr('INC-2026-08-06-0001', 15) = '-0001'   CAST('-0001' AS INTEGER) = -1

So MAX(CAST(...)) + 1 computed -1 + 1 = 0, producing refs 0001, 0000, 0001… and a UNIQUE-constraint failure (409 DUPLICATE) on the 3rd create of the day.

Fix — offset 16. In GetNextIncidentSequence (Go + modernc.org/sqlite):

// GetNextIncidentSequence returns the next per-day sequence number (1-based)
// for refs shaped "INC-YYYY-MM-DD-0001". The 4-digit sequence starts at
// 1-based byte 16, right after the 15-char "INC-YYYY-MM-DD-" prefix.
// BUGFIX: offset 15 parsed the separator dash ("-0001" -> CAST = -1), which
// reset the sequence to 0 and caused ref collisions (409) on the 3rd create.
func GetNextIncidentSequence(ctx context.Context, db *sql.DB, date string) (int, error) {
    prefix := "INC-" + date + "-" // 15 chars
    var next int
    err := db.QueryRowContext(ctx, `
        SELECT COALESCE(MAX(CAST(substr(incident_ref, 16) AS INTEGER)), 0) + 1
        FROM incidents
        WHERE substr(incident_ref, 1, 15) = ?  -- day-scoped, exact prefix match
    `, prefix).Scan(&next)
    if err != nil {
        return 0, fmt.Errorf("GetNextIncidentSequence: %w", err)
    }
    return next, nil
}

CreateIncident formats with zero padding and maps any residual UNIQUE violation to ErrDuplicateRef → 409:

func CreateIncident(ctx context.Context, db *sql.DB, date, title string) (string, error) {
    seq, err := GetNextIncidentSequence(ctx, db, date)
    if err != nil {
        return "", err
    }
    ref := fmt.Sprintf("INC-%s-%04d", date, seq)
    if _, err := db.ExecContext(ctx,
        `INSERT INTO incidents (incident_ref, title, created_date) VALUES (?, ?, ?)`,
        ref, title, date); err != nil {
        if isUniqueViolation(err) { // "unique constraint failed" -> HTTP 409
            return "", ErrDuplicateRef
        }
        return "", err
    }
    return ref, nil
}

Mandated regression test (creates 4 rows, asserts unique sequential refs):

func TestCreateIncidentSequentialRefsRegression(t *testing.T) {
    db := openTestDB(t) // fresh migrated DB
    date := "2026-08-06"
    seen := map[string]bool{}
    for i := 1; i <= 4; i++ {
        ref, err := CreateIncident(context.Background(), db, date, fmt.Sprintf("incident %d", i))
        if err != nil {
            t.Fatalf("create %d: %v", i, err) // bug would fail here on i==3 (409)
        }
        want := fmt.Sprintf("INC-%s-%04d", date, i)
        if ref != want {
            t.Fatalf("create %d: ref = %q, want %q", i, ref, want)
        }
        if seen[ref] {
            t.Fatalf("create %d: duplicate ref %q generated", i, ref)
        }
        seen[ref] = true
    }
    var n int
    db.QueryRow(`SELECT COUNT(*) FROM incidents`).Scan(&n)
    if n != 4 { t.Fatalf("rows = %d, want 4", n) }
}

Second issue — stale-DB false positive. The reopening PM probe 500 was not a regression of the fix; the API was bound to a leftover pre-fix helios.db at the repo root (old TEXT schema, no created_date, no UNIQUE), so create probes 500'd on missing columns. Guard at bind time so the API can never silently bind to an unmigrated DB:

func NewAPI(db *sql.DB) (*API, error) {
    if err := VerifySchema(db); err != nil {
        return nil, err // fail fast instead of 500 on first probe
    }
    return &API{db: db}, nil
}

func VerifySchema(db *sql.DB) error {
    // PRAGMA table_info(incidents): require incident_ref, title, created_date
    // + sqlite_master DDL must contain UNIQUE on incident_ref.
    // -> error: "incidents missing column ... (stale DB?)"
}

Operational rule: always verify the API is bound to the same fresh DB the migration just ran on — never reuse a stray helios.db from an earlier schema.