turnley.dev

PHP and MySQL code for faucet operators

Tools & Calculators

LEG-003

Let a user export or delete their own data

Export as JSON, delete as anonymise-and-keep-the-ledger — because financial records have to survive a deletion request.

Two rights come up repeatedly: give me my data, and delete my account. The first is a query and a download. The second conflicts with keeping books that balance.

The resolution is anonymisation rather than deletion. The personal fields are cleared, the account can no longer be used, and the ledger rows survive with their user id intact but no longer pointing at an identifiable person. Your totals still reconcile and there is nothing left tying the rows to a name.

Do it in one transaction. A half-anonymised account is worse than either state.

PHP 
function export_user_data(PDO $pdo, int $userId): string
{
    $out = [];
    $st = $pdo->prepare('SELECT id, username, email, created_at, balance, ref_code FROM users WHERE id = ?');
    $st->execute([$userId]);
    $out['account'] = $st->fetch(PDO::FETCH_ASSOC);

    foreach (['ledger' => 'user_id', 'withdrawals' => 'user_id', 'activity' => 'user_id'] as $table => $col) {
        $st = $pdo->prepare("SELECT * FROM {$table} WHERE {$col} = ? ORDER BY id");
        $st->execute([$userId]);
        $out[$table] = $st->fetchAll(PDO::FETCH_ASSOC);
    }

    return (string) json_encode($out, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
}

function anonymise_user(PDO $pdo, int $userId): bool
{
    $pdo->beginTransaction();
    try {
        // Financial history stays. The person behind it does not.
        $pdo->prepare(
            'UPDATE users
                SET username = CONCAT("deleted_", id),
                    email = NULL,
                    pass_hash = "",
                    ref_code = NULL,
                    wallet_address = NULL,
                    last_ip = NULL,
                    status = 0,
                    anonymised_at = NOW()
              WHERE id = ?'
        )->execute([$userId]);

        // Anything holding raw identifiers goes entirely.
        $pdo->prepare('DELETE FROM activity WHERE user_id = ?')->execute([$userId]);
        $pdo->prepare('DELETE FROM sessions WHERE user_id = ?')->execute([$userId]);

        $pdo->commit();
        return true;
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Using it

Require a password confirmation and send a notification before anonymising. It is irreversible and a hijacked session should not be able to do it silently.

Pay out or explicitly forfeit the remaining balance first, and say which in your terms. Deleting an account holding money without addressing it is the dispute you do not want.

Serve the export as a download with a sensible filename, not as a page.

What bites people

Deleting the user row outright breaks every foreign key pointing at it and leaves a ledger you cannot reconcile.

Backups still contain the original data. Say so honestly rather than claiming instant erasure everywhere.

Free the username and referral code, or the anonymised account keeps holding names nobody can use.

Also in Legal and Compliance