Phase 7 — Shopee Connector Overhaul (Kickoff Plan)¶

What Phase 6 already delivered (don't rebuild this)¶

• shopee_environment_credentials table with per-env_type (sandbox|live) rows. This is historical after Phase 7: PR-5 migrated the rows into shopee_connector_profile and dropped the legacy table.
• Encrypted partner_key_encrypted + push_partner_key_encrypted via AES-256-GCM, key derived via HKDF-SHA256 from JWT_SECRET
• Single-active-environment switcher (login/logout-style UX in the dashboard)
• base_url_override text column (used today for the SG sandbox endpoint that the SDK's built-in TEST_GLOBAL doesn't cover)
• Path-routed webhook URLs per env (/connectors/shopee/webhook?env=<env>)

What Phase 7 changes¶

Schema: profiles, not environments¶

Replace the one-row-per-env_type model with a profile table. Multiple profiles per env_type are allowed, but exactly one is is_active per env_type (so outbound API calls + webhook routing still resolve to a single profile per env). The 2 existing rows migrate cleanly into 2 active profiles named "Sandbox" and "Live".

Region as enum, base_url as derived¶

The Phase 6 region text + base_url_override text combo gets replaced by:

• region ENUM with all 10 Shopee regions + their TEST_ equivalents (GLOBAL, CHINA, BRAZIL, plus regional codes: SG, MY, ID, TH, VN, PH, TW; each has a TEST_ sibling)
• base_url derived programmatically from (region, env_type) at runtime — no DB write
• base_url_override retained as an Advanced field for new sandbox URLs the enum doesn't yet cover (e.g. when Shopee introduces a new staging host)

Push URL + OAuth callback URL: app-config-derived¶

Both fields become DERIVED, not stored:

• Push URL: ${PUBLIC_API_BASE_URL}/connectors/shopee/webhook?env=${env_type}&profile_id=${id}
• OAuth callback URL: ${PUBLIC_API_BASE_URL}/connectors/shopee/oauth/callback/${env_type}?profile_id=${id}
• PUBLIC_API_BASE_URL is the only new env var (e.g. https://tcg-staging.exzentcg.com)

Default location: API-fetched dropdown¶

Replace the free-text default_location_id field with a typed dropdown populated from sdk.shop.getWarehouseDetail() (or sdk.logistics.getChannelList() for shipping options). Auto-fetch fires:

• Once on first successful OAuth (subscriber on shopee_auth_token.created)
• On-demand via a "Refresh Locations" button on the profile detail page

A separate cache table holds the resolved (profile_id, location_id, location_name) rows.

Token refresh: middleware + Postgres advisory lock¶

A new tokenRefresher service intercepts outbound Shopee API calls. Algorithm:

1. Read shopee_auth_token row for (env, shop_id)
2. If expired_at - 60s > now, return the token as-is
3. Else: SELECT pg_try_advisory_lock(hashtext('shopee-refresh:' || env || ':' || shop_id))
4. If lock acquired: re-read row (under lock), if still stale call SDK refresh, write new row in same transaction, release lock
5. If lock not acquired: poll DB every 200ms for ≤5s waiting for the OTHER process to write the new row, then return the new value
6. Wrap all sdk.product.*, sdk.logistics.*, etc. calls through this refresher

Shopee invalidates the refresh_token immediately on rotation, so this lock is load-bearing: two concurrent refreshes would race the rotation and one would lose.

Token storage encryption¶

Roll the same AES-256-GCM-via-HKDF pattern into shopee_auth_token. Phase 6 stored partner_key encrypted but left access_token + refresh_token plaintext (caught during the 2026-05-16 sandbox migration session — see project-phase-6-state memory for context). Phase 7 fixes that.

Diagnostic UI: metadata only¶

A new admin modal exposes (per profile):

• access_token_expires_at
• access_token_last_refreshed_at
• refresh_token_last_used_at
• scopes (parsed from OAuth response or stored at token creation)
• last_refresh_attempt_at + last_refresh_status (success | failure)
• last_refresh_error (truncated, for failures)

Token VALUES are never returned by the diagnostic endpoint. Operator debugging gets the metadata; the actual token bytes stay in Postgres + memory. Matches the same principle that drove the CT-side dump helper.

Locked design decisions (D1–D7)¶

D1 — Profile is a new table with N rows per env_type, one active per env_type¶

create table shopee_connector_profile (
  id text primary key,
  display_name text not null,
  env_type text not null check (env_type in ('sandbox', 'live')),
  region text not null check (region in (
    'GLOBAL','CHINA','BRAZIL','SG','MY','ID','TH','VN','PH','TW',
    'TEST_GLOBAL','TEST_CHINA','TEST_SG','TEST_MY','TEST_ID','TEST_TH','TEST_VN','TEST_PH','TEST_TW'
  )),
  partner_id numeric,
  partner_key_encrypted text,
  push_partner_key_encrypted text,
  shop_id numeric,
  base_url_override text,  -- advanced/escape hatch only
  is_active boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz
);

create unique index shopee_connector_profile_active_per_env
  on shopee_connector_profile (env_type)
  where is_active = true and deleted_at is null;

D2 — Region/base_url derivation¶

const SHOPEE_BASE_URLS: Record<Region, string> = {
  GLOBAL: 'https://partner.shopeemobile.com/api/v2',
  CHINA: 'https://openplatform.shopee.cn/api/v2/public',
  BRAZIL: 'https://openplatform.shopee.com.br/api/v2',
  SG: 'https://openplatform.shopee.sg/api/v2',
  // ... per-region production endpoints

  TEST_GLOBAL: 'https://partner.test-stable.shopeemobile.com/api/v2',
  TEST_CHINA: 'https://openplatform.test-stable.shopee.cn/api/v2',
  TEST_SG: 'https://openplatform.sandbox.test-stable.shopee.sg/api/v2',
  // ... per-region sandbox endpoints
}

function resolveBaseUrl(profile: Profile): string {
  return profile.base_url_override?.trim() || SHOPEE_BASE_URLS[profile.region]
}

D3 — Push URL + OAuth callback URL derived from PUBLIC_API_BASE_URL env var¶

Single source of truth. Existing per-row text columns deleted.

D4 — Default location: typed dropdown, fetched on demand + on OAuth success¶

New shopee_warehouse_location cache table keyed on (profile_id, location_id). Refreshed by:

• Subscriber on shopee_auth_token.created event (fires on first OAuth completion)
• POST /admin/dashboard/connectors/shopee/profiles/:id/refresh-locations (operator-triggered button)

D5 — Postgres pg_advisory_lock keyed on hashtext('shopee-refresh:' || env_type || ':' || shop_id)¶

Cross-process correct, no extra infra, lock auto-releases on connection close. The 200ms-poll-up-to-5s fallback handles the "I didn't get the lock" case without busy-waiting.

D6 — Encrypt access_token + refresh_token in shopee_auth_token¶

Same AES-256-GCM-via-HKDF-from-JWT_SECRET pattern as Phase 6's partner_key_encrypted. Single migration:

1. Add access_token_encrypted, refresh_token_encrypted columns
2. Backfill from existing plaintext columns
3. Drop the plaintext columns once the encrypted read/write path is live

Implementation note: Migration20260516220000_drop_legacy_credentials_and_plaintext_tokens dropped access_token and refresh_token during PR-5, after PR-3a/PR-3b had written and read encrypted values. The current model only exposes access_token_encrypted and refresh_token_encrypted.

D7 — Diagnostic UI returns metadata only¶

Endpoint: GET /admin/dashboard/connectors/shopee/profiles/:id/diagnostics

Response shape:

{
  "profile_id": "01...",
  "env_type": "sandbox",
  "region": "TEST_SG",
  "shop_id": 226349641,
  "access_token_expires_at": "2026-05-16T15:00:00Z",
  "access_token_last_refreshed_at": "2026-05-16T11:00:00Z",
  "refresh_token_last_used_at": "2026-05-16T11:00:00Z",
  "scopes": ["product.basic", "order.read", "logistics.get_channel_list"],
  "last_refresh_attempt_at": "2026-05-16T11:00:00Z",
  "last_refresh_status": "success",
  "last_refresh_error": null
}

Token bytes are NEVER in this response. The operator's "debug my token" need is satisfied by expires_at + last_refresh_status + last_refresh_error.

PR breakdown¶

Risks ratified at design lock¶

Out of scope (deferred to Phase 8+)¶

• Rate-limit middleware (current Shopee limit is comfortable for our load)
• Multi-shop-per-partner (one profile = one shop, mirroring Phase 6's assumption)
• Webhook delivery retry / dead-letter queue (Phase 3 logs all events; replay is operator-driven)
• Shop-info auto-refresh (manual via OAuth re-trigger today)

Links¶

• Phase 3 Findings — Shopee cascade context (escrow / lost-push / reconcile)
• Phase 6 Findings — predecessor: multi-environment toggle that this phase consolidates
• Phase 4 PO Flow Findings — the Medusa workflow + DML gotchas surfaced during the recent staging push
• Development Workflow — design-system + audit loop (drives PR-5's UI work)