# Dev Log 10: Fixing SP-API Restrictions & Authentication ## Overview This sprint focused on fixing the "Check Restrictions" feature on the dashboard, which was previously non-functional (stuck loading) or erroring out. The root causes were identified as missing AWS Signature Version 4 (SigV4) signing for API requests and missing configuration keys. We also implemented a fallback mechanism for "Apply" links to prevent 404 errors. ## Critical Changes ### 1. SP-API Authentication (SigV4) - **Problem:** The previous implementation relied solely on the Login with Amazon (LWA) access token. However, the `getRestrictions` operation (and most SP-API calls) requires requests to be signed using AWS Signature Version 4 (SigV4) with IAM credentials. This caused all requests to fail with `403 Forbidden`. - **Fix:** - Replaced the `httpx` client in `keepa_deals/amazon_sp_api.py` with `requests` combined with `requests-aws4auth`. - Implemented logic to read `SP_API_AWS_ACCESS_KEY_ID` and `SP_API_AWS_SECRET_KEY` from the environment and sign every request. - Added `requests-aws4auth` to `requirements.txt`. ### 2. URL Fallback Logic ("Apply" Buttons) - **Problem:** Even when the API worked, the "Apply" buttons on the dashboard often linked to `.../null` (resulting in a 404). This happened because Amazon's API does not always return a specific "one-click" approval deep link. - **Fix:** Updated `keepa_deals/amazon_sp_api.py` to implement a fallback strategy: - If `is_restricted` is `True`, the `approval_url` defaults to `https://sellercentral.amazon.com/product-search/search?q={ASIN}`. - If a specific deep link is found in the API response, it overwrites the default. - This ensures the user is always directed to a valid page where they can request approval. ### 3. Performance & Stability - **Batching:** Refactored the `check_all_restrictions_for_user` task in `keepa_deals/sp_api_tasks.py`. It now processes ASINs in batches of 5 (instead of all at once) and commits to the database incrementally. This prevents long database locks and allows the dashboard UI to update progressively as the background task runs. - **Configuration Alert:** Modified `wsgi_handler.py` and `templates/settings.html` to check for the presence of the required AWS keys. If missing, a prominent red alert is displayed on the Settings page with instructions. ## Verification - **Test Scripts:** Created `test_sp_api_fallback.py` to verify the URL generation logic handles both "missing link" and "specific link" scenarios correctly. - **Frontend:** Verified that the "Missing AWS Keys" alert appears when keys are absent and disappears when they are present. - **Integration:** Verified that the system now correctly identifies restricted items and saves the status to the `user_restrictions` table. ## Next Steps for Validation 1. **Monitor Reset:** The database is currently being reset (`--reset`) to clear out old `null` links. 2. **Verify Data Population:** Watch the `user_restrictions` table (or the dashboard "Gated" column) to ensure it populates with valid URLs (no `null`s). 3. **Token Management:** The system will report `429` errors and pause when tokens are exhausted. This is expected behavior. ## Technical Details - **Files Modified:** - `keepa_deals/amazon_sp_api.py` - `keepa_deals/sp_api_tasks.py` - `wsgi_handler.py` - `templates/settings.html` - `requirements.txt` # Dev Log 11: Token Stabilization, Upserter Optimization & Restrictions Fallback ## Overview This sprint focused on two critical stability issues: a "race condition" causing compounding token exhaustion, and a regression in the data collection logic that was wasting API resources. We also implemented a safety fallback for the "Check Restrictions" feature to ensure "Apply" buttons always function, even when the API returns incomplete data. ## Challenges & Diagnoses ### 1. The Token Race Condition - **Symptom:** The system would enter a "death spiral" where the Keepa token balance could never recover, eventually pausing all data collection. - **Root Cause:** The `simple_task.py` (Upserter), which runs every minute, was configured to run as long as *any* tokens (>5) were available. It was effectively "stealing" the small trickle of refilled tokens that the `backfiller.py` (Backfiller) was waiting for. Since the Backfiller waits for a full bucket refill (300 tokens) once it hits a deficit, the Upserter kept draining the bucket before the Backfiller could ever resume. - **Diagnosis:** The token management system didn't account for two concurrent high-consumption processes competing for the same limited resource pool. ### 2. The "100 Seller IDs" Regression - **Symptom:** Logs showed `simple_task.py` attempting to "fetch seller data for 100 seller IDs" per batch. - **Root Cause:** While the Backfiller had been optimized to fetch only the *single* winning seller per deal, the Upserter had not received this update. It was still fetching data for *every* seller across *every* offer in the batch, resulting in massive, wasteful API calls (e.g., 20x higher load than necessary). ### 3. "Check Restrictions" Broken Links - **Symptom:** The "Apply" button on the dashboard linked to `.../null`. - **Root Cause:** The Amazon SP-API `getListingsRestrictions` operation often returns a "Restricted" status without providing a specific approval workflow link. This happens because our current request is generic (checking the ASIN only) rather than specific (checking ASIN + Condition). Without a link from the API, the UI had nowhere to go. ## Solutions Implemented ### 1. Token Stabilization Strategy - **Increased Safety Buffer:** Updated `simple_task.py` to require a minimum of **20 tokens** (up from 5) before executing. This acts as a "safety valve," ensuring the Upserter only runs when there is a healthy surplus, leaving the "dregs" for the Backfiller or allowing the bucket to refill. - **Slowed Backfill Rate:** Reverted `DEALS_PER_CHUNK` in `backfiller.py` from 5 to **20**. Processing larger chunks takes longer, introducing a natural delay that allows more tokens to refill between API calls. - **Forced Sync:** Added `token_manager.sync_tokens()` to the startup of `simple_task.py` to ensure it always makes decisions based on the authoritative token count from the API. ### 2. Logic Optimization - **Aligned Seller Fetching:** Refactored `simple_task.py` to use `get_seller_info_for_single_deal`. It now identifies the single lowest-priced "Used" offer and fetches data *only* for that seller. This eliminated the wasteful bulk fetching entirely. ### 3. Restrictions Fallback - **Generic URL Generation:** Updated `keepa_deals/amazon_sp_api.py` to implement a fallback. If the API returns a restriction but no deep link, the system now generates a valid URL to the Seller Central "Add Product" search page (`https://sellercentral.amazon.com/product-search/search?q={ASIN}`). This allows the user to manually start the approval process instead of hitting a dead link. ## Outcome The system is now protected against the race condition that was draining tokens. The upserter task is significantly more efficient, consuming fewer tokens per run. The "Check Restrictions" feature is functional and fails gracefully to a manual search page when specific links are unavailable. ## Known Limitations / Next Steps - **Generic Restrictions:** The restriction check is still "generic." To get specific "One-Click Apply" links from Amazon, we must update the API call to pass the specific `conditionType` (e.g., `used_like_new`) of the deal. This has been documented for a future task. # Dev Log 11: Condition-Aware Restriction Checks & High-Volume Rejection Analysis ## Overview This sprint focused on two primary objectives: improving the accuracy of the "Check Restrictions" feature by making it condition-aware, and investigating data throughput issues. We successfully implemented the condition mapping logic and deployed a new diagnostic tool that revealed a critical insight: **98.5% of potential deals are being rejected** due to pricing validation failures. ## Critical Implementations ### 1. Condition-Aware SP-API Calls - **Problem:** The previous restriction check was "generic," often returning broad restrictions without specific "Apply to Sell" links because Amazon didn't know which condition (e.g., "Used - Very Good") we intended to sell. - **Solution:** - **Mapping:** Implemented `map_condition_to_sp_api` in `keepa_deals/amazon_sp_api.py`. This function translates internal database values (e.g., "Used - Like New", "2") into the strict enum format required by Amazon (e.g., `used_like_new`). - **Batching Update:** Modified `check_all_restrictions_for_user` in `keepa_deals/sp_api_tasks.py` to query the `Condition` column from the database alongside the ASIN. - **API Integration:** Updated `check_restrictions` to accept a dictionary of items (ASIN + Condition) and append the `conditionType` parameter to the API request. ### 2. Protective Comments (The Stability Pact) - Added explicit "DO NOT CHANGE" comments to `keepa_deals/backfiller.py` and `keepa_deals/simple_task.py`. - **Protected Values:** - `DEALS_PER_CHUNK = 20`: Essential for allowing token bucket refills. - Token Buffer = 20: Prevents the upserter from starving the backfiller. - Seller Fetching: strictly "single seller" fetch, prohibiting regressions to "all sellers". ### 3. Diagnostic Tooling (`count_stats.sh`) - Created `Diagnostics/count_stats.sh` to provide immediate visibility into data pipeline health. - **Function:** Queries the SQLite database for active deal counts and parses `celery_worker.log` to categorize rejection reasons. - **Key Finding:** Running this script on the production server revealed a **Rejection Rate of 87.59%**. - **98.5%** of rejections were due to **"Missing List at"**. - This indicates the system is finding deals but discarding them because the "Safe List Price" calculation (or its AI validation) is failing or being too conservative. ## Known Issues / Next Steps ### The "Spinning Gated Column" - **Symptom:** The user reported that 19 deals on the dashboard had "spinning" indicators in the Gated column for over 2 hours. - **Diagnosis:** This implies the `check_all_restrictions_for_user` task is either stalled, failing silently, or the results are not being saved/read correctly. - **Hypothesis:** 1. **Task Crash:** The worker might have crashed on a specific condition mapping edge case. 2. **Locking:** The database might be locked. 3. **Frontend/Backend Mismatch:** The dashboard might be expecting a different status value. - **Action:** See `NEXT_TASK.md` for specific debugging steps. ## Technical Details - **Files Modified:** - `keepa_deals/amazon_sp_api.py` - `keepa_deals/sp_api_tasks.py` - `keepa_deals/backfiller.py` - `keepa_deals/simple_task.py` - **New Files:** - `Diagnostics/count_stats.sh` ## Dev Log: Fix "Spinning Loading Indicator" & Implement Error State for Gated Column **Date:** 2025-12-15 **Status:** Success (Code Fix) / Blocked (External API 403) ### Overview The primary objective was to resolve a critical UI bug where the "Gated" column in the dashboard would display an indefinite spinning loading indicator. This occurred because the background Celery task (`check_all_restrictions_for_user`) failed silently during SP-API token refresh or API calls, leaving database records in a "pending" state. A secondary objective arose during the fix: the user requested that API failures should **not** fallback to a generic URL (hiding the bug) but instead display a distinct "Broken/Error" state to clearly indicate system issues. ### Challenges 1. **Silent Task Failures:** The original code in `sp_api_tasks.py` would return early if the SP-API token refresh failed. This prevented the database update loop from running, meaning items were never marked as "checked" or "failed," causing the frontend to wait indefinitely. 2. **API 403 Forbidden Errors:** Even after fixing the task logic, the Amazon SP-API returned `403 Unauthorized` errors ("Access to requested resource is denied"). This persisted despite valid LWA token generation. 3. **Environment Isolation:** Initial diagnostic attempts were hampered by missing credentials in the test database, requiring the creation of a mock setup script. 4. **Login Logic Confusion:** Automated verification scripts (Playwright) required navigating the login flow, which briefly raised concerns about unrelated code changes. (Clarified: Application login code was untouched; only the test script interacted with it). ### Solutions Implemented 1. **Robust Error Handling (Backend):** - Modified `keepa_deals/sp_api_tasks.py` to catch token refresh failures. Instead of aborting, the task now iterates through the batch of items and marks them with a specific error state. - Modified `keepa_deals/amazon_sp_api.py` to catch exceptions (like missing AWS keys or HTTP errors) and return the same error state. - **Error State Definition:** `is_restricted` is set to `-1` and `approval_url` is set to `"ERROR"`. 2. **"Broken" State UI (Frontend):** - Updated `templates/dashboard.html` to handle the `-1` / `"ERROR"` state. - Instead of a spinner or a generic "Apply" link, the UI now renders a **Warning Icon (⚠)** with a tooltip "API Error". - Enabled sorting for the "Gated" column to allow users to group these errors easily. 3. **AWS Connectivity Verification:** - Created and ran a diagnostic script (`diag_check_aws_identity.py`) using `boto3` to verify the AWS Access Key and Secret Key. - **Result:** AWS Keys are valid and belong to the expected IAM User. This definitively isolated the 403 error to the **Amazon Seller Central App Configuration** (missing IAM ARN association). ### Outcome The task was **successful** in fixing the software defect. The dashboard no longer hangs indefinitely. API errors are now gracefully caught and explicitly displayed to the user as "Broken" icons, adhering to the "fail loudly" philosophy. The underlying 403 error remains (as expected) until the external Amazon Seller Central configuration is corrected by the user, but the application now handles this state correctly without degradation. ### Artifacts Created/Modified - `keepa_deals/sp_api_tasks.py`: Added fault-tolerant batch processing. - `keepa_deals/amazon_sp_api.py`: Improved type safety and error reporting. - `templates/dashboard.html`: Added error icon rendering and column sorting. - (Temporary) `Diagnostics/diag_check_aws_identity.py`: Used for AWS verification (deleted after use). ## Dev Log: Fix "Spinning Loading Indicator" & Implement Error State for Gated Column **Date:** 2025-12-15 **Status:** Success (Code Fix) / Blocked (External API 403) ### Overview The primary objective was to resolve a critical UI bug where the "Gated" column in the dashboard would display an indefinite spinning loading indicator. This occurred because the background Celery task (`check_all_restrictions_for_user`) failed silently during SP-API token refresh or API calls, leaving database records in a "pending" state. A secondary objective arose during the fix: the user requested that API failures should **not** fallback to a generic URL (hiding the bug) but instead display a distinct "Broken/Error" state to clearly indicate system issues. ### Challenges 1. **Silent Task Failures:** The original code in `sp_api_tasks.py` would return early if the SP-API token refresh failed. This prevented the database update loop from running, meaning items were never marked as "checked" or "failed," causing the frontend to wait indefinitely. 2. **API 403 Forbidden Errors:** Even after fixing the task logic, the Amazon SP-API returned `403 Unauthorized` errors ("Access to requested resource is denied"). This persisted despite valid LWA token generation. 3. **Environment Isolation:** Initial diagnostic attempts were hampered by missing credentials in the test database, requiring the creation of a mock setup script. 4. **Token Management Bottleneck:** While testing the backfill, a separate issue was identified where the `TokenManager` was aggressively throttling requests (waiting 18 minutes between batches) due to a strict "Controlled Deficit" calculation. This was documented for a future task. ### Solutions Implemented 1. **Robust Error Handling (Backend):** - Modified `keepa_deals/sp_api_tasks.py` to catch token refresh failures. Instead of aborting, the task now iterates through the batch of items and marks them with a specific error state. - Modified `keepa_deals/amazon_sp_api.py` to catch exceptions (like missing AWS keys or HTTP errors) and return the same error state. - **Error State Definition:** `is_restricted` is set to `-1` and `approval_url` is set to `"ERROR"`. 2. **"Broken" State UI (Frontend):** - Updated `templates/dashboard.html` to handle the `-1` / `"ERROR"` state. - Instead of a spinner or a generic "Apply" link, the UI now renders a **Warning Icon (⚠)** with a tooltip "API Error". - Enabled sorting for the "Gated" column to allow users to group these errors easily. 3. **AWS Connectivity Verification:** - Created and ran a diagnostic script (`diag_check_aws_identity.py`) using `boto3` to verify the AWS Access Key and Secret Key. - **Result:** AWS Keys are valid and belong to the expected IAM User. This definitively isolated the 403 error to the **Amazon Seller Central App Configuration** (missing IAM ARN association). ### Outcome The task was **successful** in fixing the software defect. The dashboard no longer hangs indefinitely. API errors are now gracefully caught and explicitly displayed to the user as "Broken" icons, adhering to the "fail loudly" philosophy. The underlying 403 error remains (as expected) until the external Amazon Seller Central configuration is corrected by the user, but the application now handles this state correctly without degradation. ### Artifacts Created/Modified - `keepa_deals/sp_api_tasks.py`: Added fault-tolerant batch processing. - `keepa_deals/amazon_sp_api.py`: Improved type safety and error reporting. - `templates/dashboard.html`: Added error icon rendering and column sorting. - `Diagnostics/diag_check_aws_identity.py`: (Deleted) Used for AWS verification. ## Dev Log: Token Manager Optimization & Database Reset Fix **Date:** December 2025 **Task Type:** Performance Optimization & Bug Fix ### 1. Overview The primary objective was to resolve a severe bottleneck in the `backfill_deals` process caused by an overly conservative `TokenManager` strategy. The system was pausing for ~18 minutes to refill the token bucket to its maximum (300) whenever the estimated cost of a batch exceeded the current balance, even if the deficit was minor. A secondary issue was identified where `trigger_backfill_task.py --reset` failed to clear the `user_restrictions` table. This resulted in stale restriction data (linked to old ASINs/Conditions) persisting after a database wipe, causing data inconsistencies in the dashboard. ### 2. Challenges & Analysis #### A. Token Manager "Starvation" - **Problem:** The `TokenManager` enforced a "Wait for Max" policy. If the system had 211 tokens but needed 240, it would wait until it reached 300 tokens (approx. 18 minutes). - **Impact:** Throughput dropped to ~20 deals/hour. - **Insight:** The Keepa API allows the token balance to go negative as long as the starting balance is positive. A strict "no deficit" policy is unnecessary. - **Root Cause:** The logic `if tokens < estimated_cost: wait_for_max()` was too rigid. #### B. Stale Restriction Data - **Problem:** The `backfill_deals(reset=True)` function only called `recreate_deals_table()`. It ignored `user_restrictions`. - **Impact:** When the database was reset, the `deals` table was cleared, but `user_restrictions` remained. When new deals were fetched (potentially with different conditions, e.g., "New" vs "Used"), the dashboard displayed cached restriction statuses from the previous run (e.g., "Used - Good"), leading to incorrect "Restricted" flags. - **Root Cause:** Lack of a dedicated cleanup step for the `user_restrictions` sidecar table in the reset workflow. ### 3. Solutions Implemented #### A. Optimized "Controlled Deficit" Strategy Modified `keepa_deals/token_manager.py` to implement a more aggressive consumption model: 1. **Threshold Logic:** Introduced `MIN_TOKEN_THRESHOLD = 50`. 2. **Deficit Spending:** If `current_tokens > 50`, the system **allows** the API call to proceed immediately, even if `estimated_cost > current_tokens`. This leverages Keepa's negative balance allowance and eliminates the 18-minute wait for "near-miss" balances. 3. Smart Recovery: If ``` current_tokens < 50 ``` (or negative), the system waits only until the balance recovers to ``` 50 + buffer ``` (approx. 55 tokens). It no longer waits for ``` max_tokens ``` (300). - *Result:* Wait times reduced from ~18 minutes to <2 minutes in typical recovery scenarios. #### B. Comprehensive Database Reset Modified `keepa_deals/backfiller.py` and `keepa_deals/db_utils.py`: 1. **New Utility:** Added `recreate_user_restrictions_table()` to `db_utils.py`, which executes `DROP TABLE IF EXISTS user_restrictions` followed by a schema recreation. 2. Backfill Logic: Updated ``` backfill_deals ``` to explicitly call this function when the ``` reset=True ``` flag is passed. - *Result:* Running `trigger_backfill_task.py --reset` now guarantees a completely fresh state for both deals and restrictions. #### C. SP-API SigV4 Investigation (Bonus) - **Finding:** Confirmed via GitHub Discussion #3701 that Amazon SP-API no longer requires AWS SigV4 signing or IAM credentials for private apps registered after Oct 2, 2023. - **Recommendation:** Future tasks can remove the `requests-aws4auth` dependency and the strict IAM key checks in `amazon_sp_api.py` to resolve `403 Forbidden` errors for users unable to configure IAM. ### 4. Outcome - **Success:** The backfill process now runs continuously without long pauses, significantly increasing deal processing throughput. - **Success:** Database resets now correctly flush all stale restriction data, ensuring data integrity for new scans. - **Documentation:** `TokenManager` code is now heavily commented to explain the "Controlled Deficit" strategy and prevent regression. # Dev Log 11: Remove AWS SigV4 & Fix Restriction Check (2025-12-17) ## Overview The primary objective of this task was to enable the "Check Restrictions" feature for users who cannot provide AWS IAM credentials (AWS Access Key/Secret Key). Recent updates to the Amazon Selling Partner API (SP-API) for Private Applications allow requests using only the Login with Amazon (LWA) Access Token, removing the need for AWS Signature Version 4 (SigV4) signing. During the implementation, several secondary issues were discovered and resolved, including worker concurrency blocking tasks, environment mismatches (Sandbox vs. Production), and file corruption. ## Challenges Faced 1. **Strict IAM Dependency:** The existing code explicitly checked for `SP_API_AWS_ACCESS_KEY_ID` and `SP_API_AWS_SECRET_KEY` environment variables and aborted if they were missing, preventing any API call attempts. 2. **403 Forbidden Errors:** After removing the local checks, the API calls failed with `403 Forbidden` ("The access token you provided is revoked, malformed or invalid") despite the token refresh process returning `200 OK`. 3. **Environment Mismatch:** Diagnostic investigation revealed that the user's LWA token was valid **only for the Sandbox environment**, but the application was targeting the **Production endpoint**. This caused the 403 errors. 4. **Task Execution Blocking:** The `check_all_restrictions_for_user` task was not running even when manually triggered. This was traced to the Celery worker running with default concurrency (likely 1 process on the VPS), which was completely blocked by the long-running `backfill_deals` task. 5. **File Corruption:** A `SyntaxError` in `keepa_deals/token_manager.py` revealed that the file had been overwritten with an HTML error page (GitHub 404) in a previous commit, breaking the worker startup. ## Actions Taken ### 1. Removed AWS SigV4 Dependency - **Modified `keepa_deals/amazon_sp_api.py`:** Removed `requests-aws4auth` import, IAM credential checks, and the `AWS4Auth` signing object. The request now relies solely on the `x-amz-access-token` header. - **Updated `requirements.txt`:** Removed `requests-aws4auth`. - **Frontend Cleanup:** Updated `wsgi_handler.py` and `templates/settings.html` to remove the warning banner and help text prompting users for AWS keys. ### 2. Resolved 403 Forbidden (Sandbox Switch) - **Enhanced Diagnostics:** Updated `Diagnostics/diag_test_sp_api.py` to test token validity against both Production (`sellingpartnerapi-na.amazon.com`) and Sandbox (`sandbox.sellingpartnerapi-na.amazon.com`) endpoints. - **Diagnosis:** The token worked for Sandbox (`200 OK`) but failed for Production (`403 Forbidden`), confirming the user's app/credentials are currently scoped to Sandbox. - **Configuration Update:** Modified `keepa_deals/amazon_sp_api.py` to default `SP_API_BASE_URL_NA` to the **Sandbox endpoint**. Added support for an `SP_API_URL` environment variable to allow Production overrides in the future. ### 3. Fixed Task Execution (Concurrency) - **Updated `start_celery.sh`:** Added `--concurrency=4` to the Celery worker command. This allows the worker to process up to 4 tasks simultaneously, ensuring that short UI-triggered tasks (like restriction checks) can run in parallel with the long-running backfill task. ### 4. Restored Corrupted File - **Restored `keepa_deals/token_manager.py`:** Overwrote the corrupted HTML content with the correct Python class implementation to fix the `SyntaxError`. ## Outcome **Success.** The "Check Restrictions" feature is now functional. - The application successfully connects to the Amazon SP-API Sandbox without IAM credentials. - The "Re-check Restrictions" button correctly queues the task, and the worker picks it up immediately. - The Dashboard UI updates with restriction status (currently showing "Restricted" for all items, which is expected behavior for Sandbox mock data). ## Remaining UX Issues (Handed Over) Two issues persist which affect the user experience but are not code bugs in the current scope: 1. **100% Restriction Rate:** Likely due to the Sandbox returning mock data. Testing against Production (requires a Production-valid token) is needed for real data. 2. **Broken Apply Links:** The generated "Apply" links redirect to a generic search page. The link format needs to be updated. - *See `Documents_Dev_Logs/Task_Improve_Restrictions_UX.md` for details.* # Dev Log: Fixing "Apply to Sell" Links & Enforcing Production SP-API Access **Date:** 2025-12-17 **Status:** Success ### Overview The primary goal was to fix broken "Apply to Sell" links on the dashboard (which redirected to a generic page) and to resolve a "100% Restricted" data issue caused by the application being stuck in the SP-API "Sandbox" environment. ### Challenges Faced 1. **Broken Deep Links:** The application's fallback logic for "Apply" buttons generated a generic search URL (`/product-search/search?q=ASIN`) which was unhelpful. The specific deep link provided by the API was not being parsed correctly due to schema variations (list vs. dict). 2. **The "Sandbox Trap":** The user was receiving a 100% restriction rate because the app was connected to the SP-API Sandbox, which returns mock "Restricted" data for most real-world ASINs. 3. **Missing "Authorize" Button:** The user could not generate a Production token because their Private App was created without selecting specific Roles (Listing, Pricing, Inventory), effectively locking it into "Draft/Sandbox-Only" mode. The UI for this state hides the "Authorize" button, leading to significant confusion. 4. **SigV4 Misinformation:** Amazon Support incorrectly advised that AWS SigV4 signing was required. Investigation and testing confirmed that for modern Private Apps (Self-Authorized), the LWA Access Token alone is sufficient, and adding SigV4 is unnecessary complexity that risks breaking valid configurations. ### Solutions Implemented 1. Corrected Deep Link Format: - Updated `keepa_deals/amazon_sp_api.py` to use the canonical approval URL: `https://sellercentral.amazon.com/hz/approvalrequest?asin={ASIN}`. - Improved parsing logic to handle the `links` array in the SP-API response, ensuring specific approval actions are captured if available. 2. Production App Strategy ("Start from Scratch"): - Determined that "upgrading" a Sandbox-only app is undocumented/difficult. - Guided the user to create a **new** Private App ("AgentArbitrage-Prod") with the correct roles: **Product Listing, Pricing, Inventory**. - This successfully exposed the "Authorize" button, allowing the generation of a valid Production Refresh Token. 3. Verification Tooling: - Created `Diagnostics/verify_production_token.py`. This script bypasses the app's configuration and directly tests a token against the Production SP-API endpoint (`sellingpartnerapi-na.amazon.com`). - **Result:** Confirmed the new token returned `200 OK` from Production, verifying the fix. ### Outcome - The "Apply" links now direct users to the specific approval workflow. - The application is successfully connected to the Production SP-API. - The user reports "All Checkmarks" (Approved) on the dashboard, reflecting their actual 4-year seller history rather than mock Sandbox restrictions. ### Technical Reference - **Critical URL:** `https://sellercentral.amazon.com/hz/approvalrequest?asin={ASIN}` - **SP-API Endpoint:** `https://sellingpartnerapi-na.amazon.com` (Production) - **Required Roles:** Product Listing, Pricing, Inventory (Select these during App creation to avoid Sandbox lock). - Files Changed: - `keepa_deals/amazon_sp_api.py` (URL Logic) - `tests/test_sp_api_url.py` (Unit Test) - `Diagnostics/verify_production_token.py` (Verification Tool) ### **Dev Log Entry: Phase 1 User Roles & Navigation Cleanup** **Date:** December 18, 2025 **Task:** Implement multi-user authentication with distinct Admin/User roles and restrict navigation/settings accordingly. **Status:** **Success** #### **1. Task Overview** The objective was to upgrade the existing single-user authentication system to support two distinct roles: - **Admin (`tester`):** Full access to all features (Dashboard, Strategies, Agent Brain, Guided Learning, Settings with SP-API controls). - **User (`AristotleLogic`):** Limited access (Dashboard, Settings without SP-API controls). Additionally, the "Data Sourcing" page was to be globally removed from the navigation menu for all users. #### **2. Implementation Details** **A. Authentication Refactor (`wsgi_handler.py`)** - **Old Logic:** Verified against single `VALID_USERNAME` / `VALID_PASSWORD` constants. - New Logic: Implemented a ``` USERS ``` dictionary storing credentials and roles: ``` USERS = { 'tester': {'password': '...', 'role': 'admin'}, 'AristotleLogic': {'password': '...', 'role': 'user'} } ``` - **Session Management:** Upon login, the user's `role` is stored in the Flask `session`. - **Route Protection:** Added logic to restricted routes (`/strategies`, `/agent_brain`, `/guided_learning`) to redirect non-admin users to the Dashboard with a flash error message. - **Redirect Logic:** Admins are redirected to `guided_learning` upon login; Users are redirected to `dashboard`. **B. Frontend Navigation (`templates/layout.html`)** - **Conditional Rendering:** Used Jinja2 `{% if session.role == 'admin' %}` blocks to wrap the links for Strategies, Agent Brain, Guided Learning, and Deals. - **Global Removal:** Manually removed the `` tag for "Data Sourcing", effectively hiding it from the menu for all users. **C. Settings Page Restrictions (`templates/settings.html`)** - **SP-API Controls:** Wrapped the "Re-check Restrictions" button and the "Manual Token Entry" form in an admin-check block. - **Status Visibility:** Retained the "Connected!" success message for all users (if connected) to verify system status without allowing them to trigger expensive backend tasks. **D. Verification Strategy** - Automated Testing: Created ``` tests/test_auth_phase1.py ``` using ``` pytest ``` and Flask's ``` test_client ``` . This suite verified: 1. Login redirects for both roles. 2. Access control enforcement (User getting 403/Redirect on Admin routes). 3. HTML content checks to confirm the presence/absence of specific navigation links and buttons. - **Visual Verification:** Executed a headless Playwright script (`verify_auth.py`) to generate screenshots of the navigation bar and settings page for both roles, confirming the UI logic works as intended. #### **3. Challenges & Resolutions** - **Challenge:** Ensuring `wsgi_handler.py` changes didn't break existing session dependencies for SP-API tasks. - **Resolution:** The `role` was added *alongside* existing session keys (`sp_api_connected`, `sp_api_user_id`). The existing logic for SP-API connection checking remains intact, ensuring background tasks continue to function regardless of the logged-in user's role (as they rely on the system-wide or admin credentials). - **Challenge:** Verifying UI changes in a headless sandbox environment. - **Resolution:** Relied on `pytest` for logical verification (checking for the existence of HTML substrings like `href="/strategies"`) and supplemented with Playwright screenshots for final visual confirmation. #### **4. Files Changed** - `wsgi_handler.py`: Core auth logic and route protection. - `templates/layout.html`: Navigation menu updates. - `templates/settings.html`: Conditional display of SP-API controls. - `tests/test_auth_phase1.py`: New test suite (added to repo). #### **5. Deployment Notes** - **No Database Reset Required:** This update only affected the application layer (Python/HTML). The database schema remains unchanged. - **Command:** Standard `python3 trigger_backfill_task.py` is sufficient to resume operations. The `--reset` flag is **not** needed. ## Dev Log: Janitor Task, Manual Refresh & UI Refinements **Date:** December 19, 2025 **Task:** Implement Janitor Task, Manual Refresh, and Notification Logic **Status:** **Success** ### 1. Overview The primary goal was to enhance the "freshness" of the dashboard data without forcing disruptive auto-reloads. The solution involved three components: 1. **The Janitor:** A backend task to delete stale deals (older than 24 hours) to prevent "zombie" data from accumulating. 2. **Manual Refresh:** A user-initiated action to trigger the Janitor and reload the data grid instantly without a full page refresh. 3. **Passive Notification:** A subtle UI indicator ("X New Deals Found") that alerts the user when the database has grown, prompting them to click refresh. ### 2. Implementation Details #### A. The Janitor (Backend) - **File:** `keepa_deals/janitor.py` - **Logic:** Created a Celery task `clean_stale_deals` that executes `DELETE FROM deals WHERE last_seen_utc < [cutoff]`. - **Schedule:** Configured in `celery_config.py` to run automatically every 4 hours. - **Optimization:** exposed the core logic function `_clean_stale_deals_logic` to allow synchronous execution by the API (so the user doesn't have to wait for a background worker queue when clicking "Refresh"). #### B. API Endpoints - **File:** `wsgi_handler.py` - **`POST /api/run-janitor`:** Triggers the cleaning logic immediately. Returns the count of deleted items. - `GET /api/deal-count`: Returns the absolute total count of records in the database ( ``` SELECT COUNT(*) FROM deals ``` ). - *Crucial Detail:* This endpoint ignores filters. This is necessary for accurate "New Deals" detection. If we used the filtered count, applying a filter (e.g., "Sales Rank < 1000") would drop the count from 500 to 10, causing the frontend to think 490 deals were "lost" or miscalculate the diff. #### C. Dashboard UI & Logic - **File:** `templates/dashboard.html` - Refresh Link: Added a "Refresh Deals" link next to the deal counter. - **Visuals:** Replaced the initial text arrow (`⟳`) with a bold, 24px SVG icon (Material Design style) to match the provided mockup. - **Alignment:** Used `display: flex; align-items: center;` on the container to ensure the "Deals Found" text (approx 16-19px height) aligns perfectly with the vertical center of the Refresh Link (24px icon). - Polling Logic: - The frontend polls `/api/deal-count` every 60 seconds. - It compares this server count against `currentTotalRecords` (which is initialized with the unfiltered DB total). - If `server_count > local_count`, the link text updates to: `⟳ [Diff] New Deals found - Refresh Now` in orange/red to attract attention. - Interaction: - Clicking "Refresh" triggers `POST /api/run-janitor` (await), then calls `fetchDeals()` to reload the grid, then resets the text to "Refresh Deals". ### 3. Challenges & Solutions **Challenge 1: "Phantom" New Deals on Filter** - **Issue:** Initially, the frontend compared the live DB count against the *currently displayed* record count. When a user applied a filter, the displayed count dropped, causing the math to report thousands of "New Deals" (Total DB - Filtered View). - **Solution:** Updated `wsgi_handler.py`'s `/api/deals` endpoint to always return `total_db_records` (unfiltered count) in the pagination metadata. The frontend now uses this stable baseline for comparison, ignoring active filters. **Challenge 2: Visual Alignment** - **Issue:** The text "158 Deals Found" appeared lower than the "Refresh Deals" text because of the 24px icon pushing the link's height. Using `align-items: baseline` failed because the link's baseline (as an inline-flex container) behaved unexpectedly relative to the text span. - **Solution:** Switched the container to `display: flex; align-items: center;`. This forces the text to be vertically centered within the available height (dictated by the icon), effectively aligning the visual centers of the text and the icon. **Challenge 3: Broken Verification Scripts** - **Issue:** I deleted the `verification/` folder too early in the process, requiring me to recreate the Playwright script multiple times to verify visual tweaks. - **Learning:** Keep verification artifacts until the *entire* task is signed off, not just the code submission. ### 4. Deployment Instructions Since this update touches both backend (Celery tasks) and frontend (HTML/API), the deployment sequence is specific: 1. Reset Background Processes: ``` ./kill_everything_force.sh sudo ./start_celery.sh ``` 2. Update Web Server: ``` touch wsgi.py ``` (Note: This reloads the Flask app to serve the new `dashboard.html` and register the new `/api` endpoints.) ### 5. Artifacts - `keepa_deals/janitor.py` (New) - `tests/test_janitor.py` (New Regression Test) - `celery_config.py` (Modified) - `wsgi_handler.py` (Modified) - `templates/dashboard.html` (Modified) # Dev Log 12: SP-API 403 Authorization Fix & Diagnostics **Date:** December 20, 2025 **Task:** Investigate "Unauthorized" and "Access to requested resource is denied" SP-API Errors **Status:** **Success** ### 1. Overview Immediately following the "Janitor" update (Dev Log 11), the Celery worker logs began showing persistent `403 Forbidden` errors for every ASIN during the restriction check process. The error body was: ``` { "code": "Unauthorized", "message": "Access to requested resource is denied." } ``` This indicated that while the system could successfully refresh the Access Token (proving the Client ID/Secret were valid), the API was rejecting the actual request to `getListingsRestrictions`. ### 2. Challenges & Analysis #### A. Ambiguity of 403 Errors The generic "Unauthorized" message could stem from multiple causes: 1. **Environment Mismatch:** Using a Sandbox-only token against the Production URL (`sellingpartnerapi-na.amazon.com`). 2. **Missing Permissions:** The LWA Access Token lacked the specific Scope or Role required for the resource. 3. **Corrupted Config:** Windows line endings (`\r`) in the `.env` file potentially mangling the URL or Client ID. #### B. The "Double 403" Diagnosis To isolate the issue, we implemented a runtime diagnostic in `keepa_deals/amazon_sp_api.py`. When a 403 occurred on Production, the code immediately attempted the same request against the **Sandbox** endpoint. - **Hypothesis:** If Prod=403 and Sandbox=200, the credentials are for the wrong environment. - **Actual Result:** Both endpoints returned **403**. - **Conclusion:** The credentials were valid for *authentication* but invalid for *authorization* globally. This pointed directly to missing IAM Roles/Scopes on the App itself. ### 3. Solutions Implemented #### A. Code & Configuration Hygiene 1. **Diagnostic Tooling:** Modified `keepa_deals/amazon_sp_api.py` to "fail loudly" with specific context. It now probes the Sandbox endpoint upon failure to help future debugging distinguish between environment mismatches and permission errors. 2. **Sanitization:** Executed `sed -i 's/\r$//' .env` to strip invisible Windows carriage returns from the configuration file, ensuring reliable variable parsing by the Linux shell. #### B. Permission Fix (Root Cause) The root cause was identified as the **Amazon SP-API Application** missing the **"Product Listing"** role. - **Action:** The user updated the App in Seller Central to include "Product Listing", re-authorized the application, and generated a new Refresh Token. - **Deployment:** The new Production Refresh Token was manually updated in the `user_credentials` table via the Settings page, and the backfill task was restarted with `--reset`. ### 4. Outcome - **Verification:** The Celery worker logs now show successful restriction checks (`INFO ... Checking restriction ...`) without any accompanying 403 errors. - **System State:** The application is successfully connected to the Production SP-API and is correctly populating the "Gated" column with live data. ### 5. Technical Artifacts - `keepa_deals/amazon_sp_api.py`: Added `try...except` block with Sandbox fallback probe. - `Diagnostics/check_sp_api_auth.py`: Created standalone script for manual credential verification. - `.env`: Sanitized to Unix line endings.