# Production SQL Schema Upgrade Runbook

This runbook upgrades **MariaDB/MySQL** production databases from the legacy schema (pre access-control) to the current `team-tracker-postgre` schema **without deleting existing data**.

All SQL files live in [`scripts/sql/`](./sql/).

**Recommended (single import):** [`99-full-schema-sync.sql`](./sql/99-full-schema-sync.sql) — syncs all schema changes in one file. See [One-file upgrade](#one-file-upgrade-cpanel) below.

---

## Before you start

1. **Full backup** — cPanel → Backup Wizard, or phpMyAdmin → Export → all tables (structure + data). Download and store off-server.
2. **Staging database** — Create `rinocfzp_team_management_staging` in cPanel → MySQL Databases. Import your latest production `.sql` dump via phpMyAdmin.
3. **Rehearse on staging first** — Complete every step below on staging before touching production.

---

## phpMyAdmin execution order

Select your database → **SQL** tab → paste each file contents → **Go**.

**First time on cPanel:** run [`00b-cpanel-helpers.sql`](./sql/00b-cpanel-helpers.sql) with phpMyAdmin **Delimiter** set to `$$`, then reset delimiter to `;`.

| Step | File | Purpose |
|------|------|---------|
| 0 | [`00-preflight-checks.sql`](./sql/00-preflight-checks.sql) | Save row counts + migration list |
| 0b | [`00b-cpanel-helpers.sql`](./sql/00b-cpanel-helpers.sql) | **Once** — install `cp_safe_exec` (set phpMyAdmin delimiter to `$$`) |
| 1 | [`01-baseline-migrations.sql`](./sql/01-baseline-migrations.sql) | Mark InitialSchema/NewDatabaseConnection as applied |
| 2 | [`02-access-control-schema.sql`](./sql/02-access-control-schema.sql) | Create `access_roles`, `role_permissions` |
| 3 | [`03-member-access-role.sql`](./sql/03-member-access-role.sql) | Add `members.access_role_id` |
| 4 | **Seed roles** (see below) | Populate `access_roles` + `role_permissions` |
| 5 | [`04-backfill-member-roles.sql`](./sql/04-backfill-member-roles.sql) | Link members to access roles |
| 6 | [`05-permission-scope-updates.sql`](./sql/05-permission-scope-updates.sql) | Apply scope value migrations |
| 7 | [`06-post-migration-verify.sql`](./sql/06-post-migration-verify.sql) | Confirm access-control upgrade |
| 8 | [`07-work-schedule-timezone.sql`](./sql/07-work-schedule-timezone.sql) | Add `working_schedule.timezone` |
| 9 | [`08-notice-members.sql`](./sql/08-notice-members.sql) | Create `_noticemembers` table |
| 10 | [`09-leave-document.sql`](./sql/09-leave-document.sql) | Add `leaves.document` |
| 11 | [`10-attendance-checkout-note.sql`](./sql/10-attendance-checkout-note.sql) | Add `attendances.checkout_note` |
| 12 | [`11-attendance-requests.sql`](./sql/11-attendance-requests.sql) | Create `attendance_requests` table |
| 13 | [`12-attendance-request-permissions.sql`](./sql/12-attendance-request-permissions.sql) | Attendance request permissions |
| 14 | [`13-work-schedule-correction-limit.sql`](./sql/13-work-schedule-correction-limit.sql) | Monthly correction limit |
| 15 | [`15-leave-type-day-off.sql`](./sql/15-leave-type-day-off.sql) | Add `day_off` leave type |
| 16 | [`14-post-schema-verify.sql`](./sql/14-post-schema-verify.sql) | Final schema verification |

**Already upgraded through step 7?** If access control is done, run steps **8–16** only (`07` through `15` files, then verify).

---

## One-file upgrade (cPanel)

Use [`99-full-schema-sync.sql`](./sql/99-full-schema-sync.sql) instead of steps 0–15 when you want one import:

1. Full backup
2. phpMyAdmin → SQL → set **Delimiter** to `$$`
3. Paste the entire `99-full-schema-sync.sql` file → **Go**
4. Set Delimiter back to `;`
5. Owner login once (seeds `access_roles`), then re-run Section 6 backfill from the file if `members_missing_access_role_id` > 0
6. Deploy matching backend + frontend

Safe to re-run. Does **not** drop tables, drop columns, or delete data.

---

## Step 4 — Seed access roles (choose one)

### Option A — App login (phpMyAdmin-only workflow)

1. Point staging/production API `DATABASE_URL` at the target database.
2. Deploy the current backend build.
3. Owner sign-in + OTP verify (triggers `ensureDefaultRoles`).
4. Verify in phpMyAdmin:

```sql
SELECT slug, name FROM access_roles;
SELECT COUNT(*) FROM role_permissions;
```

### Option B — CLI seed script (if SSH or local with DATABASE_URL)

```bash
cd team-tracker-postgre
# Set DATABASE_URL to staging or production connection string
npm run seed:access-roles
```

Then run [`04-backfill-member-roles.sql`](./sql/04-backfill-member-roles.sql) in phpMyAdmin.

---

## Staging verification checklist

- [ ] `members_count`, `attendances_count`, `leaves_count` match pre-flight snapshot
- [ ] `access_roles` has 3 rows per owner (owner, admin, member)
- [ ] `members_missing_access_role_id` = **0**
- [ ] Migrations table includes names through `LeaveTypeDayOff1781040000000`
- [ ] `leaves.leave_type` enum includes `day_off` (SHOW COLUMNS)
- [ ] `attendance_requests` table exists
- [ ] `working_schedule.monthly_correction_limit` column exists (default 2)
- [ ] `attendances.checkout_note` column exists
- [ ] Owner login works
- [ ] Member login works
- [ ] Dashboard attendance check-in works
- [ ] Attendance requests page loads (admin + member)
- [ ] Members list loads
- [ ] Permissions / Access Control page loads

---

## Production cutover (maintenance window)

1. Announce 15–30 minute maintenance.
2. **Fresh production backup** (new export — not the Jun 15 file if data changed since).
3. Optional: stop app or show maintenance page.
4. Run steps **0 → 16** on production DB in phpMyAdmin (same as staging).
   - If access control is already applied, run steps **8 → 16** only (`07`–`15` SQL files, then verify).
5. Deploy new backend + frontend.
6. Owner login → confirm roles seeded (or run `npm run seed:access-roles` if SSH available).
7. Re-run [`04-backfill-member-roles.sql`](./sql/04-backfill-member-roles.sql) if needed.
8. Re-run [`12-attendance-request-permissions.sql`](./sql/12-attendance-request-permissions.sql) if roles were seeded after step 13.
9. Run [`14-post-schema-verify.sql`](./sql/14-post-schema-verify.sql).
10. Smoke test critical flows.
11. Remove maintenance mode.

---

## Rollback

If anything fails after schema changes:

1. Stop the application.
2. Rename or drop the broken database.
3. Create empty database with the **original name**.
4. Import the **pre-migration backup** from step 2 above.
5. Redeploy the **previous** app version.

Do **not** use `npm run migration:revert` on production if you only use phpMyAdmin.

---

## What changes vs what stays safe

| Changes | Unchanged |
|---------|-----------|
| New tables: `access_roles`, `role_permissions`, `_noticemembers`, `attendance_requests` | All member rows |
| New columns: `members.access_role_id`, `working_schedule.timezone`, `working_schedule.monthly_correction_limit`, `leaves.document`, `attendances.checkout_note` | Attendance, leave, notice **data** rows |
| Extended enum: `leaves.leave_type` adds `day_off` | Existing leave row values (`annual`, `sick`, etc.) |
| New rows in `role_permissions` | Passwords, uploads, files on disk |
| Migration history rows added | Existing `owner`, `company` data |

---

## Local rehearsal (optional)

If you have MySQL/MariaDB locally:

```bash
mysql -u USER -p -e "CREATE DATABASE IF NOT EXISTS team_management_staging;"
mysql -u USER -p team_management_staging < /path/to/production-backup.sql
# Run each scripts/sql/*.sql file in order via mysql CLI or phpMyAdmin
DATABASE_URL="mysql://USER:PASS@localhost:3306/team_management_staging" npm run seed:access-roles
mysql -u USER -p team_management_staging < scripts/sql/04-backfill-member-roles.sql
mysql -u USER -p team_management_staging < scripts/sql/05-permission-scope-updates.sql
mysql -u USER -p team_management_staging < scripts/sql/06-post-migration-verify.sql
```