0
0
# SQLite Incremental FTS Implementation Plan
> **For Claude:** REQUIRED SUB-SKILL: Use superpowers:executing-plans to implement this plan task-by-task.
**Goal:** Replace SQLite's drop-and-rebuild FTS strategy with incremental FTS5 external content tables that stay in sync via application-level updates on insert/delete.
**Architecture:** Add `updateIndex()` and `deleteFromIndex()` methods to `SearchPort`. The SQLite implementation uses FTS5 external content mode (`content=shadowTable`) so the FTS index references the shadow data table directly. On `insertRecord()` and `deleteRecord()`, the framework incrementally updates both the shadow table and FTS index. The periodic `rebuildAllIndexes` in the indexer is skipped for SQLite. DuckDB keeps its existing drop-and-rebuild behavior unchanged.
**Tech Stack:** SQLite FTS5 with external content tables, TypeScript
---
### Task 1: Extend SearchPort Interface
**Files:**
- Modify: `packages/hatk/src/database/ports.ts:50-62`
**Step 1: Add new methods to SearchPort**
Add `updateIndex` and `deleteFromIndex` to the `SearchPort` interface:
```typescript
export interface SearchPort {
/** Build/rebuild an FTS index for a table */
buildIndex(shadowTable: string, sourceQuery: string, searchColumns: string[]): Promise<void>
/** Incrementally update a single record in the FTS index */
updateIndex?(shadowTable: string, uri: string, row: Record<string, string | null>, searchColumns: string[]): Promise<void>
/** Remove a single record from the FTS index */
deleteFromIndex?(shadowTable: string, uri: string): Promise<void>
/** Check if the FTS index already exists (for skipping rebuild on startup) */
indexExists?(shadowTable: string): Promise<boolean>
/** Search a table, returning URIs with scores */
search(
shadowTable: string,
query: string,
searchColumns: string[],
limit: number,
offset: number,
): Promise<Array<{ uri: string; score: number }>>
}
```
Note: Methods are optional (`?`) so DuckDB's SearchPort doesn't need to implement them.
**Step 2: Commit**
```bash
git add packages/hatk/src/database/ports.ts
git commit -m "feat: add incremental FTS methods to SearchPort interface"
```
---
### Task 2: Rewrite SQLiteSearchPort for External Content FTS5
**Files:**
- Modify: `packages/hatk/src/database/adapters/sqlite-search.ts`
**Step 1: Rewrite the SQLiteSearchPort class**
Replace the entire file with:
```typescript
import type { SearchPort } from '../ports.ts'
import type { DatabasePort } from '../ports.ts'
/**
* SQLite FTS5-based search port with incremental updates.
*
* Uses external content FTS5 tables (`content=shadowTable`) so the FTS index
* references the shadow data table. Updates happen incrementally per-record
* instead of dropping and rebuilding the entire index.
*/
export class SQLiteSearchPort implements SearchPort {
constructor(private port: DatabasePort) {}
async indexExists(shadowTable: string): Promise<boolean> {
const rows = await this.port.query(
`SELECT 1 FROM sqlite_master WHERE type='table' AND name=$1`,
[shadowTable],
)
return rows.length > 0
}
async buildIndex(shadowTable: string, sourceQuery: string, searchColumns: string[]): Promise<void> {
// Drop existing FTS table and data table
await this.port.execute(`DROP TABLE IF EXISTS ${shadowTable}_fts`, [])
await this.port.execute(`DROP TABLE IF EXISTS ${shadowTable}`, [])
// Create the shadow data table from the source query
await this.port.execute(`CREATE TABLE ${shadowTable} AS ${sourceQuery}`, [])
// Add a unique index on uri for fast lookups during incremental updates
await this.port.execute(`CREATE UNIQUE INDEX IF NOT EXISTS ${shadowTable}_uri ON ${shadowTable}(uri)`, [])
// Create the FTS5 virtual table with external content pointing to shadow table
const colList = searchColumns.join(', ')
await this.port.execute(
`CREATE VIRTUAL TABLE ${shadowTable}_fts USING fts5(uri UNINDEXED, ${colList}, content=${shadowTable}, content_rowid=rowid, tokenize='porter unicode61 remove_diacritics 2')`,
[],
)
// Populate FTS table from the shadow data table
const selectCols = ['uri', ...searchColumns].map((c) => `COALESCE(CAST(${c} AS TEXT), '')`)
await this.port.execute(
`INSERT INTO ${shadowTable}_fts (uri, ${colList}) SELECT ${selectCols.join(', ')} FROM ${shadowTable}`,
[],
)
}
async updateIndex(
shadowTable: string,
uri: string,
row: Record<string, string | null>,
searchColumns: string[],
): Promise<void> {
// Get existing rowid if this record is already indexed (for FTS delete)
const existing = await this.port.query(
`SELECT rowid FROM ${shadowTable} WHERE uri = $1`,
[uri],
)
if (existing.length > 0) {
const oldRowid = (existing[0] as any).rowid
// Read old values for FTS delete command
const colList = searchColumns.join(', ')
const oldRows = await this.port.query(
`SELECT uri, ${colList} FROM ${shadowTable} WHERE rowid = $1`,
[oldRowid],
)
if (oldRows.length > 0) {
const old = oldRows[0] as any
const oldVals = ['uri', ...searchColumns].map((c) => `COALESCE(CAST(${JSON.stringify(old[c] ?? '')} AS TEXT), '')`)
// Delete old entry from FTS using special 'delete' command
await this.port.execute(
`INSERT INTO ${shadowTable}_fts(${shadowTable}_fts, rowid, uri, ${colList}) VALUES('delete', $1, ${oldVals.join(', ')})`,
[oldRowid],
)
}
// Update shadow table row
const setClauses = searchColumns.map((c, i) => `${c} = $${i + 2}`)
const values = [uri, ...searchColumns.map((c) => row[c] ?? null)]
await this.port.execute(
`UPDATE ${shadowTable} SET ${setClauses.join(', ')} WHERE uri = $1`,
values,
)
// Re-read rowid (same after UPDATE) and insert new FTS entry
const newVals = searchColumns.map((c) => `COALESCE(CAST($${searchColumns.indexOf(c) + 2} AS TEXT), '')`)
await this.port.execute(
`INSERT INTO ${shadowTable}_fts(rowid, uri, ${colList}) VALUES($1, $2, ${newVals.join(', ')})`,
[oldRowid, uri, ...searchColumns.map((c) => row[c] ?? '')],
)
} else {
// Insert new row into shadow table
const colList = searchColumns.join(', ')
const placeholders = searchColumns.map((_, i) => `$${i + 2}`)
const values = [uri, ...searchColumns.map((c) => row[c] ?? null)]
await this.port.execute(
`INSERT INTO ${shadowTable} (uri, ${colList}) VALUES ($1, ${placeholders.join(', ')})`,
values,
)
// Get the new rowid and insert into FTS
const newRow = await this.port.query(`SELECT rowid FROM ${shadowTable} WHERE uri = $1`, [uri])
const newRowid = (newRow[0] as any).rowid
await this.port.execute(
`INSERT INTO ${shadowTable}_fts(rowid, uri, ${colList}) VALUES($1, $2, ${placeholders.join(', ')})`,
[newRowid, uri, ...searchColumns.map((c) => row[c] ?? '')],
)
}
}
async deleteFromIndex(shadowTable: string, uri: string): Promise<void> {
// We need the rowid and old column values for the FTS delete command
const rows = await this.port.query(
`SELECT rowid, * FROM ${shadowTable} WHERE uri = $1`,
[uri],
)
if (rows.length === 0) return
const old = rows[0] as any
const rowid = old.rowid
// Delete from FTS — requires passing old values for token removal
// Get column names from the shadow table (excluding uri, cid, did, indexed_at, handle metadata cols)
await this.port.execute(`DELETE FROM ${shadowTable} WHERE uri = $1`, [uri])
// Note: with content= table, deleting from the content table is sufficient
// if we also run the FTS delete command. But since we use content=shadowTable,
// and we just deleted from shadowTable, we need to tell FTS explicitly.
// Actually with external content, we must manually sync.
// The simplest approach: delete from shadow table, then rebuild FTS for this row.
// But we already deleted... so we use the 'delete' command with old values.
}
async search(
shadowTable: string,
query: string,
_searchColumns: string[],
limit: number,
offset: number,
): Promise<Array<{ uri: string; score: number }>> {
const escaped = query.replace(/['"*(){}[\]^~\\:]/g, ' ').trim()
if (!escaped) return []
const sql = `SELECT uri, -bm25(${shadowTable}_fts) AS score
FROM ${shadowTable}_fts
WHERE ${shadowTable}_fts MATCH $1
ORDER BY score DESC
LIMIT $2 OFFSET $3`
return this.port.query(sql, [escaped, limit, offset])
}
}
```
**Wait** — the `updateIndex` and `deleteFromIndex` above are too complex with manual FTS delete commands and rowid tracking. Let me simplify.
**Step 1 (revised): Simpler approach using DELETE + INSERT on shadow table with FTS rebuild-row**
Actually, the cleanest approach for external content FTS5: since we use `content=shadowTable`, we can:
1. Upsert the shadow table row
2. Run `INSERT INTO fts(fts) VALUES('rebuild')` only for the changed row — but FTS5 doesn't support single-row rebuild.
The correct pattern for external content tables:
1. To **delete**: `INSERT INTO fts(fts, rowid, col1, ...) VALUES('delete', old_rowid, old_col1, ...)`
2. To **insert**: `INSERT INTO fts(rowid, col1, ...) VALUES(new_rowid, new_col1, ...)`
3. To **update**: delete old + insert new
This is fiddly. A simpler alternative: **don't use external content mode**. Instead, use a **regular FTS5 table** (no content= param) and keep it in sync manually. This way:
- INSERT: just `INSERT INTO fts(uri, cols) VALUES(...)`
- DELETE: `DELETE FROM fts WHERE uri = ...` — wait, FTS5 doesn't support DELETE by column, only by rowid.
OK, the simplest correct approach:
**Use the shadow data table as a lookup for old values, and do explicit FTS delete+insert.**
Let me write the final clean version:
```typescript
import type { SearchPort } from '../ports.ts'
import type { DatabasePort } from '../ports.ts'
export class SQLiteSearchPort implements SearchPort {
constructor(private port: DatabasePort) {}
async indexExists(shadowTable: string): Promise<boolean> {
const rows = await this.port.query(
`SELECT 1 FROM sqlite_master WHERE type='table' AND name=$1`,
[shadowTable],
)
return rows.length > 0
}
async buildIndex(shadowTable: string, sourceQuery: string, searchColumns: string[]): Promise<void> {
await this.port.execute(`DROP TABLE IF EXISTS ${shadowTable}_fts`, [])
await this.port.execute(`DROP TABLE IF EXISTS ${shadowTable}`, [])
await this.port.execute(`CREATE TABLE ${shadowTable} AS ${sourceQuery}`, [])
await this.port.execute(`CREATE UNIQUE INDEX IF NOT EXISTS ${shadowTable}_uri ON ${shadowTable}(uri)`, [])
const colList = searchColumns.join(', ')
await this.port.execute(
`CREATE VIRTUAL TABLE ${shadowTable}_fts USING fts5(uri UNINDEXED, ${colList}, content=${shadowTable}, content_rowid=rowid, tokenize='porter unicode61 remove_diacritics 2')`,
[],
)
const selectCols = ['uri', ...searchColumns].map((c) => `COALESCE(CAST(${c} AS TEXT), '')`)
await this.port.execute(
`INSERT INTO ${shadowTable}_fts (uri, ${colList}) SELECT ${selectCols.join(', ')} FROM ${shadowTable}`,
[],
)
}
async updateIndex(
shadowTable: string,
uri: string,
row: Record<string, string | null>,
searchColumns: string[],
): Promise<void> {
const colList = searchColumns.join(', ')
// Remove old entry from FTS if it exists
await this._deleteFromFts(shadowTable, uri, searchColumns)
// Upsert shadow table
const placeholders = searchColumns.map((_, i) => `$${i + 2}`)
const values = [uri, ...searchColumns.map((c) => row[c] ?? null)]
await this.port.execute(
`INSERT OR REPLACE INTO ${shadowTable} (uri, ${colList}) VALUES ($1, ${placeholders.join(', ')})`,
values,
)
// Insert new FTS entry — read back rowid from shadow table
const rows = await this.port.query(`SELECT rowid FROM ${shadowTable} WHERE uri = $1`, [uri])
if (rows.length > 0) {
const rowid = (rows[0] as any).rowid
const ftsVals = searchColumns.map((c) => row[c] ?? '')
const ftsPlaceholders = searchColumns.map((_, i) => `$${i + 3}`)
await this.port.execute(
`INSERT INTO ${shadowTable}_fts(rowid, uri, ${colList}) VALUES($1, $2, ${ftsPlaceholders.join(', ')})`,
[rowid, uri, ...ftsVals],
)
}
}
async deleteFromIndex(shadowTable: string, uri: string): Promise<void> {
const cols = await this._getShadowColumns(shadowTable)
await this._deleteFromFts(shadowTable, uri, cols)
await this.port.execute(`DELETE FROM ${shadowTable} WHERE uri = $1`, [uri])
}
private async _deleteFromFts(shadowTable: string, uri: string, searchColumns: string[]): Promise<void> {
const colList = searchColumns.join(', ')
const rows = await this.port.query(
`SELECT rowid, uri, ${colList} FROM ${shadowTable} WHERE uri = $1`,
[uri],
)
if (rows.length === 0) return
const old = rows[0] as any
const oldVals = searchColumns.map((c) => old[c] ?? '')
const placeholders = searchColumns.map((_, i) => `$${i + 3}`)
await this.port.execute(
`INSERT INTO ${shadowTable}_fts(${shadowTable}_fts, rowid, uri, ${colList}) VALUES('delete', $1, $2, ${placeholders.join(', ')})`,
[old.rowid, uri, ...oldVals],
)
}
private async _getShadowColumns(shadowTable: string): Promise<string[]> {
const info = await this.port.query(`PRAGMA table_info("${shadowTable}")`, [])
return (info as any[])
.map((r) => r.name)
.filter((n: string) => !['uri', 'cid', 'did', 'indexed_at', 'handle', 'rowid'].includes(n))
}
async search(
shadowTable: string,
query: string,
_searchColumns: string[],
limit: number,
offset: number,
): Promise<Array<{ uri: string; score: number }>> {
const escaped = query.replace(/['"*(){}[\]^~\\:]/g, ' ').trim()
if (!escaped) return []
const sql = `SELECT uri, -bm25(${shadowTable}_fts) AS score
FROM ${shadowTable}_fts
WHERE ${shadowTable}_fts MATCH $1
ORDER BY score DESC
LIMIT $2 OFFSET $3`
return this.port.query(sql, [escaped, limit, offset])
}
}
```
**Step 2: Commit**
```bash
git add packages/hatk/src/database/adapters/sqlite-search.ts
git commit -m "feat: rewrite SQLiteSearchPort for incremental FTS5 with external content"
```
---
### Task 3: Add FTS Row Builder to fts.ts
**Files:**
- Modify: `packages/hatk/src/database/fts.ts`
The existing `buildFtsIndex()` constructs a SQL query to denormalize a record's searchable text. We need a similar function that builds the denormalized row for a **single record** (by URI) so `updateIndex` can be called with the right data.
**Step 1: Add `buildFtsRow()` function**
Add after the `buildFtsIndex` function (after line 192):
```typescript
/**
* Build a denormalized FTS row for a single record.
* Returns the search column values keyed by column name, or null if the record doesn't exist.
*/
export async function buildFtsRow(
collection: string,
uri: string,
): Promise<Record<string, string | null> | null> {
const schema = getSchema(collection)
if (!schema) return null
const lexicon = getLexicon(collection)
const record = lexicon?.defs?.main?.record
const dialect = getSqlDialect()
const selectExprs: string[] = []
const colNames: string[] = []
for (const col of schema.columns) {
if (col.sqlType === 'TEXT') {
selectExprs.push(`t.${col.name}`)
colNames.push(col.name)
} else if ((col.sqlType === 'JSON' || col.sqlType === 'TEXT') && record?.properties) {
const prop = record.properties[col.originalName]
if (prop?.type === 'blob') continue
if (prop && lexicon) {
const derived = jsonSearchColumns(`t.${col.name}`, prop, lexicon, dialect)
if (derived.length > 0) {
for (const d of derived) {
selectExprs.push(`${d.expr} AS ${d.alias}`)
colNames.push(d.alias)
}
continue
}
}
selectExprs.push(`CAST(t.${col.name} AS TEXT) AS ${col.name}`)
colNames.push(col.name)
}
}
for (const child of schema.children) {
for (const col of child.columns) {
if (col.sqlType === 'TEXT') {
const alias = `${child.fieldName}_${col.name}`
const agg = dialect.stringAgg(`c.${col.name}`, "' '")
selectExprs.push(`(SELECT ${agg} FROM ${child.tableName} c WHERE c.parent_uri = t.uri) AS ${alias}`)
colNames.push(alias)
}
}
}
for (const union of schema.unions) {
for (const branch of union.branches) {
for (const col of branch.columns) {
if (col.sqlType === 'TEXT') {
const alias = `${union.fieldName}_${branch.branchName}_${col.name}`
const agg = dialect.stringAgg(`c.${col.name}`, "' '")
selectExprs.push(`(SELECT ${agg} FROM ${branch.tableName} c WHERE c.parent_uri = t.uri) AS ${alias}`)
colNames.push(alias)
}
}
}
}
selectExprs.push('r.handle')
colNames.push('handle')
if (colNames.length === 0) return null
const sql = `SELECT ${selectExprs.join(', ')} FROM ${schema.tableName} t LEFT JOIN _repos r ON t.did = r.did WHERE t.uri = $1`
const rows = await runSQL(sql, [uri])
if (!rows || rows.length === 0) return null
const row = rows[0] as Record<string, any>
const result: Record<string, string | null> = {}
for (const col of colNames) {
result[col] = row[col] != null ? String(row[col]) : null
}
return result
}
```
Also export `runSQL` helper if not already exported — check `db.ts` for the `all()` function. Actually, `runSQL` is already exported from `db.ts`. We import it at top of `fts.ts` already: `import { getSchema, runSQL, getSqlDialect } from './db.ts'`. Good.
But wait — `runSQL` uses the `all()` helper internally? Let me verify. Looking at the imports, `fts.ts` already imports `runSQL` from `db.ts`. We need to confirm `runSQL` returns rows. Looking at `db.ts`, there's `run()` (execute) and `all()` (query). The import says `runSQL` — let me check if that exists.
Actually from the fts.ts imports: `import { getSchema, runSQL, getSqlDialect } from './db.ts'`. And in db.ts there should be an exported `runSQL`. If it doesn't exist as a query function, use the exported `query` or add a wrapper. The implementation should use whatever query function is available from db.ts.
**Step 2: Commit**
```bash
git add packages/hatk/src/database/fts.ts
git commit -m "feat: add buildFtsRow() for single-record FTS denormalization"
```
---
### Task 4: Add updateFtsRecord() and deleteFtsRecord() to fts.ts
**Files:**
- Modify: `packages/hatk/src/database/fts.ts`
**Step 1: Add incremental update/delete functions**
Add after `buildFtsRow()`:
```typescript
/**
* Incrementally update the FTS index for a single record.
* Only works when the search port supports incremental updates (SQLite).
* Falls back to no-op for ports without updateIndex (DuckDB).
*/
export async function updateFtsRecord(collection: string, uri: string): Promise<void> {
if (!searchPort || !searchPort.updateIndex) return
const searchCols = searchColumnCache.get(collection)
if (!searchCols || searchCols.length === 0) return
const row = await buildFtsRow(collection, uri)
if (!row) return
const safeName = ftsTableName(collection)
await searchPort.updateIndex(safeName, uri, row, searchCols)
}
/**
* Incrementally remove a record from the FTS index.
* Only works when the search port supports incremental deletes (SQLite).
*/
export async function deleteFtsRecord(collection: string, uri: string): Promise<void> {
if (!searchPort || !searchPort.deleteFromIndex) return
const safeName = ftsTableName(collection)
await searchPort.deleteFromIndex(safeName, uri)
}
```
**Step 2: Commit**
```bash
git add packages/hatk/src/database/fts.ts
git commit -m "feat: add updateFtsRecord/deleteFtsRecord for incremental FTS"
```
---
### Task 5: Hook Incremental FTS into insertRecord/deleteRecord
**Files:**
- Modify: `packages/hatk/src/database/db.ts:530-657`
**Step 1: Add import**
At the top of `db.ts`, update the import from `fts.ts`:
```typescript
import { getSearchColumns, stripStopWords, getSearchPort, updateFtsRecord, deleteFtsRecord } from './fts.ts'
```
**Step 2: Call updateFtsRecord at the end of insertRecord()**
Add at the end of `insertRecord()` (after the union branch inserts, before the closing `}`):
```typescript
// Incrementally update FTS index for this record
await updateFtsRecord(collection, uri)
```
**Step 3: Call deleteFtsRecord at the start of deleteRecord()**
Add at the beginning of `deleteRecord()`, before deleting child/union rows:
```typescript
// Remove from FTS index before deleting the record data
await deleteFtsRecord(collection, uri)
```
**Step 4: Commit**
```bash
git add packages/hatk/src/database/db.ts
git commit -m "feat: hook incremental FTS updates into insertRecord/deleteRecord"
```
---
### Task 6: Skip Periodic Rebuild for SQLite in Indexer
**Files:**
- Modify: `packages/hatk/src/indexer.ts:33-34,117-121`
**Step 1: Make periodic rebuild conditional on dialect**
Import the database port to check dialect:
```typescript
import { getDatabasePort } from './database/db.ts'
```
Replace the rebuild check in `flushBuffer()` (lines 117-121):
```typescript
writesSinceRebuild += batch.length
if (writesSinceRebuild >= ftsRebuildInterval) {
writesSinceRebuild = 0
// Skip periodic full rebuild for SQLite — it uses incremental FTS updates
const port = getDatabasePort()
if (port.dialect !== 'sqlite') {
rebuildAllIndexes([...indexerCollections]).catch(() => {})
}
}
```
**Step 2: Commit**
```bash
git add packages/hatk/src/indexer.ts
git commit -m "feat: skip periodic FTS rebuild for SQLite (now incremental)"
```
---
### Task 7: Skip Startup Rebuild When FTS Tables Already Exist
**Files:**
- Modify: `packages/hatk/src/database/fts.ts:114-192`
**Step 1: Add early return to buildFtsIndex when tables exist (SQLite only)**
At the top of `buildFtsIndex()`, after the `if (!searchPort) return` check:
```typescript
// For SQLite: skip rebuild if FTS tables already exist (incremental mode keeps them in sync)
if (searchPort.indexExists) {
const safeName = ftsTableName(collection)
const exists = await searchPort.indexExists(safeName)
if (exists) {
// Still populate the search column cache so incremental updates work
// ... (need to compute searchColNames without rebuilding)
}
}
```
Actually, we need the `searchColumnCache` populated even when skipping rebuild. Refactor `buildFtsIndex` to separate column computation from index creation:
```typescript
export async function buildFtsIndex(collection: string): Promise<void> {
if (!searchPort) return
const { searchColNames, sourceQuery, safeName } = computeFtsSchema(collection)
if (searchColNames.length === 0) return
// For incremental ports: skip rebuild if index already exists
if (searchPort.indexExists) {
const exists = await searchPort.indexExists(safeName)
if (exists) {
searchColumnCache.set(collection, searchColNames)
lastRebuiltAt.set(collection, new Date().toISOString())
return
}
}
await searchPort.buildIndex(safeName, sourceQuery, searchColNames)
searchColumnCache.set(collection, searchColNames)
lastRebuiltAt.set(collection, new Date().toISOString())
}
```
Extract the column computation into a helper:
```typescript
function computeFtsSchema(collection: string): {
searchColNames: string[]
sourceQuery: string
safeName: string
} {
const schema = getSchema(collection)
if (!schema) throw new Error(`Unknown collection: ${collection}`)
const lexicon = getLexicon(collection)
const record = lexicon?.defs?.main?.record
const dialect = getSqlDialect()
const selectExprs: string[] = ['t.uri', 't.cid', 't.did', 't.indexed_at']
const searchColNames: string[] = []
// ... (move existing column computation logic from buildFtsIndex here, lines 128-179)
const safeName = ftsTableName(collection)
const sourceQuery = `SELECT ${selectExprs.join(', ')} FROM ${schema.tableName} t LEFT JOIN _repos r ON t.did = r.did`
return { searchColNames, sourceQuery, safeName }
}
```
**Step 2: Commit**
```bash
git add packages/hatk/src/database/fts.ts
git commit -m "feat: skip FTS rebuild on startup when SQLite index already exists"
```
---
### Task 8: Make Backfill Completion Rebuild Conditional
**Files:**
- Modify: `packages/hatk/src/main.ts:168-176`
**Step 1: Skip post-backfill rebuild for SQLite**
```typescript
function runBackfillAndRestart() {
runBackfill(backfillOpts)
.then(async (recordCount) => {
// SQLite uses incremental FTS — only rebuild for other engines
const port = getDatabasePort()
if (port.dialect !== 'sqlite') {
log('[main] Backfill complete, rebuilding FTS indexes...')
await rebuildAllIndexes(collections)
log('[main] FTS indexes ready')
} else {
log('[main] Backfill complete (FTS updated incrementally)')
}
return recordCount
})
```
Add the import at the top of main.ts:
```typescript
import { getDatabasePort } from './database/db.ts'
```
**Step 2: Commit**
```bash
git add packages/hatk/src/main.ts
git commit -m "feat: skip post-backfill FTS rebuild for SQLite"
```
---
### Task 9: Handle Shadow Table Schema for INSERT OR REPLACE
**Files:**
- Modify: `packages/hatk/src/database/adapters/sqlite-search.ts`
The shadow table created by `buildIndex` uses `CREATE TABLE AS SELECT`, which doesn't create a PRIMARY KEY or UNIQUE constraint on `uri`. The `INSERT OR REPLACE` in `updateIndex` needs a unique constraint to work.
**Step 1: Verify buildIndex creates the unique index**
Already handled in Task 2 — the `buildIndex` method includes:
```typescript
await this.port.execute(`CREATE UNIQUE INDEX IF NOT EXISTS ${shadowTable}_uri ON ${shadowTable}(uri)`, [])
```
But `INSERT OR REPLACE` requires a UNIQUE constraint on the table itself, not just an index. Fix by using `INSERT ... ON CONFLICT(uri) DO UPDATE` instead:
In `updateIndex`, replace the shadow table upsert:
```typescript
// Upsert shadow table
const setClauses = searchColumns.map((c, i) => `${c} = $${i + 2}`)
await this.port.execute(
`INSERT INTO ${shadowTable} (uri, ${colList}) VALUES ($1, ${placeholders.join(', ')})
ON CONFLICT(uri) DO UPDATE SET ${setClauses.join(', ')}`,
values,
)
```
This works with the UNIQUE INDEX on uri.
**Step 2: Commit**
```bash
git add packages/hatk/src/database/adapters/sqlite-search.ts
git commit -m "fix: use ON CONFLICT for shadow table upsert"
```
---
### Task 10: Test Manually
**Step 1: Build hatk**
```bash
cd packages/hatk && npm run build
```
**Step 2: Run the teal template app locally**
```bash
cd /Users/chadmiller/code/hatk-template-teal
npm install
# Start local dev environment and verify:
# - FTS tables are created on first startup
# - Records are searchable immediately after insertion (no waiting for rebuild interval)
# - Restarting the app does NOT rebuild FTS tables
# - Search results are correct
```
**Step 3: Commit any fixes**
```bash
git commit -m "fix: address issues found during manual testing"
```