Database operations for Digital Delta — schema changes, seeding, resetting, migrations, and SQLite queries against the disaster logistics database.
cd backend
npm run db:seed # Initialize with sample data
npm run db:reset # Drop all tables and recreate
npm run ml:generate-data # Generate synthetic rainfall CSV
npm run ml:train # Train flood prediction model
backend/data/digital_delta.sqlite — WAL mode, foreign keys ON
backend/src/db/schema.sql)| Table | Purpose |
|---|---|
| nodes | 6 relief centers with lat/lng |
| edges | 7 transport routes (road/waterway/airway) with weights, flood status, ML risk |
| users | Device-based auth with roles, public keys, TOTP secrets |
| supplies | Inventory items per node with priority (P0-P3) and vector clocks |
| deliveries | Cargo in transit with vehicle type, status, SLA deadline |
| audit_log | Tamper-evident log with hash chaining |
| sync_state | CRDT vector clocks per device |
| pod_receipts | Cryptographic proof-of-delivery with signatures |
| mesh_messages | Store-and-forward relay messages with TTL |
const { getDb } = require('../db/connection');
const db = getDb(); // Singleton — never create new connections
// Query examples
const nodes = db.prepare('SELECT * FROM nodes').all();
const node = db.prepare('SELECT * FROM nodes WHERE id = ?').get('sylhet-hub');
db.prepare('UPDATE edges SET is_flooded = ? WHERE id = ?').run(1, 'e2');
backend/src/db/schema.sqlbackend/src/db/seed.js if new tables need initial databackend/src/db/reset.js to drop new tablesnpm run db:reset && npm run db:seednode -e "const {getDb}=require('./src/db/connection'); console.log(getDb().prepare('SELECT name FROM sqlite_master WHERE type=\"table\"').all())"backend/src/db/schema.sqlbackend/src/db/connection.jsbackend/src/db/seed.jsbackend/src/db/reset.js