How to test data deletion request

Send curl -s -X DELETE http://127.0.0.1:9650/api/account/1, then re-run the schema scan rather than the export. On the fixture the endpoint answered 200 and the export answered 404, while four locations still held the address: the users row, the audit log, a support note and the newsletter table.

Why check this

Run this before any release that touches account closure, and once a quarter against a seeded account on staging. The failure it catches is a deletion that reports success and removes nothing. A soft delete sets a flag, the account endpoint filters on that flag, and every check written against the API reports the account gone while the row and the address sit in the table.

The second failure is narrower and more common. A scrub overwrites the display name, which is what a human looks at, and leaves the email address, which identifies the person. The name field reads Deleted user and the record still points at a live mailbox.

Legal requirements for a deletion request vary by jurisdiction and by what else the organisation has to keep. This procedure checks whether a system does what it claims to do. It does not decide whether an organisation complies with any rule.

Prerequisites

• Node 22 and the sqlite3 shell, both named with versions in the Verified block. Name the client in your own report: the two SQLite builds here disagree about foreign keys.
• curl for the HTTP calls. The curl manual covers -X and -w.
• The fixture, printed in full below. Save both files in one directory, run sqlite3 shop.db < shop.sql, then node fixture.mjs shop.db. It listens on port 9650.
• Every value is synthetic and seeded by the script below. The subject under test is account id 1, nadia.k@example.net.
• A copy of the database taken before the request. Step 1 makes it, step 8 uses it.
• SQLite is not your production database and one file is not a backup estate. The queries are SQLite's; the order of the checks transfers.

-- shop.sql        sqlite3 shop.db < shop.sql
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT, created_at TEXT, deleted_at TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, sku TEXT, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id), placed_at TEXT, total_cents INTEGER);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), product_id INTEGER REFERENCES products(id), qty INTEGER);
CREATE TABLE tickets (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id), subject TEXT);
CREATE TABLE support_notes (id INTEGER PRIMARY KEY, ticket_id INTEGER, body TEXT);
CREATE TABLE audit_log (id INTEGER PRIMARY KEY, actor_email TEXT, action TEXT, ip TEXT, at TEXT);
CREATE TABLE newsletter (email TEXT PRIMARY KEY, since TEXT);
CREATE TABLE daily_revenue (day TEXT PRIMARY KEY, order_count INTEGER, total_cents INTEGER);

INSERT INTO users VALUES
 (1,'nadia.k@example.net','Nadia K','2025-02-11T09:00:00Z',NULL),
 (2,'tomas.b@example.net','Tomas B','2025-03-02T11:20:00Z',NULL),
 (3,'ines.r@example.net','Ines R','2025-04-19T16:45:00Z',NULL);
INSERT INTO products VALUES (10,'KB-201','Split keyboard'),(11,'MS-118','Trackball'),(12,'CB-007','USB-C cable');
INSERT INTO orders VALUES
 (500,1,'2025-05-04T10:12:00Z',18900),(501,1,'2025-06-21T14:03:00Z',3400),(502,2,'2025-06-21T15:40:00Z',7900);
INSERT INTO order_items VALUES (900,500,10,1),(901,500,12,2),(902,501,11,1),(903,502,12,4);
INSERT INTO tickets VALUES (70,1,'Keyboard arrived with a bent pin'),(71,2,'Where is my cable');
INSERT INTO support_notes VALUES
 (300,70,'Called nadia.k@example.net back on +48 500 118 220, agreed to a replacement.'),
 (301,71,'Left voicemail for tomas.b@example.net.');
INSERT INTO audit_log VALUES
 (1,'nadia.k@example.net','login','203.0.113.44','2025-05-04T10:01:00Z'),
 (2,'nadia.k@example.net','address.update','203.0.113.44','2025-05-04T10:08:00Z'),
 (3,'tomas.b@example.net','login','198.51.100.7','2025-06-21T15:30:00Z');
INSERT INTO newsletter VALUES ('nadia.k@example.net','2025-02-11T09:02:00Z'),('ines.r@example.net','2025-04-19T16:50:00Z');
INSERT INTO daily_revenue VALUES ('2025-05-04',1,18900),('2025-06-21',2,11300);

// fixture.mjs     node fixture.mjs shop.db     ->     http://127.0.0.1:9650/
import { createServer } from 'node:http';
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(process.argv[2] ?? 'shop.db');
const all = (sql, ...p) => db.prepare(sql).all(...p);
const one = (sql, ...p) => db.prepare(sql).get(...p);

createServer((req, res) => {
  const path = new URL(req.url, 'http://127.0.0.1').pathname;
  const send = (code, body) => {
    res.writeHead(code, { 'content-type': 'application/json' });
    res.end(JSON.stringify(body, null, 2));
  };
  let m;
  if (req.method === 'GET' && (m = /^\/api\/account\/(\d+)\/export$/.exec(path))) {
    const id = Number(m[1]);
    const user = one('SELECT id, email, name, created_at FROM users WHERE id = ? AND deleted_at IS NULL', id);
    if (!user) return send(404, { error: 'no such account' });
    return send(200, {
      generated_at: '2026-09-12T00:00:00Z',
      user,
      orders: all('SELECT id, placed_at, total_cents FROM orders WHERE user_id = ?', id),
      order_items: all('SELECT i.id, i.order_id, i.product_id, i.qty FROM order_items i JOIN orders o ON o.id = i.order_id WHERE o.user_id = ?', id),
      tickets: all('SELECT id, subject FROM tickets WHERE user_id = ?', id),
    });
  }
  if (req.method === 'DELETE' && (m = /^\/api\/account\/(\d+)$/.exec(path))) {
    const id = Number(m[1]);
    db.prepare("UPDATE users SET deleted_at = '2026-09-12T00:00:00Z', name = 'Deleted user' WHERE id = ?").run(id);
    db.prepare('DELETE FROM tickets WHERE user_id = ?').run(id);
    return send(200, { status: 'deleted', user_id: id });
  }
  if (req.method === 'GET' && (m = /^\/api\/orders\/(\d+)$/.exec(path))) {
    const o = one('SELECT o.id, o.placed_at, o.total_cents, u.email AS customer_email, u.name AS customer_name FROM orders o JOIN users u ON u.id = o.user_id WHERE o.id = ?', Number(m[1]));
    return o ? send(200, o) : send(404, { error: 'no such order' });
  }
  send(404, { error: 'not found' });
}).listen(9650, '127.0.0.1', () => console.log('fixture on http://127.0.0.1:9650'));

Steps

1. 1

   Step 1.

   Copy the database before sending anything. This copy stands in for the backup estate in step 8.

   cp -v shop.db backup-2026-09-12.db
   

   'shop.db' -> 'backup-2026-09-12.db'

2. 2

   Step 2.

   Send the deletion request for the account under test.

   curl -s -X DELETE http://127.0.0.1:9650/api/account/1 -w '\nHTTP %{http_code}\n'
   

   {
   "status": "deleted",
   "user_id": 1
   }
   HTTP 200

   This is the only line most deletion tests assert on, and the least informative one here.

3. 3

   Step 3.

   Ask the account endpoint for the same account.

   curl -s http://127.0.0.1:9650/api/account/1/export -w '\nHTTP %{http_code}\n'
   

   {
   "error": "no such account"
   }
   HTTP 404

   A 200 followed by a 404 looks like proof. It is one filter reporting on itself: the query ends in deleted_at IS NULL, so it answers 404 for any flagged row, whatever that row still holds.

4. 4

   Step 4.

   Run the schema scan for the subject's address again. The first sqlite3 call writes the query, the second runs it.

   sqlite3 shop.db "SELECT 'SELECT * FROM (' || group_concat('SELECT ''' || m.name || '.' || p.name || ''' AS location, count(*) AS hits FROM ' || m.name || ' WHERE ' || p.name || ' LIKE ''%nadia.k@example.net%''', ' UNION ALL ') || ') WHERE hits > 0 ORDER BY hits DESC;' FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND p.type = 'TEXT';" | sqlite3 -box shop.db
   

   ┌───────────────────────┬──────┐
   │       location        │ hits │
   ├───────────────────────┼──────┤
   │ audit_log.actor_email │ 2    │
   │ users.email           │ 1    │
   │ support_notes.body    │ 1    │
   │ newsletter.email      │ 1    │
   └───────────────────────┴──────┘

   Four locations, five rows, identical to the result before the request. The deletion removed one ticket and changed one name.

5. 5

   Step 5.

   Read the row the endpoint says does not exist.

   sqlite3 -box shop.db "SELECT id, email, name, deleted_at FROM users WHERE id = 1;"
   

   ┌────┬─────────────────────┬──────────────┬──────────────────────┐
   │ id │        email        │     name     │      deleted_at      │
   ├────┼─────────────────────┼──────────────┼──────────────────────┤
   │ 1  │ nadia.k@example.net │ Deleted user │ 2026-09-12T00:00:00Z │
   └────┴─────────────────────┴──────────────┴──────────────────────┘

   The name is gone and the identifier is not. deleted_at is a retention decision in a column, so treat it as one: it needs a stated window and a job that finishes the work.

6. 6

   Step 6.

   Ask an endpoint that reaches the same row by a different path.

   curl -s http://127.0.0.1:9650/api/orders/500 -w '\nHTTP %{http_code}\n'
   

   {
   "id": 500,
   "placed_at": "2025-05-04T10:12:00Z",
   "total_cents": 18900,
   "customer_email": "nadia.k@example.net",
   "customer_name": "Deleted user"
   }
   HTTP 200

   The order endpoint joins users with no condition on the flag, so it serves the address the account endpoint hides. One query learned about the soft delete and the other did not.

7. 7

   Step 7.

   Read the derived aggregate.

   sqlite3 -box shop.db "SELECT * FROM daily_revenue;"
   

   ┌────────────┬─────────────┬─────────────┐
   │    day     │ order_count │ total_cents │
   ├────────────┼─────────────┼─────────────┤
   │ 2025-05-04 │ 1           │ 18900       │
   │ 2025-06-21 │ 2           │ 11300       │
   └────────────┴─────────────┴─────────────┘

   The 18900 on 2025-05-04 is this person's order, still counted. Keeping it is usually right: the row names nobody and recomputing changes reported revenue. It has to be a recorded decision, with the rule that no aggregate gets fine enough to point back at one person.

8. 8

   Step 8.

   Run the same scan against the copy taken in step 1.

   sqlite3 shop.db "SELECT 'SELECT * FROM (' || group_concat('SELECT ''' || m.name || '.' || p.name || ''' AS location, count(*) AS hits FROM ' || m.name || ' WHERE ' || p.name || ' LIKE ''%nadia.k@example.net%''', ' UNION ALL ') || ') WHERE hits > 0 ORDER BY hits DESC;' FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND p.type = 'TEXT';" | sqlite3 -box backup-2026-09-12.db
   

   ┌───────────────────────┬──────┐
   │       location        │ hits │
   ├───────────────────────┼──────┤
   │ audit_log.actor_email │ 2    │
   │ users.email           │ 1    │
   │ support_notes.body    │ 1    │
   │ newsletter.email      │ 1    │
   └───────────────────────┴──────┘

   Everything, including the ticket the live database no longer has. This is the limit of the check: the live database is one copy. Backups, replicas, log archives and search indexes are the others, and none is reachable from this port.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | 200 from the deletion, 404 from the account endpoint | Nothing yet. Both answers come from the same filter | Query the table directly, as in step 5. | | The row present with a timestamp in deleted_at | A soft delete | Get the retention window in writing and re-run the scan after it passes. | | Scan hits outside the account model | Rows keyed by address, not by account id | Handle each one, or list it as a stated exclusion. | | An identifier returned by a second endpoint | One query knows about the flag and another does not | Fix at the query, and test every read path that joins the table. | | An aggregate still carrying the contribution | Usually correct, sometimes unreviewed | Write the decision down and check the aggregate cannot be narrowed to one person. | | The backup copy unchanged | The expected result, not a defect | Report deletion coverage as live plus a named backup retention period. |

Common mistakes

What to check next

• How to test data export feature: the schema scan this page reuses, and how it is built.
• How to verify a database backup: the copy in step 8, and what restoring it puts back.
• How to check foreign key: why one client refused the hard delete and the other did not.
• How to check logs for pii: the copy of the address that no deletion endpoint touches.
• How to revoke cookie consent: the smaller request a person usually makes first.

FAQ

Is a soft delete a failed deletion?

Not on its own. It is a failure when nothing follows it. A flag with a stated retention window and a job that removes the row is a design; a flag with neither is a deletion that never happens. Ask which one you are looking at.

How do I test this without deleting real data?

Use a seeded account on staging, as here. It needs history in more than one table, otherwise the check passes for the wrong reason. Recreate the fixture rather than borrowing a production account.

Should aggregates be recomputed after a deletion?

Usually not. daily_revenue names nobody and rewriting it changes reported figures. The requirement is that the decision is recorded and that no aggregate is fine grained enough to identify one person. An hourly total for a town of two thousand is no longer an aggregate.

What does the backup step prove?

That the result is a statement about one copy. Report it that way: removed from the live database on this date, present in backups until retention ends.

Verified