BUG_REPORT_AND_PROMPTS.md

LandAgro - Comprehensive Bug Report & Claude Opus 4.6 Fix Prompts

Generated: 2026-02-12 | Codebase: LandAgro (Lithuanian Farm Management System) Tech Stack: FastAPI (Python 3.11) + Next.js 14 (TypeScript) + PostgreSQL/SQLite

---

Table of Contents

• Part 1: Comprehensive Bug Report
  • CRITICAL Severity
  • HIGH Severity
  • MEDIUM Severity
  • LOW Severity
• Part 2: Claude Opus 4.6 Fix Prompts

---

Part 1: Comprehensive Bug Report

CRITICAL Severity (11 Issues)

C1. Race Condition in Gemini AI Tool Functions (Cross-Tenant Data Leak)

• Files: backend/src/integrations/gemini_chat.py:52-54, backend/src/integrations/gemini_tools.py:92-93
• Bug: Module-level context variables _current_db_var and _current_user_id_var are shared across concurrent async requests. When User A and User B start chat sessions simultaneously, the context variables can be overwritten, causing User A's tool functions to execute with User B's database session and user ID.
• Impact: Cross-tenant data leakage — work logs, farm data, and financial records could be created under the wrong user. This is the most severe bug in the codebase.

C2. CSRF Protection Broken for Cookie-Based Auth

• Files: backend/src/main.py:234-238, backend/src/auth/service.py:95
• Bug: CSRF middleware only validates CSRF tokens from Bearer header. Cookie-authenticated users (the primary auth method) bypass CSRF protection entirely.
• Impact: Any external website can make state-changing requests (create fields, delete equipment, transfer payments) on behalf of a logged-in user.

C3. Unauthenticated File Access on Multiple Endpoints

• Files: backend/src/equipment/router.py:289, backend/src/livestock/router.py:181, backend/src/ai_scanner/router.py:197, backend/src/warehouses/router.py:185
• Bug: Equipment photos, livestock images, warehouse photos, and AI scan images are served via token-based URLs but without verifying the requesting user owns the resource. Token verification exists but no ownership check.
• Impact: Any user with a valid token can access any other user's farm equipment details, livestock photos, and AI disease scan results.

C4. IDOR on Employee GPS Location History

• File: backend/src/employees/router.py:269-284
• Bug: GET /{employee_id}/lokacijos has no ownership verification. Any authenticated user can request any employee's location history by guessing employee IDs.
• Impact: Complete worker surveillance data exposed — any authenticated user can track any worker's real-time movements and location history.

C5. Operator Precedence Bug in Offline Sync Retry Counter

• File: frontend/src/lib/offlineDb.ts:160
• Bug: retries: (await this.pendingSync.get(id))?.retries ?? 0 + 1 — due to JS operator precedence, 0 + 1 evaluates to 1 before the nullish coalescing, so retries always reset to 1 instead of incrementing.
• Impact: Offline sync retry tracking is completely broken. Failed operations never track their retry count correctly, potentially causing infinite retries or premature abandonment of sync operations.

C6. Alembic Migration Revision Conflict

• Files: backend/alembic/versions/001_initial_schema.py, backend/alembic/versions/001_add_missing_columns.py
• Bug: Both migration files have down_revision = None, creating multiple Alembic "heads". Running alembic upgrade head fails with a "multiple heads" error.
• Impact: Fresh database setup is impossible without manual intervention. New deployments and CI testing will fail.

C7. Alembic Migrations Swallow All Errors

• File: backend/alembic/versions/001_add_missing_columns.py:23-28
• Bug: _add_column_safe() catches bare Exception and silently passes. This swallows real errors (permission denied, disk full, type mismatches) making them indistinguishable from "column already exists."
• Impact: Database schema corruption can occur silently during migrations with no indication of failure.

C8. No HTTPS/TLS in Production

• File: docker-compose.prod.yml, nginx/nginx.conf
• Bug: Nginx is configured HTTP-only (port 80). No SSL certificate configuration exists. Comment says "no SSL — for IP-based access."
• Impact: All credentials (passwords, JWT tokens, CSRF tokens, Stripe payment data) transmitted in plaintext over the network. GDPR/PCI-DSS violation.

C9. Production Logs Not Persisted

• Files: backend/src/core/logging.py:138-154, docker-compose.prod.yml
• Bug: Backend writes logs to logs/error.log and logs/security.log inside the container, but production docker-compose has no volume mount for /app/logs. Logs are lost on every container restart.
• Impact: No audit trail for security incidents, no debugging capability for production issues, GDPR compliance violation (cannot demonstrate data access logging).

C10. No Automated Backup Scheduling

• File: docker-compose.prod.yml:101-115
• Bug: Backup service exists but requires manual invocation via docker compose --profile backup run backup. No cron job, no scheduling, no offsite storage.
• Impact: Complete data loss risk if the VPS fails. No disaster recovery capability.

C11. XSS Vulnerability in Map Field Labels

• File: frontend/src/components/zemelapis/LeafletMap.tsx:524-526
• Bug: field.pavadinimas is directly interpolated into an HTML template literal for Leaflet marker popup without sanitization: html: `<div>${field.pavadinimas}...</div>`. The sanitize.ts utility exists but is not used here.
• Impact: If a field name contains <script> tags or event handlers, arbitrary JavaScript executes in any user's browser viewing the map.

---

HIGH Severity (16 Issues)

H1. Unbounded Queries — Memory Exhaustion / DoS

• Files: backend/src/employees/router.py:32, backend/src/warehouses/router.py:45,227,360,443,550, backend/src/livestock/router.py:54,230, backend/src/yields/router.py:39, backend/src/fields/router.py:99
• Bug: Multiple list_*() endpoints call .all() without any limit. Pagination utilities exist in core/pagination.py but are never imported or used.
• Impact: A farm with 100K+ records causes OOM crashes. Also exploitable for DoS by repeatedly hitting list endpoints.

H2. Division by Zero in Yield Calculations

• File: backend/src/yields/router.py:54-55, 109
• Bug: data['derlius_ha'] = yield_data.derlius_tonomis / field.plotas_ha — checks field.plotas_ha is truthy but 0.0 is falsy in Python, so plotas_ha = 0.0 passes the check. Line 109 has no null check at all.
• Impact: Server 500 errors when calculating yield per hectare for fields with zero area.

H3. Division by Zero in Carbon Footprint Variance

• File: backend/src/carbon_footprint/service.py:1696
• Bug: variance = (actual - projected) / abs(projection.projected_net_emissions_kg) * 100 — no zero check on projected emissions.
• Impact: ZeroDivisionError when projected emissions are zero (common for new farms).

H4. Unsafe Array Access — IndexError Risk

• File: backend/src/carbon_footprint/service.py:594, 953-954, 1347
• Bug: top = categories[0] without checking if categories is empty. final_proj = projections[-1] without checking if projections is empty.
• Impact: IndexError crashes when processing empty datasets (new farms, empty periods).

H5. No Timeout on Stripe API Calls

• File: backend/src/stripe/service.py:40, 158, 206, 242, 369-375
• Bug: All Stripe API calls (Customer.create, Subscription.modify, PaymentIntent.create, etc.) have no timeout configuration.
• Impact: Payment processing hangs indefinitely if Stripe is slow/down, blocking user workflows and potentially holding database locks.

H6. No Timeout on Weather API Calls

• File: backend/src/weather/service.py:10-25
• Bug: await client.get(url, params=params) has no timeout. raise_for_status() exceptions not fully caught at endpoint level.
• Impact: Indefinite hangs when Open-Meteo API is unresponsive.

H7. Missing Data Validation on NDVI API Response

• File: backend/src/ndvi/service.py:337-345
• Bug: Deeply nested dictionary access (intervals[0]["outputs"]["ndvi"]["bands"]["B0"]["stats"]["mean"]) without any validation that keys exist.
• Impact: KeyError crash if Sentinel Hub changes their response format (breaking API contract).

H8. Chat Context Loads All Records Without Pagination

• File: backend/src/chat/router.py:40-137
• Bug: get_user_context() loads ALL equipment, fields, employees, tasks, seeds, and chemicals for the current user without any limit (only work_logs has .limit(30)).
• Impact: Memory exhaustion for power users. This data is serialized into the Gemini prompt, potentially exceeding token limits too.

H9. Unhandled Promise Rejection in Stream Message

• File: frontend/src/lib/api.ts:702-754
• Bug: Streaming handler chains .then() but reader.read() inside the while loop can throw without being caught by the outer .catch().
• Impact: Unhandled promise rejection can crash the application or leave the UI in an inconsistent state.

H10. Race Condition in NDVI Image Cleanup

• File: frontend/src/hooks/useMapState.ts:240-274
• Bug: Cleanup function revokes blob URLs while NDVI fetch promises are still pending. If ndviImages state changes during fetch, URLs are revoked before images render.
• Impact: "Attempt to revoke object URL not in map" errors, broken NDVI overlay images.

H11. Frontend File Uploads Missing CSRF Tokens

• File: frontend/src/lib/api.ts (11+ upload functions)
• Bug: Direct fetch() calls for file uploads bypass the CSRF token injection that the regular API client provides.
• Impact: Upload endpoints are vulnerable to cross-site request forgery.

H12. No Password Strength Validation

• File: backend/src/auth/schemas.py:8, 17, 44
• Bug: password: str field has no minimum length, no complexity requirements, no common password check. Users can register with single-character passwords.
• Impact: Weak passwords leading to easy account compromise through brute force.

H13. SQL LIKE Wildcard Injection

• Files: backend/src/spray_journal/router.py:33, backend/src/chemicals/router.py:38
• Bug: query.filter(SprayEntry.kultura.ilike(f"%{kultura}%")) — user-supplied % and _ wildcards in LIKE queries are not escaped.
• Impact: Information disclosure through wildcard manipulation. Attacker can use % to match everything or _ for single-char wildcards to enumerate data.

H14. Password Reset Tokens Not Invalidated on Password Change

• File: backend/src/auth/router.py:264-280
• Bug: When a user changes their password via /slaptazodis/keisti, outstanding password reset tokens remain valid.
• Impact: An attacker who obtained a reset token can still use it after the victim changes their password — enabling account takeover.

H15. CI Tests Run Against SQLite, Production Uses PostgreSQL

• File: .github/workflows/quality.yml:68-72
• Bug: DATABASE_URL: sqlite:///:memory: in CI. SQLite and PostgreSQL have different SQL dialects, type systems, and constraint behaviors.
• Impact: Tests pass on SQLite but fail in production PostgreSQL. SQL-specific bugs (array types, JSON operations, concurrent writes) are never caught.

H16. Resource Limits Exceed VPS Capacity

• File: docker-compose.prod.yml
• Bug: Backend (512MB) + Database (512MB) = 1024MB. Architecture doc states deployment target is "cheapest VPS (1GB RAM)", leaving 0MB for OS + Nginx.
• Impact: Constant OOM kills and swap thrashing in production.

---

MEDIUM Severity (28 Issues)

M1. N+1 Query Patterns in Field Listing

• File: backend/src/fields/router.py:102-129
• 1000 fields across 500 farms = 503 separate DB queries instead of 1-2 with proper eager loading.

M2. Race Condition in Equipment Motor Hour Logging

• File: backend/src/equipment/router.py:398-413
• Read-modify-write without database-level locking. Concurrent updates cause lost updates.

M3. Missing Database Indexes

• Files: backend/src/employees/models.py, backend/src/livestock/models.py, backend/src/warehouses/models.py
• Missing indexes on organization_id and (employee_id, timestamp) causing full table scans.

M4. Missing Input Validation on Quantity Adjustments

• Files: backend/src/warehouses/router.py:333, backend/src/chemicals/router.py:266
• Float inputs not validated for NaN, Infinity, or unreasonable values.

M5. Incorrect Livestock Monthly Emissions Calculation

• File: backend/src/carbon_footprint/service.py:228-234
• Dividing annual emissions by 12 for monthly is a simplistic approximation that doesn't account for seasonal variation or herd size changes.

M6. Stripe Payment Handling Missing Transaction Rollback

• File: backend/src/stripe/service.py:271-303
• If db.commit() fails after Stripe processes payment, the org has inconsistent state (paid in Stripe, not recorded in DB).

M7. Nutrient Balance Sum on Nullable Fields

• File: backend/src/nutrient_balance/router.py:109-111
• sum(a.n_kg_ha for a in apps) fails with TypeError if any value is None.

M8. Notification System Has No Push Delivery

• File: backend/src/notifications/router.py:19-37
• Device tokens registered but no background task actually sends push notifications. Dead code.

M9. Chat Stream Response Not Persisted on Disconnect

• File: backend/src/chat/router.py:536-594
• If client disconnects mid-stream, assistant_message is never saved to DB. Partial AI responses are lost.

M10. Deprecated datetime.utcnow() Used Throughout

• Files: backend/src/auth/router.py:341, backend/src/carbon_footprint/service.py, multiple others
• Using deprecated datetime.utcnow() instead of datetime.now(timezone.utc). Can cause timezone-aware vs naive comparison bugs.

M11. CSP Allows unsafe-inline and unsafe-eval

• Files: frontend/next.config.js, backend/src/main.py:156
• Content Security Policy weakened, reducing XSS protection effectiveness.

M12. Offline IndexedDB Stores Data Unencrypted

• File: frontend/src/lib/offlineDb.ts
• Farm data, employee records, financial information stored in plaintext in browser IndexedDB.

M13. No Offline Conflict Resolution Strategy

• File: frontend/src/lib/offlineDb.ts
• Offline sync queue has no merge strategy. Edits from two devices silently overwrite each other.

M14. WebSocket Reconnect Without Backoff

• File: frontend/src/lib/chatSocket.ts:44-46
• Always reconnects after 3 seconds forever, no exponential backoff. Can flood server under failure conditions.

M15. Memory Leaks from Uncleared Timeouts in LeafletMap

• File: frontend/src/components/zemelapis/LeafletMap.tsx:405-410
• setTimeout chains without cleanup. Can cause state updates on unmounted components.

M16. Missing Error Handling in File Deletion

• Files: backend/src/livestock/router.py:126-130, backend/src/equipment/router.py (similar)
• os.remove() without try/except. Permission errors cause entire delete operation to fail with 500.

M17. NDVI Division by Zero in Color Calculation

• File: frontend/src/components/zemelapis/LeafletMap.tsx:287-290
• (coord[0] - west) / (east - west) — if east === west, division by zero produces NaN.

M18. No Pagination for Frontend Field Loading

• File: frontend/src/hooks/useMapState.ts:195-213
• api.getFields() loads ALL fields at once. 1000+ fields will be slow and memory-intensive.

M19. Missing Error Boundary Around Leaflet Map

• File: frontend/src/components/zemelapis/LeafletMap.tsx
• No React error boundary wraps the Leaflet component. Any Leaflet error crashes the entire page.

M20. Grain Price Scraping Range Too Restrictive

• File: backend/src/grain_prices/service.py:98
• Prices outside 50-1000 EUR range silently discarded. Falls back to demo data without user notification.

M21. Rate Limiting DB Uses Relative Path

• File: backend/src/core/rate_limit.py:73-112
• Hardcoded relative path for SQLite rate limit DB. Fails in multi-process or containerized environments.

M22. Makefile Test Target Masks Failures

• File: Makefile:123
• pytest src/ -v --tb=short || echo "..." — the || echo swallows test failures, making make test always succeed.

M23. Makefile Test Path Mismatch

• File: Makefile:123
• pytest src/ but pyproject.toml says testpaths = ["tests"]. Tests may be skipped or wrong directory tested.

M24. Frontend JWT Decoded Without Signature Verification

• File: frontend/src/lib/auth.ts:59-62
• atob(token.split('.')[1]) decodes payload without verifying signature. Used in auth flows to determine user state.

M25. Missing ARIA Labels in Interactive Map

• File: frontend/src/components/zemelapis/LeafletMap.tsx
• Map markers, polygons, and controls lack ARIA attributes. Keyboard navigation not supported. WCAG 2.1 violations.

M26. No CI Step Tests Alembic Migrations

• File: .github/workflows/quality.yml
• No step runs alembic upgrade head on a fresh database. Migration conflicts are not caught before merge.

M27. No Dependency Vulnerability Scanning in CI

• File: .github/workflows/quality.yml
• No npm audit or pip audit step. Known CVEs in dependencies go undetected.

M28. Unsafe Bearer Token Parsing

• File: backend/src/main.py:258
• auth_header.split(" ")[1] can throw IndexError if header is malformed "Bearer " (trailing space, nothing after).

---

LOW Severity (12 Issues)

L1. User Enumeration via Registration Endpoint

• Registration endpoint reveals whether email already exists.

L2. Validation Errors Echo User Input

• Error messages reflect unsanitized user input in responses.

L3. __import__("datetime") Dynamic Import

• File: backend/src/yield_maps/router.py:79
• Obfuscated __import__ usage instead of normal import statement.

L4. setattr() Mass Assignment Pattern

• File: backend/src/field_photos/router.py:206
• Using setattr() in a loop from model dump. Safe with Pydantic but fragile.

L5. No BBCH Growth Stage Code Validation

• File: backend/src/spray_journal/router.py
• BBCH codes (00-99) not validated for range.

L6. Missing Field Size Limits on String Inputs

• Multiple routers accept unbounded string inputs (kategorija, pavadinimas, etc.).

L7. Inconsistent Error Response Formats

• Some routers use api_error() helper, others use raw HTTPException. No standard error schema.

L8. Boolean Assertions in Tests

• assert data["atlikta"] == False instead of assert data["atlikta"] is False.

L9. JSON.stringify for Deep Comparison

• File: frontend/src/hooks/useMapState.ts:513
• O(n) JSON stringification for coordinate comparison in a loop.

L10. Color-Only Field Status Indicators

• File: frontend/src/components/zemelapis/LeafletMap.tsx:495-501
• Fields distinguished only by color. Inaccessible to color-blind users.

L11. No Log Rotation Configured

• logging.FileHandler used without rotation. Logs can grow unbounded.

L12. usePendingSync Hook Dependency Causes Interval Restart

• File: frontend/src/hooks/usePendingSync.ts:66-96
• refresh function in dependency array recreated every render, causing constant interval restart.

---

---

Part 2: Claude Opus 4.6 Fix Prompts

How to use these prompts: Each prompt follows the structured XML format optimized for Claude Opus 4.6, using the Explore → Plan → Code → Commit workflow from the playbook. Prompts are grouped by severity and domain. Use them in Claude Code with the ultrathink keyword for complex fixes and think for straightforward ones.

---

CRITICAL Fixes

Prompt 1: Fix Gemini AI Race Condition (C1)

ultrathink

<task>Fix the critical race condition in Gemini AI chat integration that causes cross-tenant data leaks</task>

<context>
- FastAPI async backend with Google Gemini AI integration
- Module-level context variables `_current_db_var` and `_current_user_id_var` in `backend/src/integrations/gemini_tools.py` are shared across concurrent async requests
- These variables are set in `backend/src/integrations/gemini_chat.py:52-54` before tool function execution
- When two users chat simultaneously, User A's tool functions can execute with User B's database session and user ID
- This is the #1 severity bug — it causes cross-tenant data creation and information disclosure
</context>

<constraints>
- Use Python contextvars.ContextVar which IS async-safe (one value per async task), NOT module-level globals
- Do NOT change the Gemini tool function signatures if possible — minimize blast radius
- Ensure database sessions are properly scoped to each request
- All existing chat tests must continue to pass
- Do NOT over-engineer: the fix should be surgical, replacing globals with ContextVar
</constraints>

<output_requirements>
- Modified `backend/src/integrations/gemini_tools.py` with ContextVar replacing module globals
- Modified `backend/src/integrations/gemini_chat.py` to set context variables using ContextVar.set()
- Verify the fix handles the case where ContextVar is accessed without being set (raise clear error, don't return None)
- Run existing tests to confirm no regressions
</output_requirements>

Prompt 2: Fix CSRF Protection for Cookie Auth (C2)

think hard

<task>Fix broken CSRF protection for cookie-authenticated users</task>

<context>
- FastAPI backend using JWT tokens in httpOnly cookies as primary auth method
- CSRF middleware in `backend/src/main.py:234-238` only validates CSRF token from Bearer header
- Cookie-authenticated users (the majority of users) bypass CSRF protection entirely
- CSRF token generation exists in `backend/src/core/csrf.py`
- Frontend API client in `frontend/src/lib/api.ts` has CSRF token handling but file uploads bypass it
</context>

<constraints>
- CSRF validation must apply to ALL state-changing requests (POST, PUT, PATCH, DELETE) when using cookie auth
- CSRF token should be sent via X-CSRF-Token header (already partially implemented)
- GET/HEAD/OPTIONS requests must be exempt from CSRF checks
- Bearer token auth (API clients) should remain exempt from CSRF (they don't need it)
- Fix the 11+ frontend file upload functions that bypass CSRF token injection
- Do NOT break existing authentication flows
</constraints>

<output_requirements>
- Modified CSRF middleware in `backend/src/main.py`
- Modified frontend upload functions in `frontend/src/lib/api.ts` to include CSRF tokens
- Verify with a test that cookie-auth POST requests without CSRF token are rejected
</output_requirements>

Prompt 3: Add Auth to File Access Endpoints (C3)

think

<task>Add ownership verification to all file-serving endpoints that currently lack it</task>

<context>
- Four file-serving endpoints serve photos without verifying the requesting user owns the resource:
  - `backend/src/equipment/router.py:289` — equipment photos
  - `backend/src/livestock/router.py:181` — livestock photos
  - `backend/src/ai_scanner/router.py:197` — AI scan images
  - `backend/src/warehouses/router.py:185` — warehouse photos
- These endpoints use media token verification but skip ownership checks
- The user's organization_id should be checked against the resource's organization_id
</context>

<constraints>
- After token verification, add a check that current_user.organization_id matches the resource's organization_id
- Return 404 (not 403) for resources that don't belong to the user (prevent enumeration)
- Keep the existing token-based URL scheme — just add the ownership layer
- Don't break existing photo serving for legitimate users
</constraints>

<output_requirements>
- Modified router files for all four endpoints
- Each endpoint verifies ownership before serving the file
- Test that cross-organization photo access returns 404
</output_requirements>

Prompt 4: Fix Employee GPS IDOR (C4)

think

<task>Fix IDOR vulnerability on employee GPS location history endpoint</task>

<context>
- `backend/src/employees/router.py:269-284` — GET /{employee_id}/lokacijos
- Any authenticated user can request any employee's location history by ID
- Should verify that the employee belongs to the current user's organization
- Similar pattern to other router files that use `get_or_404()` with ownership verification
</context>

<constraints>
- Add organization_id ownership check before returning location data
- Return 404 for employees not in the user's organization
- Check for similar IDOR issues on employee shift history endpoint
- Follow the existing `get_or_404()` pattern used in other routers
</constraints>

<output_requirements>
- Modified `backend/src/employees/router.py` with ownership verification on location and shift endpoints
- Run existing employee tests
</output_requirements>

Prompt 5: Fix Offline Sync Retry Counter (C5)

think

<task>Fix operator precedence bug in offline sync retry counter</task>

<context>
- `frontend/src/lib/offlineDb.ts:160`
- Current code: `retries: (await this.pendingSync.get(id))?.retries ?? 0 + 1`
- Due to JS operator precedence, `0 + 1` evaluates to `1` BEFORE nullish coalescing
- Retries always reset to 1 instead of incrementing
- This breaks the entire offline sync retry tracking
</context>

<constraints>
- Fix is a single line: add parentheses around the fallback expression
- Correct code: `retries: ((await this.pendingSync.get(id))?.retries ?? 0) + 1`
- Add a max retries constant (e.g., 5) and check before retrying
- Run frontend tests after fix
</constraints>

<output_requirements>
- Fixed line in `frontend/src/lib/offlineDb.ts`
- Added max retry limit
- Verify no other operator precedence issues in the same file
</output_requirements>

Prompt 6: Fix Alembic Migration Conflicts (C6 + C7)

think hard

<task>Fix Alembic migration revision conflicts and silent error swallowing</task>

<context>
- Two migration files both have `down_revision = None`:
  - `backend/alembic/versions/001_initial_schema.py` (revision: "001_initial")
  - `backend/alembic/versions/001_add_missing_columns.py` (revision: "001_add_missing_columns")
- Running `alembic upgrade head` fails with "multiple heads" error
- The second migration uses `_add_column_safe()` that catches bare `Exception` and silently passes
- No downgrade functions exist in either migration
</context>

<constraints>
- The second migration must chain after the first: set `down_revision = "001_initial"`
- Replace the bare `except Exception: pass` with proper error handling that distinguishes "column already exists" from real errors
- Add `downgrade()` functions that reverse the changes
- Do NOT restructure the existing migration files — just fix the chain and error handling
- Test that `alembic upgrade head` works on a fresh database
</constraints>

<output_requirements>
- Modified `backend/alembic/versions/001_add_missing_columns.py` with correct down_revision
- Proper error handling in `_add_column_safe()` using database introspection instead of try/except
- Added downgrade() function
- Verification that `alembic upgrade head` succeeds
</output_requirements>

Prompt 7: Fix XSS in Map Field Labels (C11)

think

<task>Fix XSS vulnerability in Leaflet map field name rendering</task>

<context>
- `frontend/src/components/zemelapis/LeafletMap.tsx:524-526`
- Field names are directly interpolated into HTML template literals for Leaflet markers
- Code: `html: \`<div>${field.pavadinimas}...</div>\``
- A `sanitize.ts` utility exists in `frontend/src/lib/sanitize.ts` but is not used here
- User-controlled field names could contain `<script>` tags or event handlers
</context>

<constraints>
- Use the existing `sanitize.ts` utility or `textContent`-safe approaches
- Apply the fix to ALL places where user data is rendered as HTML in Leaflet components
- Do NOT break existing map label styling
- Prefer escaping over sanitization where possible (simpler, more secure)
</constraints>

<output_requirements>
- Modified LeafletMap.tsx with proper HTML escaping for all user-controlled content
- Verify all template literals in the file that interpolate user data are secured
- Check for similar patterns in other map components
</output_requirements>

---

HIGH Severity Fixes

Prompt 8: Add Pagination to All List Endpoints (H1)

think hard

<task>Add pagination to all unbounded list endpoints using the existing pagination utility</task>

<context>
- `backend/src/core/pagination.py` provides `PaginationParams` and `paginate()` helpers — but they are never used
- The following endpoints call `.all()` without limits:
  - employees/router.py:32 — list_employees()
  - warehouses/router.py:45,227,360,443,550 — list_warehouses(), list_grains(), list_warehouse_equipment(), list_warehouse_chemicals(), list_warehouse_livestock()
  - livestock/router.py:54,230 — list_livestock(), list_health_records()
  - yields/router.py:39 — list_yields()
  - fields/router.py:99 — list_fields()
  - chat/router.py:40-137 — get_user_context() loads ALL user data
</context>

<constraints>
- Use the existing `PaginationParams` dependency and `paginate()` helper from `core/pagination.py`
- Default page size: 50 items, max: 200
- For `get_user_context()` in chat, limit each entity to 100 most recent records
- Add `skip` and `limit` query parameters to all list endpoints
- Return total count in response headers or wrapper for frontend pagination
- Do NOT change response schema for individual items — only wrap in paginated response
</constraints>

<output_requirements>
- All listed router files updated with pagination
- Consistent pagination across all endpoints
- Run tests to verify no regressions
</output_requirements>

Prompt 9: Fix Division by Zero in Calculations (H2 + H3 + H4)

think

<task>Fix all division-by-zero and unsafe array access bugs in yield and carbon footprint calculations</task>

<context>
- `backend/src/yields/router.py:54-55` — divides by field.plotas_ha without zero check (0.0 is falsy but passes truthy check)
- `backend/src/yields/router.py:109` — no null check before division
- `backend/src/carbon_footprint/service.py:1696` — divides by projected emissions without zero check
- `backend/src/carbon_footprint/service.py:594` — `categories[0]` without empty check
- `backend/src/carbon_footprint/service.py:953-954` — unsafe first/last access
- `backend/src/carbon_footprint/service.py:1347` — `projections[-1]` without empty check
</context>

<constraints>
- For division: check `> 0` not just truthy. Return 0 or None for undefined ratios
- For array access: check `len() > 0` before indexing
- Fix is surgical — only add guards, don't restructure calculations
- Use consistent pattern: `value / divisor if divisor and divisor > 0 else 0`
</constraints>

<output_requirements>
- Modified yields/router.py with safe division
- Modified carbon_footprint/service.py with safe array access and division
- Run existing tests
</output_requirements>

Prompt 10: Add Timeouts to All External API Calls (H5 + H6 + H7)

think

<task>Add timeouts and response validation to all external API integrations</task>

<context>
- Stripe API calls in `backend/src/stripe/service.py` have no timeout (lines 40, 158, 206, 242, 369-375)
- Weather API in `backend/src/weather/service.py:10-25` has no timeout
- NDVI API in `backend/src/ndvi/service.py:337-345` has deep nested dict access without validation
- Grain prices scraper in `backend/src/grain_prices/service.py` has inconsistent timeout handling
</context>

<constraints>
- Stripe: Configure `stripe.max_network_retries = 2` and `stripe.DEFAULT_TIMEOUT = 30` at module level
- httpx calls: Add `timeout=httpx.Timeout(30.0)` to all AsyncClient instances
- NDVI response: Add try/except KeyError with meaningful error messages and fallback to None
- Wrap all external calls in try/except that catches timeout and returns appropriate error responses
- Do NOT add retry logic everywhere — just timeouts and error handling
</constraints>

<output_requirements>
- Modified stripe/service.py with timeout configuration
- Modified weather/service.py with timeout
- Modified ndvi/service.py with response validation
- Modified grain_prices/service.py with consistent timeout
- Run existing tests
</output_requirements>

Prompt 11: Fix Password Strength Validation (H12)

think

<task>Add password strength validation to user registration and password change</task>

<context>
- `backend/src/auth/schemas.py` — password field is bare `str` with no constraints
- No minimum length, no complexity requirements, no common password check
- Users can register with single-character passwords
- Registration: UserCreate schema (line 8)
- Password change: PasswordChangeRequest schema
- Password reset: ResetPasswordConfirm schema
</context>

<constraints>
- Minimum 8 characters
- At least one uppercase, one lowercase, one digit
- Use Pydantic field_validator, not external libraries
- Apply the same validation to registration, password change, AND password reset
- Return clear Lithuanian error messages for each failed rule
- Do NOT add common password dictionary check (over-engineering)
</constraints>

<output_requirements>
- Modified `backend/src/auth/schemas.py` with password validators
- Same validation applied to all password-accepting schemas
- Test that weak passwords are rejected
</output_requirements>

Prompt 12: Invalidate Reset Tokens on Password Change (H14)

think

<task>Invalidate all pending password reset tokens when a user changes their password</task>

<context>
- `backend/src/auth/router.py:264-280` — change_password endpoint
- When password is changed, outstanding reset tokens remain valid
- Password reset model likely in `backend/src/auth/models.py`
- An attacker with a leaked reset token can use it even after the victim changes their password
</context>

<constraints>
- After successful password change, mark all pending reset tokens for that user as used/expired
- Also invalidate tokens on successful password reset (prevent token reuse)
- Do NOT delete tokens (keep for audit trail) — mark them as consumed
- Single database query to bulk-update
</constraints>

<output_requirements>
- Modified change_password endpoint to invalidate tokens
- Modified reset_password endpoint to invalidate all other tokens for the same user
- Test that used tokens cannot be reused
</output_requirements>

---

MEDIUM Severity Fixes

Prompt 13: Fix N+1 Queries in Field Listing (M1)

think

<task>Eliminate N+1 query patterns in the field listing endpoint</task>

<context>
- `backend/src/fields/router.py:102-129`
- For each field, a separate query fetches the farm record: `db.query(Farm).filter(Farm.id == f.farm_id).first()`
- With 1000 fields across 500 farms, this generates 503 separate DB queries
- Farm data is cached per-request in `farm_cache` dict, but still makes one query per unique farm_id
- Similar N+1 patterns exist in employees and livestock statistics
</context>

<constraints>
- Use SQLAlchemy `joinedload()` or `selectinload()` for relationships
- OR: batch-fetch all farm_ids in a single `WHERE id IN (...)` query upfront
- Prefer the simpler approach (batch fetch) if relationship isn't defined in the ORM
- Do NOT change the API response schema
- Apply the same fix pattern to employees/router.py and livestock/router.py
</constraints>

<output_requirements>
- Modified fields/router.py with efficient loading (1-3 queries max)
- Applied same pattern to employees and livestock where applicable
- Run tests to verify response data is unchanged
</output_requirements>

Prompt 14: Fix Race Condition in Motor Hour Logging (M2)

think

<task>Fix race condition in equipment motor hour (motovalandos) logging</task>

<context>
- `backend/src/equipment/router.py:398-413`
- Pattern: read current hours → add → write back
- Two concurrent requests can both read the same value, causing a lost update
- Example: Thread A reads 100, Thread B reads 100. A writes 150, B writes 130. Should be 180.
</context>

<constraints>
- Use SQLAlchemy database-level atomic update: `Equipment.dabartines_motovalandos = Equipment.dabartines_motovalandos + moto_data.valandos`
- Use `db.flush()` to execute the update immediately and get the new value
- Do NOT use application-level locks or SELECT FOR UPDATE (over-engineering for this case)
- Apply the same pattern to chemical quantity adjustments in `warehouses/router.py:333`
</constraints>

<output_requirements>
- Modified equipment/router.py with atomic update
- Modified warehouses/router.py chemical quantity adjustment with same pattern
- Run tests
</output_requirements>

Prompt 15: Fix WebSocket Reconnection Strategy (M14)

think

<task>Add exponential backoff to WebSocket reconnection logic</task>

<context>
- `frontend/src/lib/chatSocket.ts:44-46`
- Currently reconnects after a flat 3 seconds forever
- Under server failure, this floods the server with reconnect attempts
</context>

<constraints>
- Implement exponential backoff: 1s, 2s, 4s, 8s, 16s, max 30s
- Add max reconnect attempts (10) before giving up
- Reset backoff on successful connection
- Add a method to manually trigger reconnection
- Keep the API surface minimal
</constraints>

<output_requirements>
- Modified chatSocket.ts with exponential backoff
- Max retry limit
- Reset on successful connection
</output_requirements>

Prompt 16: Add Missing Database Indexes (M3)

think

<task>Add missing database indexes on frequently filtered columns</task>

<context>
- Missing indexes identified on:
  - `backend/src/employees/models.py` — no index on `organization_id`
  - `backend/src/employees/models.py` — no index on `(employee_id, timestamp)` for location queries
  - `backend/src/livestock/models.py` — no index on `organization_id`
  - `backend/src/warehouses/models.py` — no index on `organization_id`
- These cause full table scans on filtered queries in production with PostgreSQL
</context>

<constraints>
- Add indexes via SQLAlchemy `__table_args__` using `Index()` objects
- Use composite indexes where queries filter on multiple columns together
- Create a new Alembic migration for the index additions (do NOT modify existing migrations)
- Indexes should be named descriptively: `idx_{table}_{columns}`
</constraints>

<output_requirements>
- Modified model files with index declarations
- New Alembic migration file for the indexes
- Verify migration applies cleanly
</output_requirements>

Prompt 17: Fix Datetime Handling Across Backend (M10)

think

<task>Replace all deprecated datetime.utcnow() with timezone-aware datetime.now(timezone.utc)</task>

<context>
- Multiple files use `datetime.utcnow()` which is deprecated in Python 3.12+
- Found in: auth/router.py:341, carbon_footprint/service.py, and many other files
- Mixing timezone-aware and naive datetimes can cause comparison bugs
- Password reset token expiration is affected
</context>

<constraints>
- Replace `datetime.utcnow()` with `datetime.now(timezone.utc)` everywhere
- Also replace `datetime.now()` (local time) with `datetime.now(timezone.utc)` where UTC is intended
- Import `from datetime import timezone` where needed
- Do NOT change database column types or stored values
- Run all tests after changes
</constraints>

<output_requirements>
- All files updated to use timezone-aware datetimes
- Consistent UTC usage throughout the backend
- All tests pass
</output_requirements>

Prompt 18: Fix Frontend Error Handling and Error Boundaries (M19 + H9)

think

<task>Add error boundaries around Leaflet map and fix streaming promise rejection handling</task>

<context>
- `frontend/src/components/zemelapis/LeafletMap.tsx` — Leaflet errors crash the entire page
- `frontend/src/lib/api.ts:702-754` — streaming handler has unhandled promise rejection when reader.read() throws
- No React error boundaries exist in the app
</context>

<constraints>
- Create a single reusable ErrorBoundary component in `frontend/src/components/ui/`
- Wrap LeafletMap with the error boundary showing a "Map failed to load" fallback
- Fix the streaming handler to properly catch reader.read() errors
- Keep the error boundary simple — just catch, log, and show fallback UI
- Do NOT add error boundaries to every component (over-engineering)
</constraints>

<output_requirements>
- New ErrorBoundary component (minimal, reusable)
- LeafletMap wrapped with error boundary
- Fixed streaming error handling in api.ts
- Run frontend tests
</output_requirements>

---

Infrastructure & DevOps Fixes

Prompt 19: Fix CI Pipeline to Test Against PostgreSQL (H15)

think hard

<task>Fix CI pipeline to test against PostgreSQL instead of SQLite, and add migration testing</task>

<context>
- `.github/workflows/quality.yml:68-72` runs tests with `DATABASE_URL: sqlite:///:memory:`
- Production uses PostgreSQL 16 — SQLite has different SQL dialect and constraints
- No CI step verifies that Alembic migrations work
- Frontend tests have no coverage threshold
</context>

<constraints>
- Add PostgreSQL 16 service container to the quality workflow
- Change test DATABASE_URL to use the PostgreSQL service
- Add a step to run `alembic upgrade head` before tests
- Add `--cov-fail-under=80` (up from 70)
- Add `npm audit --audit-level=high` and `pip audit` steps for dependency scanning
- Keep the workflow fast — run backend and frontend tests in parallel jobs
</constraints>

<output_requirements>
- Modified `.github/workflows/quality.yml` with PostgreSQL service
- Migration test step added
- Dependency scanning steps added
- Coverage threshold increased
</output_requirements>

Prompt 20: Fix Docker Production Configuration (C8 + C9 + C10 + H16)

think hard

<task>Fix critical production Docker issues: add HTTPS support, log persistence, backup automation, and resource limits</task>

<context>
- `docker-compose.prod.yml` has no HTTPS/TLS (all traffic plaintext)
- No volume mount for `/app/logs` — logs lost on container restart
- Backup service requires manual invocation, no scheduling
- Resource limits (512MB backend + 512MB DB) exceed 1GB VPS target
- `nginx/nginx.conf` only configured for HTTP
</context>

<constraints>
- Add a log volume mount for backend: `backend-logs:/app/logs`
- Add log rotation via logrotate or Python RotatingFileHandler
- Reduce memory limits: backend 384MB, database 384MB, frontend 192MB, nginx 64MB
- Add a cron-based backup schedule comment/script (daily at 2 AM)
- For HTTPS: add Certbot/Let's Encrypt configuration to nginx with auto-renewal
- Add backup retention (keep last 7 daily, 4 weekly)
- Do NOT set up a full Kubernetes deployment (over-engineering)
</constraints>

<output_requirements>
- Modified `docker-compose.prod.yml` with log volumes and adjusted resource limits
- Modified backend logging to use RotatingFileHandler
- Backup scheduling script or cron configuration
- HTTPS nginx configuration with Certbot
- Updated `nginx/nginx.conf` for TLS termination
</output_requirements>

Prompt 21: Fix Makefile and Test Configuration (M22 + M23)

think

<task>Fix Makefile test targets that mask failures and use wrong paths</task>

<context>
- `Makefile:123` — `pytest src/ -v --tb=short || echo "..."` swallows test failures
- `pyproject.toml` says `testpaths = ["tests"]` but Makefile runs `pytest src/`
- No make target for E2E tests or database migrations
- Frontend tests have no coverage enforcement
</context>

<constraints>
- Remove the `|| echo` that masks failures — let pytest exit code propagate
- Change `pytest src/` to `pytest tests/` to match pyproject.toml
- Add `make test-e2e` target for Playwright tests
- Add `make db-migrate` target for Alembic
- Add `make mypy` target for backend type checking
- Keep targets simple and composable
</constraints>

<output_requirements>
- Modified Makefile with fixed targets
- New targets for e2e, migration, and type checking
- Verify `make test` properly fails on test failure
</output_requirements>

---

Batch Fix Prompts (Multiple Related Issues)

Prompt 22: Security Hardening Batch (H12 + H13 + H14 + M11 + L1)

ultrathink

<task>Comprehensive security hardening: password validation, LIKE injection, token invalidation, CSP, and user enumeration</task>

<context>
This is a batch fix for related security issues:
1. No password strength validation — `backend/src/auth/schemas.py`
2. SQL LIKE wildcard injection — `backend/src/spray_journal/router.py:33`, `backend/src/chemicals/router.py:38`
3. Password reset tokens not invalidated on password change — `backend/src/auth/router.py:264-280`
4. CSP allows unsafe-inline/unsafe-eval — `backend/src/main.py:156`, `frontend/next.config.js`
5. User enumeration via registration — registration reveals if email exists
</context>

<constraints>
- Password: min 8 chars, uppercase, lowercase, digit required
- LIKE: escape `%` and `_` in user input before passing to ilike()
- Tokens: invalidate all pending reset tokens on password change AND on successful reset
- CSP: remove unsafe-inline where possible, use nonce-based approach if needed
- Enumeration: return the same response message whether email exists or not on registration
- Do NOT install new dependencies for any of these fixes
- Run tests after each fix
</constraints>

<output_requirements>
- Modified auth/schemas.py with password validators
- Modified spray_journal/router.py and chemicals/router.py with LIKE escaping
- Modified auth/router.py with token invalidation
- Modified CSP headers in main.py and next.config.js
- Modified registration response to prevent enumeration
- All existing tests pass
</output_requirements>

Prompt 23: Data Integrity Batch (H2 + H3 + H4 + M4 + M7)

think hard

<task>Fix all data integrity issues: division by zero, unsafe array access, and missing input validation</task>

<context>
Batch fix for calculation safety:
1. Division by zero in yields — `backend/src/yields/router.py:54-55, 109`
2. Division by zero in carbon footprint — `backend/src/carbon_footprint/service.py:1696`
3. Unsafe array access — `backend/src/carbon_footprint/service.py:594, 953, 1347`
4. NaN/Infinity not validated on quantity inputs — `backend/src/warehouses/router.py:333`, `backend/src/chemicals/router.py:266`
5. None values in nutrient balance sum — `backend/src/nutrient_balance/router.py:109-111`
</context>

<constraints>
- Division: always check `divisor > 0` before dividing. Return 0 for undefined ratios
- Arrays: always check `len() > 0` before indexing
- Floats: validate with `math.isfinite()` before database operations. Reject NaN and Infinity
- Sums: use `sum(x for x in values if x is not None)` pattern for nullable fields
- Add Pydantic validators for float fields that reject non-finite values
- Fixes should be minimal — add guards, don't restructure logic
</constraints>

<output_requirements>
- Modified yields/router.py, carbon_footprint/service.py, warehouses/router.py, chemicals/router.py, nutrient_balance/router.py
- All calculations safe from zero division and None values
- Run all backend tests
</output_requirements>

Prompt 24: Frontend Resilience Batch (C5 + M15 + M17 + M14 + L12)

think

<task>Fix frontend resilience issues: retry counter, memory leaks, division by zero, WebSocket backoff, and hook dependencies</task>

<context>
Batch fix for frontend stability:
1. Retry counter operator precedence — `frontend/src/lib/offlineDb.ts:160`
2. setTimeout memory leaks in LeafletMap — `frontend/src/components/zemelapis/LeafletMap.tsx:405-410`
3. Division by zero in NDVI color calc — `frontend/src/components/zemelapis/LeafletMap.tsx:287-290`
4. WebSocket flat reconnect — `frontend/src/lib/chatSocket.ts:44-46`
5. usePendingSync hook interval restart — `frontend/src/hooks/usePendingSync.ts:66-96`
</context>

<constraints>
- Retry: add parentheses `((await ...) ?? 0) + 1` and max retry limit
- Timeouts: store timeout IDs in refs and clear on unmount
- Division: check denominator !== 0 before dividing, default to 0
- WebSocket: exponential backoff (1s, 2s, 4s, 8s, 16s, max 30s), max 10 attempts
- Hook: wrap refresh in useCallback with proper deps, or use useRef for interval
- Run frontend tests after all changes
</constraints>

<output_requirements>
- Modified offlineDb.ts, LeafletMap.tsx, chatSocket.ts, usePendingSync.ts
- All memory leaks plugged
- All calculations safe
- Run frontend tests
</output_requirements>

---

Quick Win Prompts (5-minute fixes)

Prompt 25: Quick Wins Batch

think

<task>Apply a batch of quick, low-risk fixes across the codebase</task>

<context>
Quick wins that are safe and isolated:
1. `backend/src/yield_maps/router.py:79` — replace `__import__("datetime")` with normal import
2. `backend/src/main.py:258` — add safe split for Bearer token: use `auth_header[7:]` instead of `.split(" ")[1]`
3. `backend/src/livestock/router.py:126-130` — wrap os.remove() in try/except OSError
4. `frontend/src/hooks/useMapState.ts:513` — replace JSON.stringify comparison with coordinate comparison helper
5. Add `.isfinite()` check to warehouse quantity adjustment
</context>

<constraints>
- Each fix is 1-5 lines
- No new dependencies
- No API changes
- No schema changes
- Run tests after all fixes
</constraints>

<output_requirements>
- All five files modified with minimal fixes
- Run backend and frontend tests
- Commit with message describing quick wins
</output_requirements>

---

Full Codebase Audit Prompt (For Ongoing Maintenance)

Prompt 26: Periodic Security Audit

ultrathink

Do NOT write any code yet. This is an audit task only.

<task>Perform a comprehensive security audit of the LandAgro codebase</task>

<context>
- FastAPI backend with JWT cookie auth, CSRF protection, rate limiting
- Next.js frontend with Zustand state management and offline IndexedDB
- External integrations: Google Gemini AI, Sentinel Hub NDVI, Stripe payments, Open-Meteo weather
- Multi-tenant via organizations
- Previous audit score: 5/10 (see SECURITY_REVIEW.md)
</context>

<audit_scope>
1. Authentication & session management (JWT, cookies, CSRF)
2. Authorization & access control (IDOR, tenant isolation)
3. Input validation & injection (SQL, XSS, command injection, path traversal)
4. External API security (timeouts, response validation, key management)
5. Data protection (encryption at rest, PII handling, GDPR compliance)
6. Frontend security (CSP, sanitization, secure storage)
7. Infrastructure (Docker, nginx, TLS, logging)
</audit_scope>

<output_requirements>
- Categorized findings with severity (CRITICAL/HIGH/MEDIUM/LOW)
- Exact file paths and line numbers for each finding
- Recommended fix for each finding
- Updated security score out of 10
- Priority-ordered remediation plan
</output_requirements>