Skip to content

URL: https://mkdocs.justinsforge.com/memory/handoffs/links-commissions-status-2026-07-11/

Links commissions worker: status, 2026-07-11

Built Phase 4 (commission reporting) of the Link In Bio module per affiliate-click-foundation. Additive-only: did not touch render.ts, cf.ts, the edge Worker, or the sync script (kept clear of the parallel worker on the Links core lane).

Branches (NOT merged, NOT deployed, NOT prod-migrated)

Two separate repos, same branch name:

  • forge repo, branch links-commissions (commit 495a9bc): new file scripts/forge_links_commissions_import.py only. Pushed to https://github.com/JustinWieb/forge/pull/new/links-commissions.
  • forge-suite (CreatorTrack) repo, worktree /home/justinwieb/forge-suite-wt/links-commissions, branch links-commissions (commit 51a7898): schema + migration + stats page. Pushed to https://github.com/JustinWieb/creatortrack/pull/new/links-commissions.

I did not merge either branch to main and did not touch the shared prod DB. forge's main branch had pre-existing unrelated local modifications (LESSONS.md, brands/, etc, not mine); I branched off main, committed only my file, and returned main to its original dirty state.

Files changed

forge repo (branch links-commissions) - scripts/forge_links_commissions_import.py (new) — CSV importer + API-pull scaffold.

CreatorTrack worktree (branch links-commissions) - db/migrations/20260711T214555_links_commissions_links_commissions_table.sql (new) — links.commissions table. - db/schema.ts (edit, additive) — linkCommissions drizzle table + LinkCommission type. - lib/links-store.ts (edit, additive) — getCommissionsSummary(id, workspaceId). - app/(suite)/link-in-bio/stats/page.tsx (edit, additive block only) — "Commissions" section (confirmed/pending totals + per-brand table), inserted above the existing clicks/captures section, does not restructure existing code.

Schema: links.commissions

Workspace-scoped + RLS, matching the existing links.* pattern (workspace_isolation policy on workspace_id IN (SELECT m.workspace_id FROM core.memberships ...)).

Column Type Notes
id text PK ULID, minted by the importer (own generator, no external dep)
workspace_id bigint FK core.workspaces(id)
brand text free-form, matches links.clicks/captures.brand convention
network text CHECK IN ('amazon','cj','shopmy','impact','manual')
external_id text the network's own order/conversion id
link_id text, nullable our /r/<linkId> tracking id, when the network's export carries it
product text
amount_cents bigint signed (negative = reversal amount)
currency text default 'USD'
status text CHECK IN ('pending','confirmed','reversed')
occurred_at timestamptz the network's transaction date
imported_at timestamptz stamped by the importer's injected clock
source_batch text free-text label for the import run

Idempotency: UNIQUE (network, external_id). The importer does INSERT ... ON CONFLICT (network, external_id) DO UPDATE, so re-importing the same report (or a later pull where a row flips pending -> confirmed) updates in place. Verified: re-running the same CSV produced +0 inserted, 3 updated, row count unchanged.

forge_links_commissions_import.py --csv report.csv --format amazon \
    --workspace-id 85 --brand gus [--source-batch amazon-2026-07] [--dry-run]

forge_links_commissions_import.py --csv report.csv --format generic \
    --workspace-id 85 --brand gus --network cj \
    --mapping '{"external_id":"Conversion ID","product":"Advertiser",
                "amount_cents":"Commission Amount","occurred_at":"Conversion Date"}'
  • --format amazon: matches Amazon Associates earnings-export headers by alias (tolerant of the several header variants Amazon uses across report types/locales), maps status text (Shipped/Completed -> confirmed, Returned/Cancelled -> reversed, else pending).
  • --format generic: JSON column-mapper for CJ / ShopMy / Impact / anything CSV-shaped.
  • Fail loud: a data row (non-blank external id) with an unparsable amount or date raises ImportError_ and aborts the whole run with no partial commit (verified: bad-date CSV exited 1, row count in DB unchanged). Blank/summary rows (blank external id) are skipped with a printed notice, not an error.
  • Injected clock: main() computes now = datetime.now(timezone.utc) once and threads it through import_csv() / make_ulid(); no date.today() anywhere (doctrine feedback_date_today_banned_in_forge_scripts).
  • API-pull scaffold: fetch_amazon_paapi() / fetch_cj_conversions() are typed stubs. Each raises a clear RuntimeError naming the missing ~/.forge-secrets/{amazon-paapi,cj-affiliate}.env file and points back at the CSV path as the working alternative — never a silent no-op.

Verification performed (real, against the dev clone)

  1. PGDATABASE=ct_links_commissions WORKSPACE_MIGRATIONS_DIR=db/migrations python3 ~/forge/scripts/forge_workspace_migrate.py — applied cleanly (40 prior migrations + this one, all applied).
  2. \d links.commissions on the dev clone — confirmed columns, both CHECK constraints, the unique index, the FK, and the workspace_isolation RLS policy all present as designed.
  3. Seeded a 4-row Amazon CSV (3 data rows + 1 trailing blank-id "Totals" row) and a 2-row CJ generic-mapper CSV, workspace 85 (Gus The Bass). Import: +3 inserted / +2 inserted.
  4. Re-ran the Amazon import unchanged: +0 inserted, 3 updated — idempotency confirmed, row count stayed at 5.
  5. Malformed-row test: a CSV with an unparsable date raised ImportError_, process exited 1, DB row count unchanged (5) — confirmed the failure rolled back, no partial write.
  6. API-pull stub test: fetch_amazon_paapi() (no secrets file present) raised the expected RuntimeError with the Blocked-on-Justin message.
  7. Started next dev --webpack -p 3068 against the worktree, curl'd /link-in-bio/stats with ct_active_ws=85: rendered $15.70 confirmed / $13.75 pending totals and the correct per-brand split (Gus The Bass $15.70/$0.00, Gus Outdoor Co. $0.00/$13.75), matching the seeded data exactly. Dev server stopped after.
  8. Deleted the test rows (source_batch like 'test-batch%' or 'cj-test%') from the dev clone afterward so the clone is clean for the next apply/seed cycle.
  9. npx tsc --noEmit: only 6 pre-existing, unrelated errors (missing public/brand/*.png assets, a global LayoutProps type not generated because the dev server never fully built .next/types before this run) — none in any file I touched (links-store.ts, schema.ts, stats/page.tsx are clean).

Blocked-on-Justin (does not block anything shipped here)

Real Amazon Associates report API access (or CJ/ShopMy/Impact API creds) to wire up fetch_amazon_paapi() / fetch_cj_conversions(). Amazon does not expose commission totals via PA-API (product catalog only) — real automated ingestion needs either the Associates "Earnings Report" CSV export (works today via --format amazon) or the Amazon Attribution API; confirm which Justin's account has access to before building the live pull. Until then, commissions land via manual CSV export + forge_links_commissions_import.py, which is fully working now.

Go-live steps (Justin, when ready)

  1. Review + merge the forge repo PR (scripts/forge_links_commissions_import.py) to main.
  2. Review + merge the creatortrack repo PR (schema/store/stats-page changes) to main.
  3. Apply the migration to prod by hand (per doctrine, this worker never runs prod migrations): 
    cd ~/forge-suite && PGDATABASE=<prod lifeos db> WORKSPACE_MIGRATIONS_DIR=db/migrations \
  python3 ~/forge/scripts/forge_workspace_migrate.py
  4. Deploy CreatorTrack per the normal path (scripts/forge_creatortrack_deploy.sh).
  5. Export an Amazon Associates earnings CSV and run the importer against prod (swap PGDATABASE/PG* to the prod values, or rely on ~/.forge-secrets/forge-data.env defaults) to seed real commissions; the stats page section appears automatically once links.commissions has rows for the active workspace (hidden when empty, so nothing changes for Michael until real data lands).
  6. When Amazon PA-API/Attribution or CJ API creds are available, drop them into ~/.forge-secrets/amazon-paapi.env / ~/.forge-secrets/cj-affiliate.env and implement the bodies of fetch_amazon_paapi() / fetch_cj_conversions() (currently typed stubs that fail loud).