LGL Sync Audit (UIEngine v3)

This document is a read-only, code-derived reference of the current Little Green Light (LGL) synchronization system in ui-v3.

1) File Map

Core LGL Sync Orchestration

• src/services/lgl-sync.ts - Central orchestration for syncing users, memberships, payments/gifts, enrollments, and family relationships to LGL.
• src/clients/lgl.ts - Low-level LGL API client with auth, retry/backoff, rate limiting, fund/payment-type/membership-level mapping, and relationship helpers.
• src/services/sync-queue.ts - Sync queue service for enqueueing and tracking LGL/WordPress/messaging jobs.
• src/services/sync-queue-processor.ts - Background queue worker that routes LGL jobs to lgl-sync handlers.

Database Layer and Schema

• src/db/index.ts - Exports lglSettingsRepo, syncJobRepo, auditLogRepo, and entity repos used by LGL sync flow.
• src/db/migrations/006_lgl_settings.sql - Creates LGL settings storage used for API/fund/payment/membership/relationship configuration.
• src/db/migrations/010_lgl_matching_audit.sql - Adds constituent matching audit table for dedupe/linking diagnostics.
• src/db/migrations/015_add_lgl_membership_id.sql - Adds LGL membership linkage tracking on local membership records.
• src/db/migrations/049_add_lgl_relationship_columns.sql - Adds LGL relationship linkage fields.
• src/db/migrations/050_add_lgl_relationship_type_to_invitations.sql - Adds relationship type support in invitation flow.

Triggers, Queue Producers, and Business Flows

• src/routes/webhooks.ts - Stripe webhook ingestion and primary source of queued LGL sync jobs.
• src/routes/checkout.ts - Checkout creation flow that writes payment metadata consumed by webhook/LGL sync and handles zero-dollar orders.
• src/routes/checkout-scholarship.ts - Scholarship checkout flow, including $0 payment records that still queue LGL payment sync.
• src/services/checkout-handlers.ts - Shared post-purchase handlers that create enrollment/membership outcomes and enqueue related sync jobs.
• src/services/membership.ts - Membership creation/renewal logic that enqueues membership sync jobs.
• src/services/enrollment.ts - Enrollment confirmation flow that enqueues enrollment sync jobs.

Admin API Endpoints (LGL + Sync Ops)

• src/routes/admin/sync.ts - Admin routes for sync jobs listing, retry, manual LGL sync actions, and LGL connection test.
• src/routes/admin/settings.ts - Unified settings API including LGL API config updates and cache invalidation.
• src/routes/admin/lgl.ts - LGL-specific admin configuration and utility endpoints (funds, mapping, relationship types, test scenarios, queue helpers).

Admin UI Surfaces

• admin/app/admin/sync/page.tsx - General sync jobs page (cross-target, includes retry action).
• admin/app/admin/sync/lgl/page.tsx - Legacy/alternate dedicated LGL sync operations page (manual sync, queue, test scenarios).
• admin/app/admin/system/lgl/page.tsx - Current LGL Sync Hub with users/payments/memberships/relationships/queue tabs.
• admin/app/admin/settings/lgl/page.tsx - LGL settings UI (environment, API config, payment/membership/relationship mapping, sync toggles).
• admin/app/components/Sidebar.tsx - Navigation links to LGL Sync Hub and settings.

Additional LGL References

• src/config/env.ts - Environment-level LGL variables and scholarship group IDs used by sync services.
• src/types/* and packages/shared/src/types/* - Type definitions for sync entities consumed in queue/LGL handlers.

2) What Gets Synced to LGL

User / Constituent Sync

UIEngine Data Sent

From users record:

• id (as external id)
• email
• first_name, last_name
• phone
• billing_address_1, billing_address_2, billing_city, billing_state, billing_postcode, billing_country
• existing linkage: lgl_constituent_id

LGL Object Mapping

• UIEngine users -> LGL constituents
• Local users.lgl_constituent_id stores LGL constituent ID

Payload Fields (Constituent + Contact)

Constituent create/update payload includes:

• first_name, last_name
• external_constituent_id (UIEngine user.id)
• default profile fields (is_org, contact type, addressee/salutation/sort fields, etc.)

Contact info is synced separately:

• Email -> LGL emails endpoint
• Phone -> LGL phones endpoint
• Address -> LGL addresses endpoint (street, city, state, postal_code, country)

Conditional Logic and Deduplication

• If local lgl_constituent_id exists, system re-validates linkage by checking LGL email(s).
• If existing LGL constituent email mismatches local user email, linkage is cleared and user is treated as needing new/matching lookup.
• Matching strategy uses name search + client-side email filtering (searchByNameAndEmail); direct email-only search is intentionally deprecated.
• If exact match not found, a new LGL constituent is created.

Handler

• src/services/lgl-sync.ts -> syncUserToLGL(userId)
• Uses src/clients/lgl.ts -> findOrCreateConstituent, updateConstituent, syncConstituentContactInfo

Membership Sync

UIEngine Data Sent

From memberships and linked users:

• membership.id, user_id, level, status
• start_date, renewal_date
• subsidy_tier (scholarship handling)
• product/membership-level context for mapping

LGL Object Mapping

• UIEngine memberships -> LGL memberships
• Local memberships.lgl_membership_id stores LGL membership ID

Payload Fields (Membership)

• constituent_id
• membership_level_id (mapped via product/global mapping)
• date_start <- UIEngine start_date
• finish_date <- UIEngine renewal_date
• is_current: true
• note with sync context

Conditional Logic

• If user has no LGL constituent, syncUserToLGL runs first.
• Before creating a new LGL membership, existing current LGL memberships for that constituent are deactivated (finish_date set to yesterday or start-date-safe fallback, is_current=false).
• Scholarship tiers can add user to configured LGL groups:
  • full scholarship -> LGL_GROUP_ID_SCHOLARSHIP_FULL
  • partial scholarship -> LGL_GROUP_ID_SCHOLARSHIP_PARTIAL

Handler

• src/services/lgl-sync.ts -> syncMembershipToLGL(membershipId)

Payment / Gift Sync

UIEngine Data Sent

From payments + optional payment_items + related user/enrollment/product context:

• payment.id, user_id, type, status, gateway
• amount_cents, created_at, stripe_payment_intent_id
• lgl_gift_id (idempotency marker)
• line items for cart splitting

LGL Object Mapping

• UIEngine payments -> LGL gifts
• Local payments.lgl_gift_id stores resulting gift linkage (or multiple gifts in cart mode tracked through process; primary linkage remains on payment record)

Exact Gift Payload Fields

Gift payload uses:

• constituent_id
• received_date
• received_amount (amount_cents / 100)
• fund_id (from fund mapping)
• campaign_name (from fund mapping)
• payment_type_id (from gateway mapping)
• external_id (usually payment.id, cart may append suffix per grouped subtype)
• note (includes payment intent/status/date context)

Gift Type Values, Triggers, and Assignment

Gift type routing is driven by payment.type and item/product context in syncPaymentToLGL:

• membership
  • Fund config key: membership
  • Calls createMembershipGift
• enrollment
  • Looks up related enrollment/product type
  • class -> fund key language_classes, calls createClassGift
  • event -> fund key events, calls createEventGift
  • fallback -> membership fund/key
• donation
  • Fund key donations, calls createGift
• cart
  • Splits/group items by product category and creates one gift per group
  • Membership-like items (including family add-on handling) route to membership config
  • Class items route to language classes config
  • Event items route to events config
  • Donation items route to donations config

Assignment location: src/services/lgl-sync.ts in syncPaymentToLGL.

Conditional Logic

• Only succeeded payments sync (status === "succeeded").
• If lgl_gift_id already exists, sync is skipped unless force=true.
• Ensures user has valid LGL constituent; may trigger syncUserToLGL.
• After successful membership-related payment sync, active membership sync is triggered to keep LGL membership current.

Handler

• src/services/lgl-sync.ts -> syncPaymentToLGL(paymentId, { force? })

Enrollment Sync

UIEngine Data Sent

• Enrollment entity identifiers/status are recorded locally and in audit logs.
• Monetary and donor-facing representation in LGL is primarily via payment->gift sync, not a standalone enrollment object.

LGL Object Mapping

• UIEngine enrollments are represented indirectly as gift records tied to class/event fund/campaign.

Conditional Logic

• Enrollment sync job handler mainly logs successful sync marker.
• Payment sync is the authoritative path for gift creation tied to enrollment transactions.

Handler

• src/services/lgl-sync.ts -> syncEnrollmentToLGL(enrollmentId)

Event Registration Sync

UIEngine Data Sent

• Event registrations are a subtype of enrollment/product purchase.
• Payment details and product type (event) determine gift categorization.

LGL Object Mapping

• Event registration revenue -> LGL gifts with event fund/campaign mapping.

Conditional Logic

• Triggered by successful payment pipeline.
• Product typed as event routes to event gift creation (createEventGift).

Handler

• Event path is implemented through syncPaymentToLGL and enrollment/payment metadata.

Family / Relationship Sync

UIEngine Data Sent

From family member relationship records:

• primary_user_id, member_user_id
• optional lgl_relationship_type_id
• existing lgl_relationship_id

LGL Object Mapping

• UIEngine family link -> LGL constituent relationship
• Local family_members.lgl_relationship_id stores linked relationship record

Payload Fields (Relationship)

• constituent_id (primary)
• related_constituent_id (member)
• relationship_type_id
• sharing flags (address/phone/credit/ack settings) as defined in client helper

Conditional Logic

• If relation already has lgl_relationship_id, skip.
• If either user missing LGL constituent, sync users first.
• Prefer configured/default relationship type ID.
• Legacy fallback path exists for deprecated string-based relationship helper when type ID unavailable.

Handler

• src/services/lgl-sync.ts -> syncFamilyRelationshipToLGL(primaryUserId, memberUserId)

3) Sync Queue Architecture

Where Jobs Are Created

Primary enqueue sites:

• Stripe webhook handlers in src/routes/webhooks.ts (payment success and checkout completion paths)
• Membership lifecycle in src/services/membership.ts
• Enrollment confirmation in src/services/enrollment.ts and src/services/checkout-handlers.ts
• Scholarship checkout paths in src/routes/checkout-scholarship.ts
• Admin manual queue endpoints in src/routes/admin/sync.ts / src/routes/admin/lgl.ts

Job creation call:

• queueSyncJob(target, entityType, entityId) in src/services/sync-queue.ts

Queue Model / Table

Backed by sync_jobs via syncJobRepo in src/db/index.ts.

Core fields used operationally:

• id
• target (lgl, wordpress, messaging)
• entity_type (e.g. user, payment, membership, enrollment, relationship)
• entity_id
• status (queued, running, succeeded, failed)
• attempts
• last_error
• created_at, updated_at

Job Triggers

• Stripe events (checkout.session.completed, payment_intent.succeeded) create or update domain records, then enqueue LGL jobs.
• Membership and enrollment service methods enqueue entity-specific sync jobs as domain state transitions to paid/active.
• Admin UI can manually queue or directly invoke sync handlers.

Background Processor

In src/services/sync-queue-processor.ts:

• Poll interval: PROCESS_INTERVAL_MS = 30000 (30s)
• Batch/concurrency: MAX_CONCURRENT_JOBS = 3
• Fetches pending jobs and processes in parallel
• Routes by target and entity type (routeLGLSync for LGL)

Retry and Status Lifecycle

Queue service constants in src/services/sync-queue.ts:

• MAX_RETRY_ATTEMPTS = 3
• RETRY_DELAY_MS = 5000

Lifecycle:

• New job -> queued
• Worker picks job -> running
• Success -> succeeded
• Error -> failed (and retries according to attempts policy / retry endpoint)

Manual retry:

• Admin endpoint resets failed jobs back to queue flow (POST /admin/sync/retry/:jobId)

LGL API Rate Limiting Interaction

Queue controls job concurrency; LGL client adds request-level controls:

• hard cap window: 300 requests / 5 minutes
• minimum inter-request delay: ~1.1 seconds
• availability wait when near cap

This creates two layers of throughput control:

• job-level (sync-queue-processor)
• request-level (src/clients/lgl.ts RateLimiter)

4) Admin UI — What Staff Can See and Do

/admin/sync

UI file: admin/app/admin/sync/page.tsx

What staff sees:

• Sync jobs table across targets (LGL/WordPress/etc.)
• Filters by status (all/pending/failed)
• Columns include target, entity type, status, attempts, last error, timestamps

What staff can do:

• Retry failed jobs (POST /api/admin/sync/retry/:jobId)
• Navigate to LGL-specific sync view and other target views

/admin/sync/lgl

UI file: admin/app/admin/sync/lgl/page.tsx

What staff sees:

• Connection status and rate-limit summary
• Manual payment sync controls by payment ID
• Pending vs synced payment tabs with details
• Test scenario utilities (create/cleanup test records)
• Additional tabs for users, memberships, relationships, and LGL queue summaries

What staff can do:

• Test LGL connectivity (GET /api/admin/lgl/test-connection)
• Trigger immediate payment sync (POST /api/admin/lgl/sync/payment/:paymentId)
• Queue payment sync (POST /api/admin/lgl/sync/queue-payment/:paymentId)
• Force re-sync in test mode where exposed
• Trigger user/membership/relationship sync actions via dedicated endpoints
• Create/delete LGL test scenarios/data

/admin/system/lgl (LGL Sync Hub)

UI file: admin/app/admin/system/lgl/page.tsx

What staff sees:

• Connection banner (API key configured, environment/base URL cues, rate limit health)
• Tabbed views:
  • Users (LGL constituent linkage + last sync)
  • Payments (pending/synced, gift IDs)
  • Memberships (membership linkage/status)
  • Relationships (relationship linkage/status)
  • Queue (LGL-only jobs, status/attempts/errors)

What staff can do:

• Test connection
• Sync/re-sync users manually
• Sync pending payments manually or queue them
• Sync memberships manually
• Queue relationship sync
• Refresh queue data and jump to full system queue page

/admin/settings/lgl

UI file: admin/app/admin/settings/lgl/page.tsx

What staff sees:

• Environment selector (sandbox/production)
• API key/base URL config controls
• Payment gateway mapping editor
• Membership level mapping editor
• Relationship type enable/default controls
• Sync behavior toggles

What staff can do:

• Save API config (POST /api/admin/lgl/config/api)
• Switch environment (POST /api/admin/lgl/environment)
• Test connection
• Load/refresh LGL payment types and save gateway mappings
• Load/refresh LGL membership levels and save product mappings
• Run mapping maintenance actions (product ID mismatch fixer / migration helper)
• Load/refresh relationship types and save enabled/default configuration
• Save sync toggles (auto_sync, sync_on_payment, sync_on_membership)

Connection Test Implementation

Connection tests call:

• Admin API: GET /admin/lgl/test-connection
• Service: testLGLConnection() in src/services/lgl-sync.ts
• Client: testConnection() in src/clients/lgl.ts (read request against LGL constituents endpoint)

Response includes:

• success/failure status
• configured state
• rate-limit metadata when available

5) LGL API Integration Layer

Client Location and Responsibility

• src/clients/lgl.ts contains the complete integration adapter for all LGL HTTP operations.
• It centralizes auth header creation, environment/base URL selection, retries, and response parsing.

Authentication

• Header: Authorization: Bearer <apiKey>
• API key source precedence:
  1. lgl_settings (api_config) via lglSettingsRepo
  2. process.env.LGL_API_KEY fallback
• Base URL source precedence:
  1. lgl_settings (api_config)
  2. process.env.LGL_API_BASE_URL fallback

LGL Endpoints Called (By Capability)

Constituents/contact:

• constituents list/search endpoints (name-based query)
• create/update constituent endpoint
• constituent email/phone/address subresource endpoints (delete + add pattern)

Gifts:

• create gift endpoint for generic and category-specific wrappers (membership, class, event)

Membership:

• list constituent memberships
• patch existing membership
• create membership

Relationships:

• list relationship types
• create constituent relationship
• list/delete constituent relationships (when needed)

Reference/config pull:

• payment types list
• membership levels list

Error Handling

request() in lgl.ts:

• Parses diverse error response structures into a normalized message when possible.
• Marks specific statuses retryable: 429, 500, 502, 503, 504.
• Non-retryable 4xx errors surface immediately.

Retry / Backoff

API-level retry defaults:

• max retries: 3
• base delay: 1000ms
• exponential backoff progression

Queue-level retries are separate (job lifecycle retries in sync-queue).

Rate Limiting Compliance

RateLimiter in lgl.ts enforces:

• max 300 requests / 300 seconds
• minimum spacing between calls (~1100ms)
• wait-for-availability when at/near cap

This makes LGL request pacing independent of queue burst behavior.

6) Configuration

Environment Variables

Used by LGL sync stack (with DB override patterns):

• LGL_API_KEY
• LGL_API_BASE_URL
• LGL_ENVIRONMENT (sandbox / production)
• LGL_GROUP_ID_SCHOLARSHIP_FULL
• LGL_GROUP_ID_SCHOLARSHIP_PARTIAL

Database Configuration (lgl_settings)

Stored/retrieved through lglSettingsRepo:

• API config (api_config: key/base URL/environment)
• Fund mappings (by sync category, includes fund ID + campaign)
• Payment gateway -> LGL payment type mappings
• Membership product/level -> LGL membership level mappings
• Relationship type configuration (enabled set + default ID)
• Sync behavior flags (auto-sync and trigger-specific toggles)
• Cached LGL reference data (relationship types/payment types/membership levels)

Admin-Configurable Mapping Areas

• Fund/campaign mapping (membership, language classes, events, donations)
• Gateway payment type mapping (Stripe and others as available)
• Membership level mapping per product/global default
• Relationship type enable/default selections

Hardcoded or Semi-Hardcoded Values to Note

• Queue defaults: 30s poll, 3 concurrent jobs
• Queue retry defaults: 3 attempts, 5s retry delay
• LGL client retry defaults: 3 attempts, 1s base delay, specific retryable statuses
• LGL rate limiter constants: 300/5min + 1.1s min delay
• Constituent defaults (contact type and naming defaults) inside createConstituent
• Legacy relationship fallback behavior exists when no relationship type ID is configured

7) Known Edge Cases and Failure Modes

Constituent Matching / Dedup Risks

• LGL API cannot do direct email query; matching relies on name query plus client-side email filter.
• Same-name constituents with different emails can still be ambiguous in edge populations.
• If local linkage exists but email mismatch is detected, linkage is cleared and a new/matching flow runs (may produce additional constituents if source data is inconsistent).

Invalid or Missing API Configuration

• Missing/invalid API key or base URL causes connection test and sync requests to fail early.
• Admin UI surfaces configured/not-configured state and provides test endpoint, but background jobs can still fail until config is corrected.

LGL Downtime / 5xx / Rate-Limit Responses

• Request-level retries absorb transient outages (429/5xx), but persistent outages still push jobs to failed state.
• If failures exceed retry windows, manual retry or operational intervention is required.

Persistent Job Failures

• Jobs can remain failed due to bad entity references, mapping gaps, invalid relationship type IDs, or upstream API errors.
• Staff must use queue views and retry actions after correcting root cause.

Enrollment/Event Representation

• Enrollment sync is mostly audit/status level; revenue record in LGL is driven by payment->gift path.
• Missing or unusual enrollment-product associations can route fallback gift logic (e.g., defaulting toward membership fund path).

Membership State Drift Scenarios

• Membership sync intentionally deactivates existing LGL memberships before creating current one; mis-timed local date fields can temporarily reflect unexpected finish dates.

Relationship Type Configuration Gaps

• If no relationship type ID is configured/available, code can fall back to deprecated relationship helper behavior.

Noted TODO/FIXME/Deprecation Signals

In src/clients/lgl.ts:

• findConstituentByEmail is explicitly deprecated/broken for real matching use.
• older relationship helper methods are deprecated in favor of createConstituentRelationship.

8) Data Flow Diagram (text-based)

End-to-End Sync Flow

[Member Action in Portal/Admin]
   |
   |-- checkout/membership | checkout/enrollment | checkout/cart | scholarship flow
   v
[Local DB Writes]
   - payment (pending/succeeded)
   - payment_items (cart)
   - membership/enrollment/family records as applicable
   |
   v
[Stripe Event or Direct Zero-$ Completion]
   |
   |-- webhooks.ts handles event
   |   - verifies signature
   |   - dedupes event
   |   - updates payment state
   |   - creates/updates membership/enrollment outcomes
   |   - queues sync jobs (target=lgl, entity_type=...)
   v
[sync_jobs table]
   status: queued -> running -> succeeded/failed
   attempts increment on failures/retries
   |
   v
[sync-queue-processor (30s interval, up to 3 jobs)]
   |
   |-- routeLGLSync(entity_type)
   |    -> lgl-sync.ts
   |       - syncUserToLGL
   |       - syncPaymentToLGL
   |       - syncMembershipToLGL
   |       - syncEnrollmentToLGL
   |       - syncFamilyRelationshipToLGL
   v
[lgl.ts client request()]
   - loads API config (DB/env)
   - applies rate limiting (300/5min, 1.1s min delay)
   - calls LGL endpoints
   - retries transient 429/5xx
   |
   +--> [Success]
   |      - update local lgl_* linkage ids
   |      - write audit logs
   |      - mark job succeeded
   |
   +--> [Failure]
          - capture error
          - mark failed/retry path
          - visible in Admin Queue UI for manual retry

Staff Observation and Intervention Loop

Admin UI (/admin/system/lgl, /admin/sync, /admin/settings/lgl)
   |
   |-- observe queue failures, unsynced entities, connection health
   |-- test connection
   |-- fix config/mappings
   |-- retry or manually trigger sync
   v
System re-enters queue/client flow until jobs succeed