How to test data export feature

Run curl -s http://127.0.0.1:9650/api/account/1/export, then enumerate every table and column in the schema and ask which of them the export covers. A scan for the subject's email found four locations holding five rows on the fixture below. The export carried one of them, and missed the audit log.

Why check this

Run this on staging after a release that adds a table, and again before an export endpoint reaches production. The failure it catches is a table nobody remembered. An audit log written by another service, a support note typed by a human, a newsletter row keyed by email address: none of them belongs to the account model, so none of them reaches the export, and none of them appears in a checklist written from that same account model.

The second failure is an export that is complete and unreadable. The file below lists product_id 10, 11 and 12 and carries no products table, so the person who receives it learns three numbers. A file that cannot be read without the exporter's own database answers the request in form only.

Legal requirements for a data export vary by jurisdiction, by the kind of data and by who is asking. This procedure checks whether a system does what it claims to do. It does not decide whether an organisation complies with any rule, and no result on this page is a compliance finding.

Prerequisites

• Node 22 and the sqlite3 shell. Both versions are in the Verified block, and they matter here: the two SQLite clients on a machine are often different builds.
• curl for the HTTP call. The curl manual covers the flags used below.
• 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.
• SQLite is not the database you run in production. The catalogue queries are SQLite's. The method, enumerate first and ask about coverage second, is the part that 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.

   Request the export and keep the file.

   curl -s http://127.0.0.1:9650/api/account/1/export | tee export.json
   

   {
   "generated_at": "2026-09-12T00:00:00Z",
   "user": {
     "id": 1,
     "email": "nadia.k@example.net",
     "name": "Nadia K",
     "created_at": "2025-02-11T09:00:00Z"
   },
   "orders": [
     {
       "id": 500,
       "placed_at": "2025-05-04T10:12:00Z",
       "total_cents": 18900
     },
     {
       "id": 501,
       "placed_at": "2025-06-21T14:03:00Z",
       "total_cents": 3400
     }
   ],
   "order_items": [
     {
       "id": 900,
       "order_id": 500,
       "product_id": 10,
       "qty": 1
     },
     {
       "id": 901,
       "order_id": 500,
       "product_id": 12,
       "qty": 2
     },
     {
       "id": 902,
       "order_id": 501,
       "product_id": 11,
       "qty": 1
     }
   ],
   "tickets": [
     {
       "id": 70,
       "subject": "Keyboard arrived with a bent pin"
     }
   ]
   }

   Four sections. Read them once, then stop reading them. An export cannot tell you what it left out, so the rest of this check works from the schema.

2. 2

   Step 2.

   List every table and every column the database has.

   sqlite3 -box shop.db "SELECT m.name AS tbl, group_concat(p.name, ', ') AS columns FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' GROUP BY m.name ORDER BY m.name;"
   

   ┌───────────────┬─────────────────────────────────────────┐
   │      tbl      │                 columns                 │
   ├───────────────┼─────────────────────────────────────────┤
   │ audit_log     │ id, actor_email, action, ip, at         │
   │ daily_revenue │ day, order_count, total_cents           │
   │ newsletter    │ email, since                            │
   │ order_items   │ id, order_id, product_id, qty           │
   │ orders        │ id, user_id, placed_at, total_cents     │
   │ products      │ id, sku, name                           │
   │ support_notes │ id, ticket_id, body                     │
   │ tickets       │ id, user_id, subject                    │
   │ users         │ id, email, name, created_at, deleted_at │
   └───────────────┴─────────────────────────────────────────┘

   Nine tables. The export named four of them.

3. 3

   Step 3.

   Ask the schema which tables declare a reference to the account.

   sqlite3 -box shop.db "SELECT m.name AS child, f.\"from\" AS col, f.\"table\" AS parent FROM sqlite_master m JOIN pragma_foreign_key_list(m.name) f WHERE m.type = 'table' AND f.\"table\" = 'users';"
   

   ┌─────────┬─────────┬────────┐
   │  child  │   col   │ parent │
   ├─────────┼─────────┼────────┤
   │ orders  │ user_id │ users  │
   │ tickets │ user_id │ users  │
   └─────────┴─────────┴────────┘

   Two tables, and both are already in the export. A review that stops here reports full coverage.

4. 4

   Step 4.

   Scan every text column in the database for the subject's address. 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. The foreign key graph in step 3 named none of the three that are not users, because none of them holds an account id. The audit log keys on the address, the newsletter table keys on the address, and the support note holds it inside a sentence a human typed.

5. 5

   Step 5.

   Take one distinctive value from each location and ask whether the export carries it.

   for s in 'nadia.k@example.net' 'address.update' '203.0.113.44' '500 118 220' 'tomas.b@example.net'; do printf '%-22s %s\n' "$s" "$(grep -c "$s" export.json)"; done
   

   nadia.k@example.net    1
   address.update         0
   203.0.113.44           0
   500 118 220            0
   tomas.b@example.net    0

   Three misses, and one result worth keeping: the last line is zero, so no second account leaked into this file.

6. 6

   Step 6.

   Resolve the identifiers the export left bare.

   grep -o '"product_id": [0-9]*' export.json | grep -o '[0-9]*$' | sort -u | while read id; do sqlite3 shop.db "SELECT $id || ' -> ' || name FROM products WHERE id = $id;"; done
   

   10 -> Split keyboard
   11 -> Trackball
   12 -> USB-C cable

   The command that made the file readable had to read a table the file does not contain. That is the readability defect, stated as a reproducible fact rather than an opinion about formatting.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | A scan hit in a table the export does not name | The export was built from the account model, not from the schema | Decide per table: include it, or write down why it stays. | | A hit in a free-text column | A human put the address into a sentence | Search text columns by identifier, not by display name. | | An id in the export with no lookup table beside it | The file is complete and unreadable | Join the name in at export time, or ship the lookup table. | | Another account's identifier in the file | The export query is missing a filter | Treat it as a defect before any coverage work. | | A scan hit that stays out on purpose | A retention or accounting decision | Record the decision. An undocumented omission looks identical to a bug. |

Common mistakes

What to check next

• How to test data deletion request: the same schema scan, run after a deletion, on the same fixture.
• How to check table schema: the catalogue queries behind step 2, in more detail.
• How to check logs for pii: the copy of the person's data that no export endpoint reaches.
• Sensitive data in logs: the same question asked of secrets rather than of personal data.
• How to check a consent record: what the system stored about the person's choices, which is itself exportable data.

FAQ

What should a data export contain?

Whatever the product tells the person it contains, at minimum. Beyond that the answer is a policy decision, not a test result. The check here is coverage against the schema: every location holding the subject's data is either in the file or on a written list of deliberate omissions.

Can I test an export without database access?

Partly. You can confirm the file parses, holds one account, and resolves its own identifiers. You cannot confirm coverage, because coverage is a statement about what exists outside the file. Ask for read access to the catalogue, or for the schema dump.

Why scan text columns instead of joining on the user id?

Both, in that order. The id join finds tables that declare a foreign key. The text scan finds the ones that store an email address or a phone number with no relationship at all, which on this fixture was three tables out of nine.

Is a missing table always a defect?

No. An accounting record or a fraud log can be a deliberate exclusion. The defect is an exclusion nobody decided on. Write the list of excluded tables into the test, so the next release changes a reviewed list rather than a silent one.

Verified