Exports & Imports — Data Model
export_jobs (libs/db/src/lib/schema/exports.ts)
| Column | Type | Notes |
|---|---|---|
id | uuid PK | Job identity. |
organization_id | uuid FK | Scope. |
type | varchar | csv_members today; expandable. |
status | enum | pending → in_progress → completed / failed. |
config | jsonb | ExportMembersDto. |
started_by | uuid FK → users.id | Requester (for email + audit). |
started_at, completed_at, updated_at, created_at | timestamps | Lifecycle. |
file_key | varchar nullable | R2 key on success (exports/<orgId>/<jobId>.csv). |
results | jsonb nullable | { rowCount, fileSize } on success. |
error_message | text nullable | Populated on failure. |
import_jobs (libs/db/src/lib/schema/imports.ts)
| Column | Type | Notes |
|---|---|---|
id | uuid PK | Job identity. |
organization_id | uuid FK | Scope. |
source | enum | arbox or csv. |
status | enum | pending → in_progress → completed / failed / cancelled. |
phase | varchar | Free-form phase label (plans, members, enrichment, done). |
config | jsonb | Source-specific. Arbox: ImportConfigDto. CSV: { parsedRows, headers, suggestedMapping, columnMapping?, entities?, memberFields?, planFields? }. |
started_by | uuid FK | Requester. |
started_at, completed_at, updated_at, created_at | timestamps | Lifecycle. |
results | jsonb | Per-entity { created, skipped, failed, errors[] }; Arbox jobs add enrichment counters and receipts: { imported, windows, failedWindows[] }. |
error_message | text nullable | Orchestration-level failure. |
import_provider_configs (libs/db/src/lib/schema/imports.ts)
| Column | Type | Notes |
|---|---|---|
id | uuid PK | Row identity. |
organization_id | uuid FK | Scope. |
provider | enum | arbox. |
credentials_encrypted | text | Encrypted JSON { apiKey, email?, password? }. |
is_active | boolean | Only one active row per (org, provider). |
created_at, updated_at | timestamps | Audit. |
Encryption uses CredentialEncryptionService (shared with payment providers). FIT-120: rotating CREDENTIAL_ENCRYPTION_KEY makes old rows unreadable — re-enter credentials after rotation.
imported_receipts (libs/db/src/lib/schema/imports.ts)
Receipts a member received under the gym’s previous system. History only: never read by billing, refunds or revenue analytics.
| Column | Type | Notes |
|---|---|---|
id | uuid PK | Row identity. |
organization_id | uuid FK | Scope. |
membership_id | uuid FK, cascade | The member the receipt belongs to. |
import_job_id | uuid FK, set null | The import that copied it. |
source | import_source | arbox. |
external_id | varchar(64) | Source transaction id. Unique with org + source: re-runs skip existing rows. |
issued_at | timestamptz | Source’s box-local time converted with the org timezone. |
description | text nullable | Item name on the document. |
amount_in_cents, currency | integer, varchar(3) | Amount; currency is the org’s. |
installments | integer nullable | Number of card payments. |
card_last_four | varchar(4) nullable | Only a real four-digit suffix is kept. |
document_url | text | Bearer link to the provider’s PDF. Staff-only reads; deleted on member erasure. |
created_at | timestamptz | Copy time. |
Tables written by imports
| Table | Write |
|---|---|
users | INSERT (new emails) or UPDATE (existing emails — merge non-null source fields). |
memberships | INSERT a member role row in active status (or per arboxStatus). |
member_profiles | INSERT/UPDATE — full profile mirror including arbox_user_id for back-reference. The collected national/personal ID is written encrypted into personal_id (AES-256-GCM via NationalIdEncryptionService; the column is text to fit ciphertext). Readers (member CSV export, invoice tax ID in PlansService) decrypt it, returning any non-decryptable legacy plaintext as-is. Use scripts/encrypt-personal-ids.ts to encrypt pre-existing plaintext rows in place. |
plans | INSERT new plans by name + price; updates existing on name collision. |
leads (when entity targeted) | INSERT lead rows from CSV. |
imported_receipts (Arbox enrichment) | INSERT … ON CONFLICT DO NOTHING per receipt of an imported member. |
R2
exports/<orgId>/<jobId>.csv— UTF-8 with BOM.
- Sent from
notifications/email.service.tsusing theexport-ready.tstemplate with a 1h presigned download URL.
Queues
| Queue | Job data | Producer | Consumer |
|---|---|---|---|
export | { jobId, orgId } | ExportService.enqueueExport | ExportProcessor |
import | { jobId, orgId } | ImportService.{enqueueImport, confirmCsvImport} | ImportProcessor |
import-member | ImportMemberJobData | ImportService.fanOutMemberJobs | ImportMemberProcessor |
import-enrich | EnrichMemberJobData | ImportService enrichment phase | ImportEnrichProcessor |
member-profiles-enrichment | { memberProfileId } | ImportService (downstream) | embeddings worker |