Teardown: Getting 'Download Your Data' Right
A step-by-step teardown of data export flows, from contract to SQL, with an async /exports example and checks that keep you out of trouble.

This teardown is for product managers who need data portability, compliance exports, or user-initiated dumps. By the end, you will have a contract you can put in your PRD, a mapped data model, an API and job sketch, a set of SQL starting points, and checks to keep you honest.
In brief
- Write a tight contract: what objects, which fields, file formats, and time windows. Not “everything”.
- Map user actions to canonical tables. Expect many-to-many joins and soft-deletes.
- Choose sync vs async. Add chunking, retries, pagination, and rate limits.
- Draft and test SQL with temporal filters and anti-joins for deletions.
- Instrument with audit rows and export events. Add quick integrity checks.
What PMs miss about “Download my data”
If you do not write the contract, the schema will write it for you.
Users ask for a button. Your system has normalization, caches, soft-deletes, and retention rules. The mismatch is where trust breaks and costs grow.
Here are the steps to land a data export that is clear, correct, and efficient.
1) Define the contract first
List user-facing objects: profile, settings, posts, comments, attachments, consent history. Exclude system-only artifacts unless needed for legal reasons.
Pick JSON for nested objects and CSV for tabular lists. Freeze field names and types. Note identifier formats and time zones.
State the retention window per object. Example: last 24 months of activity; full consent history regardless of age.
Put the contract and examples in your PRD. It prevents “just add this table too” scope creep.
2) Map the user view to canonical tables
Write the mapping. One user action can touch many normalized tables and denormalized caches. Avoid reading from caches unless you verify them against source-of-truth tables.
- Profile → users, user_settings, user_identifiers.
- Posts → posts, post_edits, post_visibility, post_tags (many-to-many).
- Comments → comments, comment_flags.
- Attachments → attachments, attachment_blobs, attachment_links.
- Consent → user_consents, consent_versions.
Note soft-deletes and archives. You will need anti-joins to include deletions in the audit trail or to exclude them from content, depending on jurisdiction and policy.
3) Pick sync vs async, then sketch the flow
| Aspect | Sync request | Async job |
|---|---|---|
| User wait | Immediate, small payloads | Email or in-app when ready, good for large data |
| Payload size | Small JSON pages | Large zipped bundles |
| Limits | Short timeouts, strict rate limits | Queue limits, worker concurrency |
| Retries | Client retries with idempotency key | Backoff and resume in worker |
| Failure surfaces | API only | API + queue + storage |
| When to use | Account profile, last 20 events | Full user archive, legal exports |
Whichever you choose, plan for chunking, pagination, and resumable work. Set user-facing rate limits and back-pressure on workers.
4) Draft the SQLs you need
Write the core queries early. Use temporal predicates for retention windows. Add anti-joins to represent deletions where required. Test on sample cohorts: a light user, a heavy user, and one with many deletions.
5) Instrument and verify
- Write one audit row when a user requests an export. Include requester id, subject id, scope, and retention window.
- Emit status events: requested, processing, chunk_written, completed, failed.
- Add quick SQL integrity checks. Compare counts and detect gaps before you mark completed.
- Store consent timestamps and policy version. Many audits ask for it.
Common mistakes
- Exporting “all tables”. Users get noise. You ship sensitive internal fields by accident.
- Ignoring deleted or archived rows. Audits will ask where they went.
- Trusting denormalized caches without verification. They drift.
- Underestimating traffic and CPU from large joins. The export becomes an outage risk.
- Missing legal metadata, like consent timestamps and versions.
Worked example: an async /exports that zips JSON + CSV
Goal: a user requests an export. The system builds a zipped bundle with JSON (profile) and CSV (lists) for posts, comments, attachments, and consent history. It returns a download link when done.
API sketch
# Request an export
POST /exports
Body: {
"subject_user_id": "u_123",
"scope": ["profile","posts","comments","attachments","consents"],
"retention": {"from": "2022-01-01T00:00:00Z", "to": "2024-12-31T23:59:59Z"}
}
# Response
202 Accepted
{
"export_id": "exp_789",
"status": "queued"
}
# Poll status
GET /exports/exp_789
{
"status": "processing|completed|failed",
"files": [
{"path": "profile.json"},
{"path": "posts.csv"},
{"path": "comments.csv"},
{"path": "attachments.csv"},
{"path": "consents.csv"}
],
"download_url": "https://.../exp_789.zip"
}
Background job flow
- Write an audit row for the request. Enqueue work with idempotency key export_id.
- Fetch ids in pages (e.g., 5k at a time) by time window. Avoid full scans.
- Stream rows to CSV/JSON writers per object. Flush per chunk.
- Store chunks in temporary storage. Track bytes written.
- When all objects are done, assemble zip. Emit completed.
SQL snippets
Assume soft-deletes via deleted_at and archives via archived_at. Replace table and column names to match your schema.
-- Posts within window, excluding deleted content
SELECT p.id, p.user_id, p.title, p.body, p.created_at, p.updated_at
FROM posts p
WHERE p.user_id = :user_id
AND p.created_at BETWEEN :from AND :to
AND p.deleted_at IS NULL
ORDER BY p.created_at
LIMIT :limit OFFSET :offset;
-- Comments by user, including those on others' posts
SELECT c.id, c.post_id, c.user_id, c.body, c.created_at, c.updated_at
FROM comments c
WHERE c.user_id = :user_id
AND c.created_at BETWEEN :from AND :to
AND c.deleted_at IS NULL
ORDER BY c.created_at
LIMIT :limit OFFSET :offset;
-- Attachments the user owns or uploaded
SELECT a.id, a.owner_user_id AS user_id, a.filename, a.content_type,
a.size_bytes, a.created_at
FROM attachments a
WHERE a.owner_user_id = :user_id
AND a.created_at BETWEEN :from AND :to
AND a.deleted_at IS NULL
ORDER BY a.created_at
LIMIT :limit OFFSET :offset;
-- Consent history (no time limit here by design)
SELECT uc.id, uc.user_id, uc.consent_code, uc.version,
uc.status, uc.consented_at
FROM user_consents uc
WHERE uc.user_id = :user_id
ORDER BY uc.consented_at;
-- Deletions audit: posts deleted in window (for transparency)
SELECT p.id, p.user_id, p.deleted_at
FROM posts p
WHERE p.user_id = :user_id
AND p.deleted_at BETWEEN :from AND :to;
If you maintain a denormalized feed table, verify it instead of exporting from it:
-- Verify denormalized feed does not miss live posts
SELECT p.id
FROM posts p
LEFT JOIN feed_denorm f ON f.post_id = p.id
WHERE p.user_id = :user_id
AND p.deleted_at IS NULL
AND f.post_id IS NULL
LIMIT 10; -- any rows here indicate drift
Quick integrity check before “completed”
Compare the export against source-of-truth counts inside the same window. Fail fast if they drift beyond an agreed delta.
-- Example: did we export every live post?
WITH live AS (
SELECT COUNT(*) AS c
FROM posts
WHERE user_id = :user_id AND deleted_at IS NULL
AND created_at BETWEEN :from AND :to
),
exported AS (
SELECT COUNT(*) AS c
FROM export_items
WHERE export_id = :export_id AND object_type = 'post'
)
SELECT l.c AS live_count, e.c AS exported_count, (l.c - e.c) AS gap
FROM live l CROSS JOIN exported e;
If gap is not zero, retry the missing ranges or flag the job as failed with a clear reason. Do not mark completed until checks pass.
Putting it together
Start with the contract. Map to canonical tables. Choose async for large scopes. Draft SQL with temporal filters and anti-joins. Instrument the flow, then verify. Keep formats stable and documented. Change them only with a new contract version.
This keeps the feature simple for users and honest for audits. It also protects your systems from surprise load when a few heavy users press the button at once.


