# Telegram Task Earning

## Phase 04 — MySQL Database Foundation Report

| Field                        | Result                         |
| ---------------------------- | ------------------------------ |
| Status                       | **Complete**                   |
| Completed                    | 2026-09-03                     |
| Project version              | `0.4.0`                        |
| Database target              | Oracle MySQL 8.0.34+ / 8.4 LTS |
| Domain tables                | 71                             |
| Migration metadata tables    | 4                              |
| Reconciliation views         | 2                              |
| Ordered migrations           | 14                             |
| Idempotent seeds             | 6                              |
| Named constraints            | 155                            |
| Migration up/down statements | 85 / 86                        |
| Seed statements              | 19                             |
| Automated tests              | 40 passed                      |
| Required layout              | 77 directories / 73 files      |
| npm scripts                  | 29                             |
| Dependency audit             | 0 vulnerabilities              |

## Outcome

Phase 4 creates the complete relational foundation for identity, RBAC, users, settings, tasks, referrals, wallet ledger, withdrawals, free rewards, content, Telegram messages, reliable jobs, risk/audit evidence, analytics, and leaderboards.

The migration, rollback, seed, status, dry-run, capability-check, query, transaction, and pagination foundations are operational. Business services remain deferred to their owning phases.

## Financial integrity

- No `users.balance` column exists.
- Wallet truth is an append-only double-entry ledger.
- All amounts are `BIGINT` minor units, never floating-point values.
- MySQL big integers and decimals remain strings at the JavaScript boundary.
- Task, referral, free-reward, hold, payout, refund, adjustment, and reversal transactions have explicit types.
- Unique idempotency/event constraints protect duplicate financial writes.
- Posted corrections use linked reversals rather than editing entries.
- Reconciliation views expose unbalanced transactions and rebuild account balances.

The later wallet service must lock relevant accounts, validate sufficient available funds, create balanced entries, and post status in one transaction.

## Identity and timestamp integrity

- Telegram IDs use `BIGINT UNSIGNED` and are handled as strings.
- Public/API IDs are opaque 26-character identifiers.
- Application connections force UTC and strict SQL mode.
- Server/database timestamps, never device time, govern reward and withdrawal eligibility.

## Migration reliability

The runner uses an advisory lock, immutable SHA-256 file checksums, contiguous versions, per-statement progress, explicit status, and latest-only rollback. Embedded `USE`, transaction control, source commands, delimiter changes, and foreign-key-check disabling are rejected.

This design acknowledges MySQL implicit DDL commits instead of claiming that schema changes are fully transactionally reversible.

## Safe defaults

- Withdrawals remain disabled until methods and business rules are configured.
- Referral reward amounts default to zero.
- Reminder and broadcast features remain disabled.
- Bot templates are drafts until reviewed.
- One BDT settlement currency and required system ledger accounts are seeded.
- Default permissions and roles are inserted without creating an administrator.
- Mystery Box is disabled and permanently free-only at the database constraint layer.
- Seeds never overwrite operator settings.

## Verification scope

Automated checks cover SQL parsing, ordered files, checksums, table/constraint inventory, money types, Telegram ID types, free-only safeguards, seed behavior, CLI dry runs, migration locking, idempotent reruns, partial-statement resume, immutable-file rejection, rollback, seed invalidation, strict MySQL version/session behavior, transaction rollback/deadlock retry, identifier allowlisting, and pagination limits.

The final local quality gate passed formatting, linting, all 40 automated tests, the 77-key environment contract, database schema verification, CSS builds, and the 77-directory/73-file required project layout. Full and production-only dependency audits both reported zero known vulnerabilities, and a clean-install dry run succeeded.

The deterministic SHA-256 digest of the sorted migration-and-seed checksum manifest is `2c057c0fdd0b2766946a92d72ac878af6b853a017fcb9e9f30d2344682816b92`.

Live migration against a deployment database is intentionally not performed without operator-provided deployment credentials. `npm run database:check`, `npm run migrate`, and `npm run seed` are ready for that controlled environment.

## Phase 5 handoff

Phase 5 can build the Express application bootstrap using the validated configuration and database pool. It should add graceful shutdown, request logging, central errors, health/readiness endpoints, connection lifecycle, and a minimal fail-closed web process without prematurely implementing authentication or business routes.

**Phase 4 status: COMPLETE AND VERIFIED.**