Data Import

Data Import

Component File: src/features/admin/components/DataImportManager.tsx Route / navigation: Page /administration, Zustand administrationTool = data-import. See 00-ADMINISTRATION-HUB.md. Access Level: Admin role · Plus tier or above Status: Implemented (QuickBooks Online), Aplos placeholder

Overview

Data Import is the Administration tool for pulling accounting and donor data from external systems into Alignmint. It replaces the previous "AI data import — coming soon" modal with a real routed page that hosts one card per provider:

| Provider | Status | Notes |

|---|---|---|

| **QuickBooks Online** | Active | OAuth 2.0 + read-only broad-object import |

| **Aplos** | Placeholder | Visible card, disabled actions, "coming soon" |

User-visible strings live under admin.dataImportPage in src/i18n/locales/*/auth.json for all seven supported locales.

Access Control

The tool slug data-import is mapped to administration in `src/lib/permissions.ts` and requires plus tier minimum (TOOL_MINIMUM_TIER['data-import'] = 'plus'). It is intentionally excluded from the global search index by `searchIndex.parity.test.ts` — integrations are reached via the Administration hub, not search.

Edge functions also call guardExternalServiceAccess to block demo orgs.

Architecture

Frontend

DataImportManager.tsx
├── data-import/QuickBooksProviderCard.tsx     // status + actions
├── data-import/AplosProviderCard.tsx          // disabled placeholder
├── data-import/ImportRunsDialog.tsx           // run history
└── data-import/useQuickBooksConnection.ts     // status / OAuth / import hook

The page resolves the org UUID exactly like Payment Integrations (getActualOrgId / getParentOrgId) and passes it to the connection hook. React Query handles polling: when any run is queued or running, the runs query polls every 5 s.

Backend

| Resource | Purpose |

|---|---|

| `supabase/functions/quickbooks-auth-callback` | OAuth GET callback + POST actions (`get-auth-url`, `check-status`, `disconnect`). Mirrors the `google-auth-callback` shape. |

| `supabase/functions/quickbooks-import` | POST `start` action — refreshes tokens if needed, walks the requested QBO entities, upserts payloads into `quickbooks_external_links`, advances `quickbooks_sync_cursor`, and records totals in `quickbooks_sync_runs`. |

| `quickbooks_integrations` table | One row per organization. Stores realm id, tokens, environment, scopes, status, last refresh. |

| `quickbooks_sync_runs` table | Append-only run history. Drives the history dialog. |

| `quickbooks_external_links` table | Idempotent mapping `(org, realm, object_type, external_id) → payload + checksum + optional internal_id`. |

| `quickbooks_sync_cursor` table | Per-`(org, object_type)` `MetaData.LastUpdatedTime` watermark for incremental pulls. |

Frontend code never imports tokens or talks to Intuit directly. All calls flow through src/lib/db/quickbooks.ts which wraps supabase.functions.invoke.

Data flow

AdministrationHub
   └─▶ DataImportManager
          ├─ QuickBooksProviderCard ──▶ supabase.functions.invoke('quickbooks-auth-callback', { action: 'get-auth-url' })
          │                                                    │
          │                                                    ▼
          │                                        Intuit OAuth consent
          │                                                    │
          │           GET /functions/v1/quickbooks-auth-callback?code=…&realmId=…&state=…
          │                                                    │
          │                                                    ▼
          │                                       quickbooks_integrations (upsert)
          │                                                    │
          │           302 redirect → /?page=administration&admin_tool=data-import&qbo_connected=true
          │                                                    ▼
          │                                              toast + refetch
          │
          ├─ "Import now" ──▶ supabase.functions.invoke('quickbooks-import', { action: 'start' })
          │                                                    │
          │                                                    ▼
          │                                ensureFreshAccessToken (refresh if < 5 min)
          │                                                    │
          │                                                    ▼
          │                          for each requested QBO entity:
          │                            QBO query (paginated, MAXRESULTS 200)
          │                            upsert into quickbooks_external_links
          │                            advance quickbooks_sync_cursor
          │                                                    │
          │                                                    ▼
          │                          quickbooks_sync_runs (status, totals, completed_at)
          │
          └─ "View history" ──▶ ImportRunsDialog (fetchQuickBooksImportRuns)

Required environment variables

Set on the Supabase project (the auth-callback + import functions both read them):

| Variable | Purpose |

|---|---|

| `QBO_CLIENT_ID` | Intuit app client id |

| `QBO_CLIENT_SECRET` | Intuit app client secret |

| `QBO_ENVIRONMENT` | `production` (default) or `sandbox` |

| `APP_URL` | Origin Intuit redirects users back to (e.g. `https://alignmint.app`) |

Tokens are stored in quickbooks_integrations.{access,refresh}_token_encrypted columns. TODO: wrap them in Supabase Vault / pgsodium when the secret-store rollout lands; today the columns hold the raw bearer string.

Operational runbook

Connect a new org

1. User opens Administration → Data Import while logged in to the target org. 2. Clicks Connect QuickBooks. 3. Frontend calls quickbooks-auth-callback with action: 'get-auth-url'. 4. Browser is redirected to Intuit consent screen. After consent Intuit calls our GET callback with code, realmId, and state. 5. The callback exchanges the code for tokens, fetches the CompanyInfo, upserts quickbooks_integrations, and 302s back to /?page=administration&admin_tool=data-import&qbo_connected=true. 6. The hook in useQuickBooksConnection reads qbo_connected, fires a success toast, scrubs the URL, and refetches status.

Run an import

1. Card shows Connected with company name, environment, last import time. 2. Import now posts { action: 'start', organizationId, profile: { objectTypes: [], fullResync: false } }. 3. Empty objectTypes means "all supported entities". The function caps each entity at 25 pages × 200 rows per request to stay inside Edge Function timeouts. Subsequent runs use the saved updated_since watermark so only deltas are fetched. 4. The runs query (5-second polling while the run is active) updates the table in the History dialog.

Reconnect / token expired

The status surface returns token_expired when the refresh token is past its expiry. The card swaps the primary CTA to Reconnect which restarts the OAuth flow and overwrites the existing row (onConflict: organization_id).

Disconnect

Calls action: 'disconnect' which: 1. Posts the refresh token to Intuit's /v2/oauth2/tokens/revoke endpoint (best effort — failures are logged, not surfaced). 2. Deletes the quickbooks_integrations row. Sync runs and external links are left in place (audit trail).

QA checklist

duplicate rows.

counts (idempotent upsert) without duplicating downstream data.

appear in quickbooks_external_links with a new payload_checksum.

guardExternalServiceAccess.

gating; the page itself shows the Upgrade required alert.

• OAuth happy path: connect, see toast, see "Connected" badge with company.
• OAuth denial: cancel on Intuit screen, expect qbo_error=access_denied toast.
• Reconnect after refresh-token expiry: status surfaces "Reconnect required".
• Disconnect + reconnect from a clean slate: integration row replaced, no
• Run import twice without changes: second run should report imported
• Run import after a deliberate edit in QBO: only the changed object should
• Demo org: import button surfaces the demo-block error from
• Free tier: tool tile shows paywall upsell from existing TOOL_MINIMUM_TIER

Non-goals (this iteration)

• No Aplos OAuth or sync pipeline — placeholder card only.
• No bidirectional write-back into QBO (no creating invoices/payments back).
• No conversion of existing CSV import surfaces.

Synced from IFMmvp-Frontend documentation: pages/administration/08-DATA-IMPORT.md