Skip to main content

Session Notes: 2025-12-18

Summary​

Tested and fixed the 4 wired Cloudflare Workflows to work with the actual database schema. All workflows are now functional and tested.

Workflows Tested​

WorkflowStatusResult
AssetOnboardWorkflow✅ PassedNike.com asset created, DomainOnboardWorkflow triggered, subscription created
DomainOnboardWorkflow✅ Passedamazon.com onboarded with backlinks and keywords stored correctly
SerpTrackingWorkflow✅ PassedKeywords loaded, location resolved. 401 on DataForSEO is env config, not code
AppDetailsWorkflow✅ PassedNike Training Club fetched from iTunes API and stored

Schema Fixes Applied​

AssetOnboardWorkflow (src/workflows/asset-onboard.ts)​

Problem: INSERT used columns that don't exist in assets table.

Fix:

// Before (wrong)
INSERT INTO assets (id, project_id, type, value, name, platform, icon_url, meta_json,
                   local_entity_id, location_id, created_at, updated_at)

// After (correct)
INSERT INTO assets (id, project_id, type, value, name, platform, role, icon_url, meta_json, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, 'primary', ?, ?, ?, ?)

Also fixed UPDATE statement for local_business assets to use meta_json instead of non-existent columns.

DomainOnboardWorkflow (src/workflows/domain-onboard.ts)​

Problems fixed:

  1. urls table INSERT used wrong columns (source, anchor_text, source_domain, source_rank)
  2. keywords table uses TEXT PRIMARY KEY with kw_xxx format, not auto-increment
  3. domain_keyword_rankings table has different schema than workflow expected
  4. fanOutToQueue was waiting indefinitely for queue results

Fixes:

  • URLs INSERT now uses correct schema: url, url_hash, domain, first_seen, last_seen, data_tier, source_endpoint
  • Keywords now generated with kw_ prefix + SHA-256 hash
  • Rankings use year_week, check_ts, rank_absolute, ranking_url, is_featured_snippet
  • Changed to fire-and-forget pattern for queue messages

SerpTrackingWorkflow (src/workflows/serp-tracking.ts)​

Problems fixed:

  1. google_locations query used canonical_name and geo_json (don't exist)
  2. domain_keyword_rankings INSERT used wrong columns

Fixes:

  • Location query now uses name and raw_json
  • Rankings INSERT matches actual schema with year_week format
  • Added getYearWeek() helper method

AppDetailsWorkflow (src/workflows/app-details.ts)​

Problems fixed:

  1. apps table uses id not app_id
  2. Column names: categories_json → categories, screenshot_urls_json → screenshot_urls, updated_date → current_version_release_date

Fixes:

// Fixed column names
sed -i '' 's/FROM apps WHERE app_id = /FROM apps WHERE id = /g'
sed -i '' 's/app_id, platform, title/id, platform, title/g'
sed -i '' 's/categories_json/categories/g'
sed -i '' 's/screenshot_urls_json/screenshot_urls/g'
sed -i '' 's/updated_date/current_version_release_date/g'

Console CORS Fix​

Fixed src/endpoints/workflow-status.js to include CORS headers so console.rankdisco.com can fetch workflow data:

const CORS_HEADERS = {
  'Access-Control-Allow-Origin': '*',
  'Access-Control-Allow-Methods': 'GET, POST, DELETE, OPTIONS',
  'Access-Control-Allow-Headers': 'Content-Type, Authorization'
};

Test Commands Used​

# AssetOnboardWorkflow
curl -X POST "https://api.rankdisco.com/api/admin/workflow/asset-onboard" \
  -H "Content-Type: application/json" \
  -d '{"project_id": "proj_2fae47dd1fabc10e", "asset_type": "website", "value": "https://nike.com", "name": "Nike Website Test"}'

# SerpTrackingWorkflow
curl -X POST "https://api.rankdisco.com/api/admin/workflow/serp-tracking" \
  -H "Content-Type: application/json" \
  -d '{"keyword_ids": ["kw_2215532d218bfc75", "kw_d70df8a191c98cde"], "location_code": 2840}'

# AppDetailsWorkflow
curl -X POST "https://api.rankdisco.com/api/admin/workflow/app-details" \
  -H "Content-Type: application/json" \
  -d '{"app_id": "301521403", "platform": "apple", "country": "US"}'

# Check status
npx wrangler workflows instances describe <workflow-name> <instance-id>

Known Issues​

SerpTrackingWorkflow - DataForSEO 401​

The workflow gets a 401 on the DataForSEO API call because credentials are accessed via globalThis.DATAFORSEO_LOGIN which doesn't work in Cloudflare Workers. The workflow itself is correct - this is an environment configuration issue.

Fix needed: Pass credentials via this.env.DATAFORSEO_LOGIN instead of globalThis.

Documentation Status​

Reviewed existing documentation - already up to date:

  • docs/WORKFLOW_ARCHITECTURE.md - Complete
  • docs/WORKFLOW_IMPLEMENTATION_PLAN.md - Complete
  • docs/domain-onboarding-flow.md - Complete
  • docs/data-architecture.md - Complete with schema details

Files Modified This Session​

FileChanges
src/endpoints/workflow-status.jsAdded CORS headers
src/workflows/asset-onboard.tsFixed assets table INSERT, UPDATE
src/workflows/domain-onboard.tsFixed urls, keywords, rankings storage
src/workflows/serp-tracking.tsFixed google_locations query, rankings INSERT
src/workflows/app-details.tsFixed apps table column names

Next Steps​

  1. Fix DataForSEO credential access in SerpTrackingWorkflow (use this.env instead of globalThis)
  2. Wire remaining workflows to entry points (optional - they work via queues)
  3. Build workflow dashboard UI in console