jobtrackingapp/deploy/MARIADB.md

MariaDB production bootstrap

What this app supports

• SQLite for local/dev fallback
• MariaDB/MySQL for production when configured with:
  • DATABASE_PROVIDER=mariadb
  • JOBTRACKER_CONNECTION_STRING=...

1. Create database and user in MariaDB

Run this against your MariaDB server/container:

CREATE DATABASE jobtracker CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'jobtracker'@'%' IDENTIFIED BY 'REPLACE_WITH_STRONG_PASSWORD';
GRANT ALL PRIVILEGES ON jobtracker.* TO 'jobtracker'@'%';
FLUSH PRIVILEGES;

2. Configure server .env

Example:

DATABASE_PROVIDER=mariadb
JOBTRACKER_CONNECTION_STRING=server=mariadb;port=3306;database=jobtracker;user=jobtracker;password=REPLACE_WITH_STRONG_PASSWORD
AUTH_JWT_KEY=replace_with_long_random_secret
AUTH_ADMIN_EMAIL=you@example.com
AUTH_ADMIN_PASSWORD=replace_with_strong_password
APP_PUBLIC_BASE_URL=https://your-domain.example
SUMMARIZER_BASE_URL=http://summarizer:8001

3. First startup

On first deploy, the API will run EF migrations at startup. That means the MariaDB schema is created automatically as long as:

• the DB exists
• credentials are correct
• the backend container can reach mariadb

4. SQLite to MariaDB migration notes

This repo now includes helper tooling to make migration safer:

• deploy/sqlite_export.py exports the important SQLite tables into JSON
• deploy/sqlite_preview.py lets you inspect the exported payload quickly

Suggested migration path:

1. Stop writes to the app
2. Back up SQLite DB file
3. Export SQLite data:
   • python deploy/sqlite_export.py /path/to/jobtracker.db sqlite-export.json
4. Preview the export:
   • python deploy/sqlite_preview.py sqlite-export.json
5. Start MariaDB-backed environment on a staging copy
6. Import with a one-time script/manual loader tailored to your production data
7. Validate users, companies, jobs, correspondence, attachments metadata
8. Switch production .env to MariaDB

Tables you would likely want to migrate

• AspNetUsers
• AspNetRoles
• AspNetUserRoles
• Companies
• JobApplications
• Correspondences
• Attachments
• JobEvents
• GmailConnections
• UserRuleSettings

Important note on attachments

Files are stored separately from DB rows. When moving environments, make sure you preserve:

• /data
• especially attachments and data-protection keys

Suggested validation checklist after switching

• can log in with admin user
• profile loads
• jobs list loads
• correspondence loads
• attachments metadata still matches files on disk
• summarizer works
• reminders page works
• admin/system page loads