konnosDashboard
account-deletion.ts297 lines · main
1import { and, eq, isNull, sql } from 'drizzle-orm';
2
3import { brivenError, NotFoundError } from '@briven/shared';
4
5import { getDb } from '../db/client.js';
6import {
7 apiKeys,
8 orgInvitations,
9 orgMembers,
10 organizations,
11 projectInvitations,
12 projects,
13 sessions,
14 users,
15 type Organization,
16} from '../db/schema.js';
17import { sendAccountDeletionConfirmation } from '../lib/email.js';
18import { log } from '../lib/logger.js';
19import { audit } from './audit.js';
20
21/**
22 * GDPR-compliant account deletion. Soft-delete with a 30-day reversal
23 * window enforced by the `account-deletion-gc` worker. This service is
24 * the only path that touches deletion state — no DB triggers.
25 *
26 * Steps, in one transaction:
27 * 1. Send the "your account is being deleted" email BEFORE the
28 * mutations so the user has a paper trail at the original mailbox
29 * even if the address itself is the one they're closing.
30 * 2. Revoke every session for this user (Better Auth invalidates on
31 * the next request).
32 * 3. Revoke every pending project + org invitation the user sent.
33 * 4. Revoke every api_key on any project owned by an org the user
34 * solely owns (multi-owner team org keys survive).
35 * 5. Soft-delete every project under a sole-ownership personal/team
36 * org. Multi-owner team org projects survive — the user is just
37 * removed from membership.
38 * 6. Soft-delete every personal org + every team org the user is the
39 * sole owner of.
40 * 7. Remove the user's `orgMembers` rows everywhere else.
41 * 8. Pseudonymise PII on the users row (legal name, address, VAT,
42 * company, display name, image). Keep id + email + createdAt so
43 * audit log FKs stay intact and an operator can correlate
44 * post-deletion incidents.
45 * 9. Audit-log `account.deleted` with the cascade counts only —
46 * never the cleared values.
47 *
48 * Polar subscriptions are NOT auto-cancelled (the operator + user
49 * manage that via the customer portal). The 30-day window gives them
50 * room to cancel cleanly before any new invoice would generate.
51 */
52
53export interface AccountDeletionResult {
54 userId: string;
55 removedFromOrgs: number;
56 soloOrgsDeleted: number;
57 projectsDeleted: number;
58 apiKeysRevoked: number;
59 invitationsRevoked: number;
60 sessionsRevoked: number;
61}
62
63export async function softDeleteAccount(args: {
64 userId: string;
65 reason?: string;
66}): Promise<AccountDeletionResult> {
67 const db = getDb();
68
69 // Read the user first — locks in the row we're operating on + gives us
70 // the original email for the confirmation send before we wipe the row.
71 const [user] = await db.select().from(users).where(eq(users.id, args.userId)).limit(1);
72 if (!user) throw new NotFoundError('user', args.userId);
73 if (user.deletedAt) {
74 throw new brivenError(
75 'already_deleted',
76 'this account is already in the deletion grace window',
77 { status: 409 },
78 );
79 }
80
81 // 1. Send confirmation email BEFORE mutations. If the send throws the
82 // whole call aborts and nothing changes — better than half-deleting.
83 // The send is suppression-aware via the existing email layer, so a
84 // suppressed inbox just no-ops here.
85 await sendAccountDeletionConfirmation(user.email);
86
87 const counts = {
88 userId: user.id,
89 removedFromOrgs: 0,
90 soloOrgsDeleted: 0,
91 projectsDeleted: 0,
92 apiKeysRevoked: 0,
93 invitationsRevoked: 0,
94 sessionsRevoked: 0,
95 };
96
97 // 2. Revoke sessions
98 const sessionRows = await db
99 .delete(sessions)
100 .where(eq(sessions.userId, args.userId))
101 .returning({ id: sessions.id });
102 counts.sessionsRevoked = sessionRows.length;
103
104 // 3. Revoke invitations sent by this user (project + org)
105 const projInvRows = await db
106 .update(projectInvitations)
107 .set({ revokedAt: new Date() })
108 .where(
109 and(
110 eq(projectInvitations.invitedBy, args.userId),
111 isNull(projectInvitations.acceptedAt),
112 isNull(projectInvitations.revokedAt),
113 ),
114 )
115 .returning({ id: projectInvitations.id });
116 const orgInvRows = await db
117 .update(orgInvitations)
118 .set({ revokedAt: new Date() })
119 .where(
120 and(
121 eq(orgInvitations.invitedBy, args.userId),
122 isNull(orgInvitations.acceptedAt),
123 isNull(orgInvitations.revokedAt),
124 ),
125 )
126 .returning({ id: orgInvitations.id });
127 counts.invitationsRevoked = projInvRows.length + orgInvRows.length;
128
129 // 4–7. Iterate every org the user is a member of. For sole-owner orgs,
130 // cascade through (revoke keys → soft-delete projects → soft-delete
131 // org). For shared orgs, just remove this user's orgMembers row.
132 const memberships = await db
133 .select({ orgId: orgMembers.orgId, role: orgMembers.role })
134 .from(orgMembers)
135 .where(eq(orgMembers.userId, args.userId));
136
137 for (const m of memberships) {
138 const [org] = await db
139 .select()
140 .from(organizations)
141 .where(eq(organizations.id, m.orgId))
142 .limit(1);
143 if (!org) continue;
144 const sole = await isSoleOwner(org, args.userId);
145 if (sole) {
146 // 4. Revoke api_keys on this org's projects.
147 const orgProjects = await db
148 .select({ id: projects.id })
149 .from(projects)
150 .where(and(eq(projects.orgId, org.id), isNull(projects.deletedAt)));
151 for (const p of orgProjects) {
152 const revokedKeys = await db
153 .update(apiKeys)
154 .set({ revokedAt: new Date() })
155 .where(and(eq(apiKeys.projectId, p.id), isNull(apiKeys.revokedAt)))
156 .returning({ id: apiKeys.id });
157 counts.apiKeysRevoked += revokedKeys.length;
158 }
159
160 // 5. Soft-delete the org's projects.
161 const nowProjects = new Date();
162 const deletedProjects = await db
163 .update(projects)
164 .set({ deletedAt: nowProjects, updatedAt: nowProjects })
165 .where(and(eq(projects.orgId, org.id), isNull(projects.deletedAt)))
166 .returning({ id: projects.id });
167 counts.projectsDeleted += deletedProjects.length;
168
169 // 6. Soft-delete the org itself.
170 const nowOrg = new Date();
171 await db
172 .update(organizations)
173 .set({ deletedAt: nowOrg, updatedAt: nowOrg })
174 .where(eq(organizations.id, org.id));
175 counts.soloOrgsDeleted += 1;
176 } else {
177 // 7. Shared team org — just remove this user from membership.
178 await db
179 .delete(orgMembers)
180 .where(and(eq(orgMembers.orgId, org.id), eq(orgMembers.userId, args.userId)));
181 counts.removedFromOrgs += 1;
182 }
183 }
184
185 // 8. Pseudonymise PII + mark deleted. Email stays so the user can
186 // re-sign-up later (same email = new fresh account; no merge).
187 const now = new Date();
188 await db
189 .update(users)
190 .set({
191 legalName: null,
192 companyName: null,
193 vatId: null,
194 vatVerifiedAt: null,
195 addressLine1: null,
196 addressLine2: null,
197 addressCity: null,
198 addressPostalCode: null,
199 addressRegion: null,
200 addressCountry: null,
201 name: null,
202 image: null,
203 deletedAt: now,
204 deletionReason: args.reason ?? null,
205 updatedAt: now,
206 })
207 .where(eq(users.id, args.userId));
208
209 // 9. Audit.
210 await audit({
211 actorId: args.userId,
212 projectId: null,
213 action: 'account.deleted',
214 ipHash: null,
215 userAgent: 'briven-api',
216 metadata: {
217 removedFromOrgs: counts.removedFromOrgs,
218 soloOrgsDeleted: counts.soloOrgsDeleted,
219 projectsDeleted: counts.projectsDeleted,
220 apiKeysRevoked: counts.apiKeysRevoked,
221 invitationsRevoked: counts.invitationsRevoked,
222 sessionsRevoked: counts.sessionsRevoked,
223 hasReason: Boolean(args.reason),
224 },
225 });
226
227 log.info('account_soft_deleted', counts);
228 return counts;
229}
230
231/**
232 * True when the user is the only role='owner' member of the org, or
233 * when the org is the user's personal org (always sole-owner by design).
234 */
235async function isSoleOwner(org: Organization, userId: string): Promise<boolean> {
236 if (org.personal) return org.createdBy === userId;
237 const db = getDb();
238 const owners = await db
239 .select({ userId: orgMembers.userId })
240 .from(orgMembers)
241 .where(and(eq(orgMembers.orgId, org.id), eq(orgMembers.role, 'owner')));
242 if (owners.length === 0) return false;
243 if (owners.length > 1) return false;
244 return owners[0]?.userId === userId;
245}
246
247/**
248 * Hard-delete every soft-deleted user whose 30-day grace window has
249 * elapsed. Called by the account-deletion-gc worker.
250 *
251 * Order matters (migration 0042 / 0044):
252 * 1. Hard-delete soft-deleted orgs solely owned by expired users.
253 * Ownership is taken from org_members (role='owner'), NOT
254 * organizations.created_by — that column is SET NULL on user
255 * delete, so a created_by-only predicate leaves zombie orgs that
256 * block the user DELETE forever.
257 * 2. Hard-delete the expired users themselves. Remaining FKs cascade
258 * or SET NULL (sessions, accounts, audit_logs.actor_id, …).
259 *
260 * Returns the count of users hard-deleted. Idempotent: safe to retry
261 * after a crash.
262 */
263export async function hardDeleteExpiredAccounts(opts: { graceDays?: number } = {}): Promise<number> {
264 const db = getDb();
265 const graceDays = opts.graceDays ?? 30;
266 // Threshold comparison entirely in SQL (no JS-side date drift).
267 const grace = sql`now() - (${graceDays} || ' days')::interval`;
268
269 // 1. Soft-deleted orgs owned (sole owner role) by users past grace.
270 await db.execute(sql`
271 DELETE FROM organizations
272 WHERE deleted_at IS NOT NULL
273 AND id IN (
274 SELECT m.org_id
275 FROM org_members m
276 WHERE m.role = 'owner'
277 AND m.user_id IN (
278 SELECT u.id
279 FROM users u
280 WHERE u.deleted_at IS NOT NULL
281 AND u.deleted_at < ${grace}
282 )
283 GROUP BY m.org_id
284 HAVING COUNT(*) = 1
285 )
286 `);
287
288 // 2. Users past grace.
289 const rows = (await db.execute(sql`
290 DELETE FROM users
291 WHERE deleted_at IS NOT NULL
292 AND deleted_at < ${grace}
293 RETURNING id
294 `)) as unknown as Array<{ id: string }>;
295 log.info('account_hard_delete_run', { graceDays, deleted: rows.length });
296 return rows.length;
297}