adamp
7068ea354e
P0 fixes: - Fix OrderDetail product change overwriting product_id due to React state batching (single setItems call now updates both fields) - Validate all :id route params via parseId helper; return 400 for invalid IDs instead of passing raw strings to SQLite - Product/customer delete now checks for references first, returns 409 Conflict instead of letting FK constraint produce 500 P1 fixes: - Disallow quantity_on_hand in product PUT so all stock changes go through PATCH /stock (preserves audit trail) - Add global Express error handler and unhandledRejection listener P2 fixes: - Validate report date params (YYYY-MM-DD format) and stock-history limit (positive integer, capped at 1000) - Add jsonSafe() helper to api.js for safe 204 handling - OrderNew setSubmitting now runs in finally block - Login shows specific message for 429 rate limit, generic message for other auth failures P3 fixes: - Replace brittle try/catch ALTER TABLE with schema_version migration table and versioned migrations - Fix OrderDetail useEffect missing dependency (useCallback + [load]) Also: expanded README with full production deployment instructions (PM2, nginx, backups) Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
107 lines
3.0 KiB
JavaScript
107 lines
3.0 KiB
JavaScript
const Database = require('better-sqlite3');
|
|
const path = require('path');
|
|
|
|
const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '..', 'data', 'cookies.db');
|
|
|
|
let db;
|
|
|
|
function getDb() {
|
|
if (!db) {
|
|
const dir = path.dirname(dbPath);
|
|
const fs = require('fs');
|
|
if (!fs.existsSync(dir)) {
|
|
fs.mkdirSync(dir, { recursive: true });
|
|
}
|
|
db = new Database(dbPath);
|
|
db.pragma('foreign_keys = ON');
|
|
initSchema(db);
|
|
}
|
|
return db;
|
|
}
|
|
|
|
function initSchema(database) {
|
|
database.exec(`
|
|
CREATE TABLE IF NOT EXISTS products (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
price REAL NOT NULL DEFAULT 0,
|
|
quantity_on_hand INTEGER NOT NULL DEFAULT 0,
|
|
low_stock_threshold INTEGER DEFAULT 0,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS customers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
phone TEXT,
|
|
email TEXT,
|
|
address TEXT,
|
|
notes TEXT,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
customer_id INTEGER REFERENCES customers(id),
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
notes TEXT,
|
|
created_at TEXT DEFAULT (datetime('now')),
|
|
updated_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS order_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
product_id INTEGER NOT NULL REFERENCES products(id),
|
|
quantity INTEGER NOT NULL,
|
|
price_at_sale REAL NOT NULL,
|
|
UNIQUE(order_id, product_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS stock_adjustments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
product_id INTEGER NOT NULL REFERENCES products(id),
|
|
adjustment INTEGER NOT NULL,
|
|
reason TEXT NOT NULL,
|
|
reference_id INTEGER,
|
|
created_at TEXT DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS schema_version (
|
|
version INTEGER NOT NULL
|
|
);
|
|
`);
|
|
|
|
runMigrations(database);
|
|
}
|
|
|
|
function getSchemaVersion(database) {
|
|
const row = database.prepare('SELECT version FROM schema_version').get();
|
|
return row ? row.version : 0;
|
|
}
|
|
|
|
function setSchemaVersion(database, version) {
|
|
const existing = database.prepare('SELECT version FROM schema_version').get();
|
|
if (existing) {
|
|
database.prepare('UPDATE schema_version SET version = ?').run(version);
|
|
} else {
|
|
database.prepare('INSERT INTO schema_version (version) VALUES (?)').run(version);
|
|
}
|
|
}
|
|
|
|
function runMigrations(database) {
|
|
const version = getSchemaVersion(database);
|
|
|
|
if (version < 1) {
|
|
try { database.exec("ALTER TABLE orders ADD COLUMN payment_method TEXT"); } catch (e) {
|
|
if (!e.message.includes('duplicate column')) throw e;
|
|
}
|
|
try { database.exec("ALTER TABLE orders ADD COLUMN amount_paid REAL NOT NULL DEFAULT 0"); } catch (e) {
|
|
if (!e.message.includes('duplicate column')) throw e;
|
|
}
|
|
setSchemaVersion(database, 1);
|
|
}
|
|
}
|
|
|
|
module.exports = { getDb, initSchema };
|