Skip to content

fix(database): exclude derived FTS search tables and generated columns from JSON export and import #1273

Closed

Description

@khaira777

Summary

Schema 115 (#1204) added external-content FTS5 virtual tables (orders_search_fts, customers_search_fts) and their SQLite FTS5 shadow tables (*_fts_data, *_fts_idx, *_fts_docsize, *_fts_config). While getTables(db) in main/db.ts:2440 excludes virtual and FTS shadow tables, GET /api/db/export (main/routes/database.ts:93) queries raw sqlite_master tables and exports all 10 FTS virtual/shadow tables (plus customers.phone_digits virtual generated column). Re-importing that JSON via POST /api/db/import fails with SqliteError: table orders_search_fts_data may not be modified (or cannot INSERT into generated column "phone_digits").

Acceptance Criteria

  • Reuse canonical getTables(db) (and stored getTableColumns(db, tableName)) in GET /api/db/export so derived FTS virtual/shadow tables and virtual generated columns are excluded from exports while preserving EXPORT_EXCLUDE_TABLES and secret redaction.
  • Filter incoming tables in POST /api/db/import against getTables(db) and always intersect imported columns with getTableColumns(db, tableName) so existing schema 115 exports containing *_search_fts* shadow tables or customers.phone_digits import cleanly in both overwrite: true and overwrite: false modes, followed by rebuildSearchIndex(db) and executeSequenceRepair(db).
  • Add full export -> overwrite and merge import round-trip tests at schema 115, schema 114 -> 115 import tests, contaminated schema 115 export tests, FTS searchability checks, and rollback sentinel checks.

Activity

  1. self-assigned this
    on Oct 21, 2026
  2. added a commit that references this issue on Oct 21, 2026
    7198d17
  3. added a commit that references this issue on Oct 21, 2026
    51ba436
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions