Hello from MCP server
// SQLite schema migration statements for PocketBase collections
// These are idempotent - safe to run multiple times
const v1_statements = [
`CREATE TABLE IF NOT EXISTS appStatus (
id INTEGER PRIMARY KEY AUTOINCREMENT,
key TEXT,
value TEXT
)`,
// userPrefs table - key-value store for user preferences
`CREATE TABLE IF NOT EXISTS userPrefs (
key TEXT PRIMARY KEY,
value TEXT
)`,
// currencies table
`CREATE TABLE IF NOT EXISTS currencies (
id TEXT PRIMARY KEY,
name TEXT,
refId TEXT,
symbol TEXT,
created TEXT,
updated TEXT
)`,
// menuCopy table
`CREATE TABLE IF NOT EXISTS menuCopy (
id TEXT PRIMARY KEY,
name TEXT,
refId TEXT,
org TEXT,
book TEXT,
contentItems TEXT,
created TEXT,
updated TEXT
)`,
// menuTiers table
`CREATE TABLE IF NOT EXISTS menuTiers (
id TEXT PRIMARY KEY,
menus TEXT,
refId TEXT,
org TEXT,
book TEXT,
offers TEXT,
tiers TEXT,
menuCopy TEXT,
contentItems TEXT,
created TEXT,
updated TEXT
)`,
// costsTime table
`CREATE TABLE IF NOT EXISTS costsTime (
id TEXT PRIMARY KEY,
hours REAL,
name TEXT,
org TEXT,
book TEXT,
refId TEXT,
created TEXT,
updated TEXT
)`,
// costsMaterial table
`CREATE TABLE IF NOT EXISTS costsMaterial (
id TEXT PRIMARY KEY,
name TEXT,
quantity REAL,
refId TEXT,
org TEXT,
book TEXT,
created TEXT,
updated TEXT
)`,
// contentItems table
`CREATE TABLE IF NOT EXISTS contentItems (
id TEXT PRIMARY KEY,
name TEXT,
refId TEXT,
org TEXT,
book TEXT,
content TEXT,
created TEXT,
updated TEXT
)`,
// books table
`CREATE TABLE IF NOT EXISTS books (
id TEXT PRIMARY KEY,
name TEXT,
org TEXT,
book TEXT,
parent TEXT,
refId TEXT,
created TEXT,
updated TEXT
)`,
// menus table
`CREATE TABLE IF NOT EXISTS menus (
id TEXT PRIMARY KEY,
name TEXT,
org TEXT,
book TEXT,
books TEXT,
refId TEXT,
created TEXT,
updated TEXT
)`,
// offers table
`CREATE TABLE IF NOT EXISTS offers (
id TEXT PRIMARY KEY,
name TEXT,
org TEXT,
book TEXT,
menus TEXT,
costsTime TEXT,
costsMaterial TEXT,
multiplier TEXT,
refId TEXT,
created TEXT,
updated TEXT
)`,
// tiers table
`CREATE TABLE IF NOT EXISTS tiers (
id TEXT PRIMARY KEY,
name TEXT,
rank REAL,
refId TEXT,
org TEXT,
book TEXT,
warrantyCopy TEXT,
created TEXT,
updated TEXT
)`,
// problems table
`CREATE TABLE IF NOT EXISTS problems (
id TEXT PRIMARY KEY,
name TEXT,
description TEXT,
org TEXT,
book TEXT,
menus TEXT,
problemTags TEXT,
problemCategories TEXT,
refId TEXT,
created TEXT,
updated TEXT
)`,
// problemTags table
`CREATE TABLE IF NOT EXISTS problemTags (
id TEXT PRIMARY KEY,
refId TEXT,
name TEXT,
org TEXT,
book TEXT,
created TEXT,
updated TEXT
)`,
// problemCategories table - hierarchical categories for problems
// parent field creates a directed graph from top-level down to sub-categories
`CREATE TABLE IF NOT EXISTS problemCategories (
id TEXT PRIMARY KEY,
refId TEXT,
name TEXT,
parent TEXT,
org TEXT,
book TEXT,
created TEXT,
updated TEXT
)`,
// organizations table
`CREATE TABLE IF NOT EXISTS organizations (
id TEXT PRIMARY KEY,
name TEXT,
org TEXT,
owner TEXT,
currency TEXT,
variables TEXT,
created TEXT,
updated TEXT
)`,
`CREATE TABLE IF NOT EXISTS pricebookChanges (
id INTEGER PRIMARY KEY AUTOINCREMENT,
book TEXT,
changeset TEXT,
status TEXT DEFAULT 'success',
error_message TEXT,
created DATETIME DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS currencyRates (
id INTEGER PRIMARY KEY AUTOINCREMENT,
baseCurrency TEXT,
quoteCurrency TEXT,
rate TEXT,
name TEXT,
created TEXT
)`,
`CREATE TABLE IF NOT EXISTS logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
log_type TEXT NOT NULL,
log_data TEXT NOT NULL, -- JSON stored as string
created_by TEXT NOT NULL, -- e.g. user ID, system ID
created_by_name TEXT NOT NULL -- human-readable name
);
`,
`CREATE TABLE if not exists sessions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
state TEXT,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
updated DATETIME DEFAULT CURRENT_TIMESTAMP
);
`,
`CREATE TRIGGER update_timestamp
AFTER UPDATE ON sessions
FOR EACH ROW
BEGIN
UPDATE sessions
SET updated = CURRENT_TIMESTAMP
WHERE id = OLD.id;
END;
`,
// changeMessages table - stores processed changesets for review
`CREATE TABLE IF NOT EXISTS changeMessages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pocketbase_id TEXT,
book TEXT,
org TEXT,
changeset TEXT,
status TEXT DEFAULT 'pending',
error_message TEXT,
processed_at DATETIME,
created DATETIME DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE INDEX IF NOT EXISTS idx_changeMessages_book ON changeMessages (book)`,
`CREATE INDEX IF NOT EXISTS idx_changeMessages_status ON changeMessages (status)`,
// warrantyCopy table
`CREATE TABLE IF NOT EXISTS warrantyCopy (
id TEXT PRIMARY KEY,
name TEXT,
refId TEXT,
org TEXT,
contentItems TEXT,
book TEXT,
created TEXT,
updated TEXT
)`,
// Create unique indexes (these will be ignored if they already exist)
//
`CREATE UNIQUE INDEX IF NOT EXISTS idx_HP8sxq73W4 ON menus (refId, org)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_HP8sxq73W5 ON appStatus (key)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_HP8sxq73Wo ON menuCopy (refId, org)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_HP8sxq73W6 ON currencyRates (quoteCurrency)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_N91TFEopK5 ON menuTiers (refId, org)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_bQGELhzt8l ON costsTime (org, refId)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_Ge3s7XBN1N ON costsMaterial (refId, org)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_SVun81Mdbz ON contentItems (org, refId)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_zw2ku80SuD ON offers (org, refId)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_kRQrbkQNxa ON tiers (refId, org)`,
`CREATE UNIQUE INDEX IF NOT EXISTS idx_warrantyCopy_refId_org ON warrantyCopy (refId, org)`,
];
import { SQLiteDBConnection } from "@capacitor-community/sqlite";
// v2: Add techHandbook column to offers table for tech handbook contentItems
// Using a workaround since SQLite ALTER TABLE ADD COLUMN doesn't support IF NOT EXISTS
// We check if column exists before adding
async function applyV2Migration(db: SQLiteDBConnection) {
// Check if techHandbook column already exists
const tableInfo = await db.query(`PRAGMA table_info(offers)`);
const columns = tableInfo.values?.map((row: any) => row.name) || [];
if (!columns.includes("techHandbook")) {
await db.execute(`ALTER TABLE offers ADD COLUMN techHandbook TEXT`);
}
}
// v3: Add changeset, status, and error_message columns to pricebookChanges table
async function applyV3Migration(db: SQLiteDBConnection) {
const tableInfo = await db.query(`PRAGMA table_info(pricebookChanges)`);
const columns = tableInfo.values?.map((row: any) => row.name) || [];
if (!columns.includes("changeset")) {
await db.execute(`ALTER TABLE pricebookChanges ADD COLUMN changeset TEXT`);
}
if (!columns.includes("status")) {
await db.execute(
`ALTER TABLE pricebookChanges ADD COLUMN status TEXT DEFAULT 'success'`,
);
}
if (!columns.includes("error_message")) {
await db.execute(
`ALTER TABLE pricebookChanges ADD COLUMN error_message TEXT`,
);
}
}
async function applyMigrations(
db: SQLiteDBConnection,
migrationStatements: string[],
) {
for (const statement of migrationStatements) {
try {
await db.execute(statement);
} catch (error) {
console.error(
"Error executing statement:",
statement.split("\n")[0] + "...",
);
console.error(error);
throw error;
}
}
}
async function runMigrations(db: SQLiteDBConnection) {
const version = (await db.query("pragma user_version"))?.values![0]
.user_version;
// v1 migration
if (version < 1) {
try {
await applyMigrations(db, v1_statements);
await db.execute(`pragma user_version = 1;`);
} catch (e) {
console.error("v1 migration error:", e);
}
}
// v2 migration - uses custom function to handle idempotent column addition
if (version < 2) {
try {
await applyV2Migration(db);
await db.execute(`pragma user_version = 2;`);
} catch (e) {
console.error("v2 migration error:", e);
}
}
// v3 migration - add changeset columns to pricebookChanges table
if (version < 3) {
try {
await applyV3Migration(db);
await db.execute(`pragma user_version = 3;`);
} catch (e) {
console.error("v3 migration error:", e);
}
}
// v4 migration - ensure problemCategories table exists (was added to v1 after some devices already migrated)
if (version < 4) {
try {
await db.execute(`CREATE TABLE IF NOT EXISTS problemCategories (
id TEXT PRIMARY KEY,
refId TEXT,
name TEXT,
parent TEXT,
org TEXT,
book TEXT,
created TEXT,
updated TEXT
)`);
await db.execute(`pragma user_version = 4;`);
} catch (e) {
console.error("v4 migration error:", e);
}
}
const finalVersion = (await db.query("pragma user_version"))?.values![0]
.user_version;
console.log("Database version:", finalVersion);
console.log("All migrations completed successfully");
}
export { runMigrations };