Required-store catalog
apps/backend/internal/persistence/requiredstores/catalog.go ↗The catalog is the single source of truth for every built-in SQL owner, its tables, dependencies, and capabilities.
Descriptor
type Descriptor struct {
ID string
OwnerPackage string
RequiredTables []string
DependsOn []string
Capabilities []Capability
}
Catalog entries
var catalog = []Descriptor{
{ID: "schema-meta", OwnerPackage: "internal/persistence", RequiredTables: []string{"kandev_meta"}},
{ID: "task", OwnerPackage: "internal/task/repository/sqlite", RequiredTables: []string{"workspaces", "tasks"}, DependsOn: []string{"schema-meta"}, Capabilities: []Capability{CapabilityBoolean, CapabilityTimestamp, CapabilityConflict, CapabilityTransaction}},
{ID: "workflow", OwnerPackage: "internal/workflow/repository", RequiredTables: []string{"workflow_templates", "workflow_steps"}, DependsOn: []string{"task"}},
{ID: "office", OwnerPackage: "internal/office/repository/sqlite", RequiredTables: []string{"office_projects", "runs"}, DependsOn: []string{"task", "agent-settings"}},
{ID: "github", OwnerPackage: "internal/github", RequiredTables: []string{"github_pr_watches"}, DependsOn: []string{"task", "user"}},
}
Validation
func ValidateCatalog(descriptors []Descriptor) error {
if len(descriptors) == 0 {
return fmt.Errorf("catalog is empty")
}
// checks duplicate IDs, unknown dependencies, out-of-order deps, cycles
if err := validateCycles(descriptors); err != nil {
return err
}
return nil
}
Shared SQL rendering
apps/backend/internal/db/dialect/schema.go ↗Stores render one portable schema text for both engines instead of branching SQL in each repository.
RenderSchema
func RenderSchema(driver, schema string) (string, error) {
if driver != SQLite3 && driver != PGX {
return "", fmt.Errorf("unsupported database driver %q", driver)
}
timestamp := TimestampType(driver)
boolean := "INTEGER"
identity := "INTEGER"
if IsPostgres(driver) {
boolean = "BOOLEAN"
identity = "BIGSERIAL"
}
rendered := strings.NewReplacer(
"{{timestamp}}", timestamp,
"{{boolean}}", boolean,
"{{identity}}", identity,
"{{current_time}}", "CURRENT_TIMESTAMP",
).Replace(schema)
if strings.Contains(rendered, "{{") {
return "", fmt.Errorf("schema contains unknown or unexpanded token")
}
return rendered, nil
}
Time helpers
func NullableTimestamp(driver, placeholder string) string {
if IsPostgres(driver) {
return fmt.Sprintf("(%s)::timestamptz", placeholder)
}
return placeholder
}
func DurationMs(driver, end, start string) string {
if IsPostgres(driver) {
return fmt.Sprintf("EXTRACT(EPOCH FROM (%s - %s)) * 1000", end, start)
}
return fmt.Sprintf("(julianday(%s) - julianday(%s)) * 86400000", end, start)
}
Rebind boundary
func Bind(rebinder Rebinder, query string, args ...any) (string, []any, error) {
expanded, expandedArgs, err := sqlx.In(query, args...)
if err != nil {
return "", nil, err
}
return rebinder.Rebind(expanded), expandedArgs, nil
}
SQL guard analyzer
apps/backend/internal/db/sqlguard/analyzer.go ↗The guard parses Go AST and rejects SQLite-only catalog, conflict, boolean, and date syntax before it ships.
Rules
const (
RuleSQLiteCatalog Rule = "sqlite-catalog"
RuleConflictSyntax Rule = "conflict-syntax"
RuleRawPlaceholder Rule = "raw-placeholder"
RuleBooleanInteger Rule = "boolean-integer"
RuleSQLiteDateFunction Rule = "sqlite-date-function"
RuleDateTimeType Rule = "datetime-type"
)
Literal check
func analyzeSQLLiteral(filename, value, symbol string, position token.Position, exemptions []Exemption, seen map[string]struct{}, result *analysisResult, forceSQL ...bool) {
checks := []struct{ rule Rule; match bool; message string }{
{RuleSQLiteCatalog, sqlText && sqliteCatalogPattern.MatchString(value), "SQLite catalog or PRAGMA syntax must stay behind a dialect boundary"},
{RuleConflictSyntax, sqlText && conflictPattern.MatchString(value), "use portable conflict syntax"},
{RuleBooleanInteger, sqlText && booleanIntegerPattern.MatchString(value), "use a boolean value or a dialect-rendered boolean default"},
{RuleSQLiteDateFunction, sqlText && sqliteDatePattern.MatchString(value), "use an internal/db/dialect date helper"},
}
}
Exemptions
func ValidateExemptions(exemptions []Exemption) error {
for _, exemption := range exemptions {
if exemption.File == "" || filepath.IsAbs(exemption.File) || strings.ContainsAny(exemption.File, "*?") {
return fmt.Errorf("SQL guard exemption file must be an exact relative path: %q", exemption.File)
}
if _, ok := knownRules[exemption.Rule]; !ok {
return fmt.Errorf("SQL guard exemption has unknown rule %q", exemption.Rule)
}
}
return nil
}
Conformance framework
apps/backend/internal/persistence/storeconformance/adapters.go ↗One fixed adapter per catalog entry proves schema creation and real API behavior on both engines.
Adapters
func Adapters() []testconformance.Adapter {
descriptors := requiredstores.Catalog()
adapters := make([]testconformance.Adapter, 0, len(descriptors))
for _, descriptor := range descriptors {
adapters = append(adapters, adapterFor(descriptor))
}
return adapters
}
func adapterFor(descriptor requiredstores.Descriptor) testconformance.Adapter {
return testconformance.Adapter{
ID: descriptor.ID,
Engines: map[testconformance.EngineName]testconformance.EngineAdapter{
testconformance.EngineSQLite: {Fresh: initializer, Replay: initializer},
testconformance.EnginePostgres: {Fresh: initializer, Replay: initializer},
},
Scenarios: behaviorScenarios(descriptor),
}
}
Capability probe
func exerciseCapability(s testconformance.ScenarioContext, action apiAction, capability requiredstores.Capability, suffix string) error {
switch capability {
case requiredstores.CapabilityBoolean:
for _, want := range []bool{false, true} {
record, err = action.setBoolean(s, key, record, want)
got, ok := action.readBoolean(record)
if got != want {
return fmt.Errorf("boolean = %t, want %t", got, want)
}
}
case requiredstores.CapabilityTimestamp:
created, updatedAt, ok := recordTimestamps(before)
if created.Location() != time.UTC || updatedAt.Location() != time.UTC {
return fmt.Errorf("timestamps are not UTC")
}
}
return nil
}
Bootstrap and health enforcement
apps/backend/internal/backendapp/storage.go ↗Bootstrap records every store through the tracker and fails fast when a required store is missing or unhealthy.
Tracker wiring
func provideRepositories(ctx context.Context, cfg *config.Config, log *logger.Logger, version string) (*db.Pool, *Repositories, []func() error, error) {
tracker, err := requiredstores.NewCatalogTracker()
if err != nil {
return nil, nil, nil, err
}
pool, cleanup, err := persistence.Provide(cfg, log, version)
if err != nil {
return nil, nil, nil, err
}
if err := recordRequiredStore(tracker, "schema-meta", nil); err != nil {
return nil, nil, nil, err
}
taskRepoImpl, cleanup, err := repository.Provide(writer, reader, log)
if err := recordRequiredStore(tracker, "task", err); err != nil {
return nil, nil, nil, fmt.Errorf("task store: %w", err)
}
}
Health probe
func (h *Health) Check(ctx context.Context) error {
probeErr := h.ping(ctx)
for index, descriptor := range h.tracker.catalog {
results[index] = probeErr
if probeErr == nil {
results[index] = h.probeTables(ctx, descriptor)
}
}
for index, result := range results {
if err := h.tracker.RecordProbe(h.tracker.catalog[index].ID, result); err != nil {
failures = append(failures, err)
}
}
if len(failures) == 0 {
return nil
}
return errors.Join(failures...)
}
Middleware
func requiredPersistenceMiddleware(health *requiredstores.Health) gin.HandlerFunc {
return func(c *gin.Context) {
if health == nil || health.Healthy() || persistencePathExcluded(c.Request.URL.Path) {
c.Next()
return
}
c.Abort()
c.JSON(http.StatusServiceUnavailable, gin.H{
"error": "required persistence is unavailable",
"code": "persistence_unavailable",
"store_ids": health.UnavailableStoreIDs(),
})
}
}
CI contributor gates
.github/workflows/backend-tests.yml ↗CI now runs explicit, reviewable package lists and pinned Postgres versions instead of grep discovery.
Static checks
- name: Check required-store SQL portability
run: go run ./cmd/sqlguard ./internal
- name: Run catalog and SQLite conformance gates
run: |
go test -v -race ./internal/persistence/storeconformance \
-run '^(TestStoreCatalogCompleteness|TestStoreConformance|TestUpgradeFixtureManifest|TestPreviousStableUpgrade)$'
Postgres 16
- name: Run fixed PostgreSQL 16 persistence gates
run: |
POSTGRES_PACKAGES=(
./internal/agent/settings/store
./internal/analytics/repository/sqlite
./internal/backendapp
./internal/db
./internal/persistence/storeconformance
./internal/task/repository/sqlite
)
go test -v -race "${POSTGRES_PACKAGES[@]}"
Postgres 18
postgres-18:
name: Backend Postgres 18
services:
postgres:
image: ghcr.io/kdlbs/kandev-ci:postgres-18@sha256:4ef4dbc939d61acea57712655ddb4b4ab27419c913f94cca0cd57cb3ea3c2280
steps:
- name: Run PostgreSQL 18 boot and upgrade gates
run: |
go test -v -race ./internal/persistence/storeconformance ./internal/backendapp \
-run '^(TestPreviousStableUpgrade|TestPostgresBootInitializesRepositories)$'
