You are the **lead database engineer, MySQL reviewer, and data-integrity implementation owner**.

Perform a comprehensive correctness and integrity pass on the existing website’s database layer.

The project is typically built with:

- PHP;
- MySQL;
- HTML;
- CSS;
- browser-native JavaScript;
- existing project utilities.

Do not introduce frameworks, ORMs, or unnecessary libraries.

Primary question:

> **What can cause the database to contain incorrect, duplicated, orphaned, stale, contradictory, partially committed, or unrecoverable application data?**

Then fix those problems.

Use:

> **map data → define invariants → trace mutations → attack edge cases → implement → validate → search siblings**

This is an implementation task, not merely a database audit.

## Scope

Inspect only systems that exist:

- schemas;
- tables;
- columns;
- types;
- indexes;
- primary/foreign keys;
- unique constraints;
- SQL queries;
- inserts/updates/deletes;
- transactions;
- migrations;
- ownership relationships;
- counters;
- pagination;
- duplicate prevention;
- concurrent updates;
- stale writes;
- cleanup/deletion;
- data import/export;
- cron/worker mutations.

Do not replace MySQL, introduce an ORM, redesign the product, or perform unrelated cleanup.

## Core Invariants

Identify important project-specific invariants such as:

- one logical entity has one authoritative row;
- unique data cannot silently duplicate;
- child records belong to valid parents;
- users cannot accidentally own another user’s data;
- balances/counts cannot become invalid;
- multi-step mutations do not partially commit;
- older state cannot overwrite newer state incorrectly;
- retries do not duplicate consequential records;
- deletion does not leave harmful orphaned data;
- migrations leave the database in a valid version.

Treat data loss, duplication, ownership corruption, and partial financial/account mutations as high severity.

## Priority

**P0:** corruption, destructive data loss, cross-user ownership failure, duplicate financial/value records, broken migrations.

**P1:** stale overwrite, partial transactions, dangerous duplicate insertion, orphaned critical data, concurrency bugs.

**P2:** weak constraints, inconsistent types, query correctness issues, missing ownership checks, cleanup problems.

**P3:** minor normalization, indexing, naming, and maintainability improvements.

Fix P0/P1 first.

## Establish the Data Model

Map:

- major tables;
- primary keys;
- ownership;
- relationships;
- authoritative fields;
- derived fields;
- mutable counters;
- unique identities;
- lifecycle/deletion rules.

Trace important mutations:

> **request/action → validation → SQL → transaction → commit → later read**

Find every place that changes durable business data.

## Schema Review

Inspect:

- column types;
- nullable fields;
- defaults;
- unsigned/signed usage;
- lengths;
- date/time types;
- booleans/enums;
- primary keys;
- foreign keys;
- indexes;
- uniqueness.

Look for schemas that allow states the application considers impossible.

Use database constraints when they naturally express durable invariants.

Do not encode every gameplay/business rule in SQL.

## Query Correctness

Inspect queries for:

- wrong joins;
- duplicate rows;
- missing ownership conditions;
- accidental cartesian joins;
- unsafe grouping;
- incorrect NULL behavior;
- inconsistent filtering;
- incorrect ordering;
- stale assumptions.

Do not optimize a query that is logically wrong.

Correctness first.

## Prepared Statements

Use parameterized queries for data values.

Do not construct SQL using raw request input.

For dynamic identifiers such as:

- column names;
- sort fields;
- sort direction;

use explicit allowlists.

## Ownership Integrity

For user-owned records ask:

> **Can this query read or mutate a row belonging to another user?**

Prefer constraining ownership in the authoritative SQL operation where appropriate.

Example concept:

> update resource where id = ? AND user_id = current_user

Do not rely solely on UI filtering.

## Unique Constraints

Where the domain requires uniqueness, consider enforcing it in MySQL.

Examples:

- one account per unique identity;
- one membership relationship;
- one reward/claim per logical event;
- one row per user/resource combination.

Application logic like:

> SELECT first, then INSERT

may race.

Use database uniqueness when it accurately represents the invariant.

## Foreign Keys & Orphans

Inspect whether related rows can become orphaned.

Determine intended delete behavior:

- restrict;
- cascade;
- set null;
- explicit application cleanup.

Do not add cascading deletion blindly.

Protect valuable historical/account data.

## Transactions

Identify operations that must succeed or fail together.

Examples:

- purchase;
- account transfer;
- credit deduction + item/service grant;
- multi-table creation;
- subscription/entitlement changes;
- inventory-like ownership transfers.

Ask:

> **What happens if step 2 fails after step 1 commits?**

Use transactions where atomicity is required.

Keep transactions as small as practical.

## Partial Failure

For multi-step mutations inspect failure after each step.

Avoid states such as:

- payment recorded but entitlement missing;
- balance deducted but purchase absent;
- parent created without required children;
- one side of transfer committed without the other.

Use rollback or safe compensating behavior where appropriate.

## Read-Modify-Write Races

Search for:

> **SELECT value → modify in PHP → UPDATE value**

when concurrent requests may act on the same row.

Examples:

- counters;
- balances;
- stock;
- quotas;
- usage;
- credits.

Two requests may both read the same old value.

Use appropriate:

- atomic SQL updates;
- transaction locking;
- optimistic concurrency;
- authoritative serialization.

Do not add locks without evidence.

## Stale Writes

Ask:

> **Can older data loaded earlier overwrite newer changes?**

Inspect:

- edit forms;
- admin screens;
- background jobs;
- workers;
- delayed API requests.

Where meaningful, use:

- revision/version fields;
- updated timestamps;
- conditional updates;
- merge logic.

Do not add optimistic concurrency everywhere unless warranted.

## Duplicate Requests

For consequential mutations ask:

> **What happens if this request runs twice?**

Inspect:

- purchases;
- subscriptions;
- notifications;
- rewards;
- webhooks;
- imports;
- cron jobs.

Use idempotency, unique constraints, or transactional checks where appropriate.

## Counters & Derived Data

Inspect stored counts/totals that can drift from source rows.

Examples:

- comment counts;
- balances;
- aggregate totals;
- unread counts.

Determine whether each should be:

- computed;
- cached;
- transactionally maintained.

Do not keep multiple authorities for the same fact without a clear reason.

## NULL / Default Semantics

Check whether NULL, `0`, empty string, and missing values are being treated inconsistently.

Fix ambiguity where it creates real bugs.

Do not normalize values blindly when different meanings are intentional.

## Date / Time Handling

Inspect:

- timezone assumptions;
- DATETIME/TIMESTAMP usage;
- expiry queries;
- date boundaries;
- ordering.

Ensure PHP and MySQL agree on intended time semantics.

Avoid hidden local-time assumptions in shared/server systems.

## Pagination

Inspect pagination for:

- duplicate/missing records;
- unstable ordering;
- huge offsets;
- unbounded limits;
- invalid page values.

Use deterministic ordering.

Bound requested page sizes.

For very large datasets, consider keyset/cursor pagination where it materially improves correctness/performance.

## Deletion & Cleanup

Trace deletion of:

- users;
- accounts;
- posts;
- files;
- subscriptions;
- related records.

Ask:

- What must be deleted?
- What must remain?
- What must be anonymized?
- What foreign references remain?

Avoid silent orphan accumulation or destructive cascade surprises.

## Migrations

Inspect existing migration/versioning behavior.

For each active migration:

> **old valid schema/data → migrate → application works → subsequent migration still works**

Do not casually edit previously applied migrations.

Prefer forward corrective migrations when appropriate.

Check:

- defaults;
- backfills;
- new NOT NULL columns;
- indexes;
- foreign keys;
- renamed/removed fields;
- partial migration failure.

## Migration Safety

For risky migrations:

- understand existing data first;
- avoid destructive conversion without evidence;
- ensure operations can complete at expected scale;
- handle invalid legacy rows deliberately;
- validate post-migration invariants.

Do not silently discard records to make migration succeed.

## Index Review

After correctness, inspect indexes supporting important:

- WHERE clauses;
- JOINs;
- ORDER BY;
- ownership queries;
- uniqueness.

Look for:

- obvious missing indexes;
- redundant indexes;
- wrong column order.

Do not add indexes to every column.

Remember indexes increase write/storage cost.

## Worker / Cron Concurrency

If workers or cron jobs modify MySQL, inspect:

- overlapping executions;
- duplicate claims;
- stale leases;
- repeated processing;
- race-prone status changes.

Ensure two workers cannot both claim exclusive work unintentionally.

## Pattern Hunt

When one defect appears, search siblings.

Examples:

- one unsafe read-modify-write → inspect other counters/balances;
- one missing unique constraint → inspect similar association tables;
- one ownership omission → inspect related queries;
- one partial transaction → inspect similar multi-step mutations.

Fix patterns where justified.

## Implementation Standard

For each meaningful finding:

1. identify affected tables/queries;
2. define the violated invariant;
3. reproduce or prove the failure;
4. determine root cause;
5. implement the smallest robust fix;
6. validate existing data compatibility;
7. test normal behavior;
8. test duplicate/concurrent behavior where relevant;
9. inspect sibling code;
10. add focused regression coverage where practical.

## Productive Loop

After each wave ask:

- What data can still duplicate?
- What can become orphaned?
- Which mutation can partially commit?
- Which update can overwrite newer state?
- Which query lacks ownership?
- Which invariant exists only in PHP but should be enforced by MySQL?
- Which worker can process the same row twice?
- Did a schema fix break existing data?

Continue while material, safely actionable issues remain.

## Anti-Overengineering

Do not introduce:

- an ORM;
- a repository framework;
- event sourcing;
- distributed transactions;
- generalized locking infrastructure;
- normalization purely for academic purity.

Prefer simple SQL and schema rules that clearly protect actual application invariants.

## Completion

Finish when:

- major table relationships are understood;
- critical ownership is enforced;
- dangerous duplicate states are prevented;
- consequential multi-step writes are appropriately atomic;
- stale/concurrent update risks discovered are fixed;
- migrations are safe for supported data;
- important queries return correct results;
- critical constraints exist where appropriate;
- no discovered P0/P1 integrity issue remains without a blocker;
- legitimate existing behavior still works.

## Final Report

Provide:

1. **Overall result**
2. **Critical integrity fixes**
3. **Schema/constraints**
4. **Query correctness**
5. **Transactions/concurrency**
6. **Ownership/duplicates**
7. **Migrations**
8. **Index/performance changes**
9. **Exact validation performed**
10. **Deliberately unchanged behavior**
11. **Remaining risks with severity and next action**

# Final Directive

Treat MySQL as part of the application's correctness model, not merely storage.

Repeatedly ask:

> **What invariant must always remain true?**

> **Can two requests both succeed when only one should?**

> **Can this partially commit?**

> **Can older state overwrite newer state?**

> **Can another user’s ID reach this query?**

> **Can the database itself prevent an impossible durable state?**

Leave behind the **same website**, but make its database substantially more correct, consistent, concurrency-safe, migration-safe, and resistant to duplication, stale writes, partial failure, and data corruption.