property-management-network/lib/gdpr/export.ts

import { desc, eq, or } from "drizzle-orm"
import { db } from "@/lib/db"
import {
  user as userTable,
  session,
  account,
  profiles,
  properties,
  units,
  tenants,
  rent_payments,
  maintenance_requests,
  leases,
  expenses,
  documents,
  notifications,
  usage_events,
  vendors,
  inspections,
  ai_recommendations,
  ai_predictions,
  follow_up_rules,
  follow_up_log,
  activity_log,
  admin_audit_log,
  account_members,
  api_keys,
  accounting_connections,
  esign_connections,
  signature_requests,
  webhook_endpoints,
  consent_log,
  account_deletion_requests,
} from "@/lib/db/schema"

// ============================================================================
// GDPR data export (Articles 15 & 20 — access + portability).
//
// Produces a single machine-readable JSON object containing every record the
// platform stores about a user, EXCLUDING credentials and third-party secrets
// (password hashes, OAuth/API tokens, session tokens). What's excluded is
// declared in `omitted` so the export is honest about its own boundaries.
// ============================================================================

export async function buildUserDataExport(userId: string) {
  const [
    authUser,
    profile,
    sessions,
    linkedAccounts,
    propertyRows,
    unitRows,
    tenantRows,
    paymentRows,
    maintenanceRows,
    leaseRows,
    expenseRows,
    documentRows,
    vendorRows,
    inspectionRows,
  ] = await Promise.all([
    db.query.user.findFirst({
      where: eq(userTable.id, userId),
      columns: { id: true, name: true, email: true, emailVerified: true, image: true, createdAt: true },
    }),
    db.query.profiles.findFirst({ where: eq(profiles.id, userId) }),
    db.query.session.findMany({
      where: eq(session.userId, userId),
      columns: { token: false }, // active credential — never exported
      orderBy: desc(session.createdAt),
    }),
    db.query.account.findMany({
      where: eq(account.userId, userId),
      columns: { id: true, providerId: true, accountId: true, scope: true, createdAt: true },
    }),
    db.query.properties.findMany({ where: eq(properties.user_id, userId) }),
    db.query.units.findMany({ where: eq(units.user_id, userId) }),
    db.query.tenants.findMany({ where: eq(tenants.user_id, userId) }),
    db.query.rent_payments.findMany({ where: eq(rent_payments.user_id, userId) }),
    db.query.maintenance_requests.findMany({ where: eq(maintenance_requests.user_id, userId) }),
    db.query.leases.findMany({ where: eq(leases.user_id, userId) }),
    db.query.expenses.findMany({ where: eq(expenses.user_id, userId) }),
    db.query.documents.findMany({ where: eq(documents.user_id, userId) }),
    db.query.vendors.findMany({ where: eq(vendors.user_id, userId) }),
    db.query.inspections.findMany({ where: eq(inspections.user_id, userId) }),
  ])

  const [
    notificationRows,
    usageRows,
    activityRows,
    recommendationRows,
    predictionRows,
    followUpRuleRows,
    followUpLogRows,
    apiKeyRows,
    webhookRows,
    accountingRows,
    esignRows,
    signatureRows,
    teamRows,
    adminActionsOnUser,
    consentRows,
    deletionRows,
  ] = await Promise.all([
    db.query.notifications.findMany({ where: eq(notifications.user_id, userId) }),
    db.query.usage_events.findMany({ where: eq(usage_events.user_id, userId) }),
    db.query.activity_log.findMany({ where: eq(activity_log.user_id, userId) }),
    db.query.ai_recommendations.findMany({ where: eq(ai_recommendations.user_id, userId) }),
    db.query.ai_predictions.findMany({ where: eq(ai_predictions.user_id, userId) }),
    db.query.follow_up_rules.findMany({ where: eq(follow_up_rules.user_id, userId) }),
    db.query.follow_up_log.findMany({ where: eq(follow_up_log.user_id, userId) }),
    db.query.api_keys.findMany({
      where: eq(api_keys.user_id, userId),
      columns: { key_hash: false },
    }),
    db.query.webhook_endpoints.findMany({ where: eq(webhook_endpoints.user_id, userId) }),
    db.query.accounting_connections.findMany({
      where: eq(accounting_connections.user_id, userId),
      columns: { access_token: false, refresh_token: false },
    }),
    db.query.esign_connections.findMany({
      where: eq(esign_connections.user_id, userId),
      columns: { access_token: false, refresh_token: false },
    }),
    db.query.signature_requests.findMany({ where: eq(signature_requests.user_id, userId) }),
    db.query.account_members.findMany({
      where: or(eq(account_members.owner_id, userId), eq(account_members.member_id, userId)),
      columns: { invite_token: false },
    }),
    db.query.admin_audit_log.findMany({
      where: eq(admin_audit_log.target_user_id, userId),
      columns: { action: true, created_at: true },
    }),
    db.query.consent_log.findMany({ where: eq(consent_log.user_id, userId) }),
    db.query.account_deletion_requests.findMany({
      where: eq(account_deletion_requests.user_id, userId),
    }),
  ])

  return {
    format: "propertymanagement.network/data-export",
    version: 1,
    generated_at: new Date().toISOString(),
    omitted: [
      "password hashes, OAuth/refresh tokens, session tokens, and API-key hashes (credentials are never exported)",
      "webhook delivery logs (operational copies of the event records included above)",
      "uploaded file BYTES — file metadata is under portfolio.documents; download the files themselves from the Documents page",
    ],
    data_subject: { user: authUser ?? null, profile: profile ?? null },
    security: { sessions, linked_sign_in_providers: linkedAccounts },
    portfolio: {
      properties: propertyRows,
      units: unitRows,
      tenants: tenantRows,
      rent_payments: paymentRows,
      maintenance_requests: maintenanceRows,
      leases: leaseRows,
      expenses: expenseRows,
      documents: documentRows,
      vendors: vendorRows,
      inspections: inspectionRows,
    },
    communications: { notifications: notificationRows, follow_up_log: followUpLogRows },
    automation: {
      follow_up_rules: followUpRuleRows,
      webhook_endpoints: webhookRows,
      api_keys: apiKeyRows,
    },
    ai: { recommendations: recommendationRows, predictions: predictionRows },
    integrations: {
      accounting_connections: accountingRows,
      esign_connections: esignRows,
      signature_requests: signatureRows,
    },
    team: { memberships: teamRows },
    activity: { activity_log: activityRows, usage_events: usageRows },
    privacy: {
      consent_log: consentRows,
      deletion_requests: deletionRows,
      admin_actions_affecting_you: adminActionsOnUser,
    },
  }
}

export type UserDataExport = Awaited<ReturnType<typeof buildUserDataExport>>