Overview
ICL Cotizaciones uses SQLite with better-sqlite3 for data storage. The database is located at {cwd}/data/icl.db and runs in WAL (Write-Ahead Logging) mode with foreign keys enabled.
Database Configuration
Location and Settings
Database Path: ./data/icl.db (relative to application root)
Configuration (src/db/index.ts:12-15):
const sqlite = new Database(dbPath);
sqlite.pragma("journal_mode = WAL");
sqlite.pragma("foreign_keys = ON");
export default defineConfig({
schema: "./src/db/schema.ts",
out: "./drizzle",
dialect: "sqlite",
dbCredentials: {
url: "./data/icl.db",
},
});
Database Schema
The database contains the following tables:
| Table | Description |
|---|
users | System users with authentication and roles |
clients | Customer records (FFWW, Final, or Both) |
locations | Origin and via locations for shipping |
commercial_agreements | Client pricing and terms |
quotations | Freight quotations and sales records |
pricing_netos | Base freight rates by port and container type |
port_rates | LCL rate schedules by volume |
Schema Migrations
Migration Workflow
Edit the schema definition in src/db/schema.ts.
Example: Adding a new field
export const clients = sqliteTable("clients", {
// ... existing fields
website: text("website"), // new field
});
Create SQL migration files:
This runs drizzle-kit generate and creates timestamped SQL files in drizzle/.
ALTER TABLE clients ADD COLUMN website TEXT;
This runs drizzle-kit migrate and applies changes to data/icl.db.
sqlite3 data/icl.db ".schema clients"
SQLite Limitations
SQLite has limited ALTER TABLE support. Some schema changes require table recreation:
- Adding/removing CHECK constraints
- Modifying column types
- Adding NOT NULL to existing columns
Manual Migration Scripts
The repository includes manual migration scripts for operations that Drizzle/SQLite cannot handle automatically:
Available Scripts
| Script | Command | Description |
|---|
migrate.ts | npx tsx src/db/migrate.ts | Initial schema creation |
migrate-v2.ts | npx tsx src/db/migrate-v2.ts | Schema version 2 changes |
migrate-v3.ts | npx tsx src/db/migrate-v3.ts | Fix CHECK constraint: location_type enum |
migrate-v4.ts | npx tsx src/db/migrate-v4.ts | Add CSV role to users CHECK constraint |
migrate-v5.ts | npx tsx src/db/migrate-v5.ts | Backfill week column for 1049 records |
Running Manual Migrations
npx tsx src/db/migrate-v5.ts
Review console output for success messages:
Backfilling week for 1049 quotations...
Updated 1049 records
sqlite3 data/icl.db "SELECT COUNT(*) FROM quotations WHERE week IS NULL;"
Data Import Scripts
For migrating historical data from AppSheet or Excel:
Import Scripts
| Script | Command | Type | Description |
|---|
import.ts | npx tsx src/db/import.ts | Destructive | Import from XLSX (icldata.xlsx) |
import-cotizaciones.ts | npx tsx src/db/import-cotizaciones.ts | Idempotent | Import quotations from JSON |
link-cotizaciones.ts | npx tsx src/db/link-cotizaciones.ts | Idempotent | Link quotations to clients |
normalize-clients.ts | npx tsx src/db/normalize-clients.ts | Idempotent | Normalize client names to Title Case |
enrich-clients.ts | npx tsx src/db/enrich-clients.ts | Idempotent | Add phone, email, tax_id from JSON |
tag-final-clients.ts | npx tsx src/db/tag-final-clients.ts | Idempotent | Mark Both client types |
import.ts is destructive and clears tables before import. Use only for initial data load.
AppSheet Migration Workflow
Recommended order for importing AppSheet data:
npx tsx src/db/import-cotizaciones.ts
Imports from allcotis.json. Uses INSERT OR IGNORE (safe to re-run).
npx tsx src/db/link-cotizaciones.ts
Matches quotations to clients by name. Creates missing clients.
npx tsx src/db/normalize-clients.ts
Converts names to Title Case (e.g., “ACME SRL” → “Acme SRL”).
npx tsx src/db/enrich-clients.ts
Adds phone, email, user_id, tax_id from CLIENTES.json.
npx tsx src/db/tag-final-clients.ts
Marks clients from CLIENTES_FINALES.json as Both type.
Seed Data
For development and testing:
Running Seed
DESTRUCTIVE: This deletes the existing database and creates a new one.
tsx src/db/seed.ts which:
- Deletes
data/icl.db and WAL files
- Creates fresh database with schema
- Inserts test data:
- 8 users (all password:
icl2026)
- 30 clients
- 20 origin locations
- 20 via locations
- 200 quotations
Seed User Accounts
| Email | Name | Role | Password |
|---|
| [email protected] | Admin ICL | DIRECTOR | icl2026 |
| [email protected] | Maria Lopez | GERENTE | icl2026 |
| [email protected] | Agustina Bosich | COMERCIAL | icl2026 |
| [email protected] | Bruno Gonzalez | COMERCIAL | icl2026 |
| [email protected] | Diego Osman | COMERCIAL | icl2026 |
| [email protected] | Federico Monserrat | COMERCIAL | icl2026 |
| [email protected] | Gonzalo Balena | COMERCIAL | icl2026 |
| [email protected] | Pablo Pascal | COMERCIAL | icl2026 |
Database Backups
Manual Backup
pm2 stop icl-cotizaciones
# or
sudo systemctl stop icl-cotizaciones
cp data/icl.db data/icl.db.backup.$(date +%Y%m%d_%H%M%S)
cp data/icl.db-wal data/icl.db-wal.backup.$(date +%Y%m%d_%H%M%S)
cp data/icl.db-shm data/icl.db-shm.backup.$(date +%Y%m%d_%H%M%S)
sqlite3 data/icl.db.backup.* "PRAGMA integrity_check;"
pm2 start icl-cotizaciones
Automated Backups
Create a backup script at /usr/local/bin/backup-icl-db.sh:
#!/bin/bash
APP_DIR="/opt/iclapp"
BACKUP_DIR="/backups/icl-cotizaciones"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30
mkdir -p "$BACKUP_DIR"
# Create backup using SQLite VACUUM INTO (safe for WAL mode)
sqlite3 "$APP_DIR/data/icl.db" "VACUUM INTO '$BACKUP_DIR/icl.db.$DATE';"
# Compress backup
gzip "$BACKUP_DIR/icl.db.$DATE"
# Remove old backups
find "$BACKUP_DIR" -name "icl.db.*.gz" -mtime +$RETENTION_DAYS -delete
echo "Backup completed: icl.db.$DATE.gz"
sudo chmod +x /usr/local/bin/backup-icl-db.sh
0 2 * * * /usr/local/bin/backup-icl-db.sh >> /var/log/icl-backup.log 2>&1
Restore from Backup
Restoring will overwrite current database. Make a backup first.
pm2 stop icl-cotizaciones
gunzip -c /backups/icl-cotizaciones/icl.db.20260304_020000.gz > data/icl.db.restore
sqlite3 data/icl.db.restore "PRAGMA integrity_check;"
mv data/icl.db data/icl.db.old
mv data/icl.db.restore data/icl.db
rm -f data/icl.db-wal data/icl.db-shm
pm2 start icl-cotizaciones
sqlite3 data/icl.db "SELECT COUNT(*) FROM quotations;"
Database Maintenance
Optimize Database
Run VACUUM to reclaim space and optimize performance:
pm2 stop icl-cotizaciones
sqlite3 data/icl.db "VACUUM;"
sqlite3 data/icl.db "ANALYZE;"
pm2 start icl-cotizaciones
WAL Checkpoint
Manually checkpoint WAL to main database:
sqlite3 data/icl.db "PRAGMA wal_checkpoint(TRUNCATE);"
Check Database Size
Example output:
5.2M data/icl.db
128K data/icl.db-shm
2.1M data/icl.db-wal
Verify Integrity
Run integrity check:
sqlite3 data/icl.db "PRAGMA integrity_check;"
ok
If errors found:
sqlite3 data/icl.db "PRAGMA integrity_check;"
# If corruption detected, restore from backup
Direct Database Access
Using SQLite CLI
SELECT COUNT(*) FROM quotations WHERE status = 'SI';
.mode csv
.output export.csv
SELECT * FROM quotations WHERE date > '2026-01-01';
.quit
Common Queries
Count quotations by status:
SELECT status, COUNT(*) as count
FROM quotations
GROUP BY status;
SELECT id, full_name, email
FROM users
WHERE is_active = 0;
SELECT c.id, c.legal_name
FROM clients c
LEFT JOIN quotations q ON c.id = q.client_id
WHERE q.id IS NULL;
Enable Query Profiling
Log slow queries:
// In src/db/index.ts
sqlite.pragma("query_only = OFF");
sqlite.function("log_query", (query) => {
console.log("[SQL]", query);
});
Add Indexes
For frequently queried columns:
CREATE INDEX idx_quotations_client ON quotations(client_id);
CREATE INDEX idx_quotations_status ON quotations(status);
CREATE INDEX idx_quotations_date ON quotations(date);
CREATE INDEX idx_quotations_user ON quotations(user_id);
sqlite.pragma("wal_autocheckpoint = 1000");
sqlite.pragma("journal_size_limit = 67108864"); // 64MB
Troubleshooting
For database-related issues, see Troubleshooting Guide.