CLI Scripts and DB Maintenance
Relevant source files
The following files were used as context for generating this wiki page:
Introduction
The Syllogic personal finance app utilizes a suite of CLI scripts and maintenance tools to manage environment orchestration, database migrations, and real-time system monitoring. These tools bridge the gap between the Next.js frontend and Python backend, ensuring a unified infrastructure state across local development, Docker-based deployments, and cloud platforms like Railway.
The maintenance system focuses on three primary areas: deployment automation through shell scripts, robust database migration logic using Drizzle ORM, and diagnostic visibility via a Streamlit-based monitoring application. These components work together to manage the shared PostgreSQL database, which serves as the single source of truth for both services.
Sources: CLAUDE.md, README.md
Deployment and Orchestration Scripts
The project provides several entry-point scripts to automate the setup of the two-service architecture. These scripts handle environment variable configuration, container lifecycle management, and initial data seeding.
Development and Production Up-Scripts
dev-up.sh: Orchestrates local development environments. It supports a--localflag to run the database and Redis in Docker while allowing the application code to run natively on the host.prod-up.sh: Deploys the full stack using prebuilt images from the GitHub Container Registry (GHCR).install.sh: A root-level installation script for Linux environments that installs Docker dependencies, generates secure random secrets (e.g.,POSTGRES_PASSWORD), and configures Caddy for TLS.
Sources: README.md, deploy/install/install.sh:24-85
Orchestration Flow
The following diagram illustrates how the installation and startup scripts initialize the environment:
The installation process automates dependency checks and secret generation to ensure a "one-command" setup experience.
Sources: deploy/install/install.sh:30-110, deploy/compose/docker-compose.yml:37-48
Database Migration System
Database schema management is handled by Drizzle ORM on the frontend and mirrored by SQLAlchemy on the backend. Production-safe migrations are executed via a specialized Node.js script.
Migration Runner (migrate.js)
The migrate.js script is a plain Node.js utility designed to run inside production containers without requiring development dependencies like ts-node. It performs several critical safety checks:
- Tracking Table Initialization: Ensures the
drizzle.__drizzle_migrationstable exists to track applied patches. - Baseline Logic: If an existing schema is detected without tracking metadata, it can "baseline" the first migration to prevent "relation already exists" errors.
- Postgres Error Handling: Specifically handles and ignores "duplicate_schema" (42P06) and "unique_violation" (23505) errors that can occur during racy schema creation.
Sources: frontend/scripts/migrate.js:18-70, frontend/scripts/migrate.js:134-150
Migration Components
| Component | Description | Source |
|---|---|---|
migrate.js | Production migration runner using postgres-js. | frontend/scripts/migrate.js |
drizzle-kit | Used in development for generating and pushing schema changes. | frontend/package.json:11-14 |
syllogic-migrate | A short-lived Docker container that runs migrations before the app boots. | deploy/compose/docker-compose.yml:37-48 |
Database Monitoring
Diagnostic visibility is provided through a Streamlit application located in the backend migration tools. This tool provides a read-only (and occasionally interactive) view of the shared database.
Monitor DB Functionality
The monitor_db.py script connects to the PostgreSQL instance using SQLAlchemy and presents data through a web interface. It organizes information into several key tabs:
- Table Statistics: Real-time counts of records in
users,accounts,transactions, etc. - Auth Accounts: Visibility into BetterAuth sessions and token expiration warnings.
- Exchange Rates: Monitoring of synced currency pairs and historical rate distributions.
- Subscription Suggestions: Insights into AI-detected recurring payments and confidence scores.
Sources: backend/postgres_migration/monitor_db.py:92-120, backend/postgres_migration/monitor_db.py:210-250, backend/postgres_migration/monitor_db.py:800-850
Diagnostic Data Flow
The monitor ensures developers can verify the state of AI enrichment and background job results without direct SQL access.
Sources: backend/postgres_migration/monitor_db.py:48-75, backend/postgres_migration/monitor_db.py:530-580
Maintenance Commands Reference
The following table summarizes common maintenance tasks performed via the CLI:
| Task | Command | Environment |
|---|---|---|
| Start Local Infra | ./scripts/dev-up.sh --local | Local |
| Manual Migration | DATABASE_URL=... node scripts/migrate.js | Production/CI |
| Database GUI | pnpm db:studio | Development |
| Monitor DB UI | streamlit run postgres_migration/monitor_db.py | Backend Dev |
| Reset Balances | deleteAllTransactionsAndResetBalances() (Server Action) | App UI |
Sources: README.md, frontend/package.json:14, frontend/lib/actions/settings.ts:89-130
Conclusion
The Syllogic CLI and maintenance ecosystem is designed to manage a complex two-service architecture with a shared data layer. By utilizing a combination of shell orchestration, idempotent Node.js migration scripts, and Streamlit-based monitoring, the project maintains high technical accuracy and operational visibility across diverse deployment targets. All schema changes originate in Drizzle and are propagated through the migrate.js utility, ensuring the PostgreSQL database remains synchronized with both the Next.js and FastAPI services.