Soiloptix Command Center Audit

# TASK: SoilOptix Command Center — Security Audit & Pre-Deploy Fixes

**Priority:** HIGH (priority 20)
**Agent:** engineer
**Filed:** 2026-02-21
**Filed by:** Doug (via Claude Code session)
**Trello:** Soil board → In Progress

---

## Overview

The SoilOptix Command Center (`farmiq.ai/soiloptix/`) is a Flask app for managing
SoilOptix scanning workflows, Midwest Labs sample submission, and field operations.
It's ~85% feature-complete but has critical security issues and schema gaps that
must be fixed before production testing.

**App location:** `/data/Sandbox/farmiq.ai/soiloptix/`
**Templates:** `/data/Sandbox/farmiq.ai/soiloptix/templates/`
**Sync script:** `/data/Sandbox/SoilOptix/soiloptix_sync.py`
**Report gen:** `/data/Sandbox/SoilOptix/generate_customer_reports.py`

---

## Step 1: Fix Credential Exposure (CRITICAL)

### 1a. Remove hardcoded default password from app.py

In `farmiq.ai/soiloptix/app.py`, find the DB config dict and remove the fallback:
```python
# BEFORE (BAD):
'password': os.environ.get('DB_PASS', 'FarmTech2025'),

# AFTER (GOOD):
'password': os.environ.get('DB_PASS'),
```

### 1b. Hash the default admin user password

In the `init_db()` function in app.py, the default user insert uses plaintext:
```python
INSERT INTO soiloptix_users (username, password, name, active)
VALUES ('dougw', 'FarmTech2025', 'Doug Weist', 1)
```

Change this to use bcrypt:
```python
import bcrypt
hashed = bcrypt.hashpw(os.environ.get('SOILOPTIX_ADMIN_PASS', 'changeme').encode(), bcrypt.gensalt()).decode()
# Then use hashed in the INSERT
```

### 1c. Verify .env files are gitignored

Check that these paths are in `.gitignore`:
- `farmiq.ai/soiloptix/.env`
- `SoilOptix/.env`

If not already gitignored, add them. Do NOT remove the files — they're needed locally.

---

## Step 2: Fix Database Schema Drift

The code in app.py references columns that may not exist in the migration.
Read the `init_db()` function and the `migrations/001_create_soiloptix_jobs.sql` file.

Ensure these columns exist in the CREATE TABLE statements:

**target_operations table needs:**
- `skipped_points INTEGER DEFAULT 0`
- `current_point INTEGER DEFAULT 0`
- `status VARCHAR(20) DEFAULT 'active'`

**target_points table needs:**
- `distance_from_target FLOAT`
- `skip_reason VARCHAR(100)`
- `notes TEXT`

Add `ALTER TABLE` statements if the columns are missing, or update the CREATE TABLE
in init_db() if it creates the table from scratch.

---

## Step 3: Add CSRF Protection

Install flask-wtf and add CSRF middleware:

```python
from flask_wtf.csrf import CSRFProtect
csrf = CSRFProtect(app)
```

Add `{{ csrf_token() }}` to all form templates:
- `templates/login.html`
- `templates/command_center.html`
- `templates/soil_sampling.html`
- `templates/target_sampling.html`
- `templates/prepare_submission.html`

For AJAX endpoints, add the CSRF token to request headers.

---

## Step 4: Fix Missing @login_required

Add `@login_required` decorator to the `/api/feedback` endpoint in app.py.
Scan for any other unprotected endpoints that should require auth.

---

## Step 5: Update requirements.txt

Add missing dependencies to `farmiq.ai/soiloptix/requirements.txt`:

```
flask>=2.3.0
mysql-connector-python>=8.0.0
python-dotenv>=1.0.0
gunicorn>=21.0.0
requests>=2.31.0
bcrypt>=4.0.0
flask-wtf>=1.1.0
numpy>=1.21.0
matplotlib>=3.5.0
reportlab>=3.6.0
scipy>=1.7.0
```

---

## Step 6: Fix Hardcoded Paths in Report Generators

In `SoilOptix/generate_customer_reports.py` and `generate_missing_reports.py`,
replace hardcoded Pi paths with env-variable fallbacks:

```python
# BEFORE:
SOILOPTIX_DATA = Path("/data/soiloptix_data")
REPORT_OUTPUT = Path("/data/Sandbox/reports/soiloptix")

# AFTER:
SOILOPTIX_DATA = Path(os.environ.get("SOILOPTIX_DATA_DIR", "/data/soiloptix_data"))
REPORT_OUTPUT = Path(os.environ.get("SOILOPTIX_REPORT_DIR", "/data/Sandbox/reports/soiloptix"))
```

---

## Step 7: Verify & Test

After all fixes:

1. Run `python farmiq.ai/soiloptix/app.py` and verify it starts without errors
2. Check that all 6 DB tables are created correctly
3. Verify login works with the hashed password
4. Check that CSRF tokens render in the templates
5. Confirm requirements.txt installs cleanly: `pip install -r requirements.txt --dry-run`

---

## DO NOT

- Do NOT rotate credentials (Doug will do that manually)
- Do NOT deploy to GoDaddy (just fix the code in the repo)
- Do NOT send any emails about this work
- Do NOT modify the Midwest Labs client code (`farmiq.ai/soiloptix/midwest_labs/`)
- Do NOT modify `SoilOptix/soiloptix_sync.py` (it's working)
- Do NOT change the .env files themselves — only fix the code that reads them

## After Completion

- Commit with message: "fix: SoilOptix command center security audit + schema fixes"
- Push to GitHub
- Post to dashboard: "SoilOptix Command Center audit complete — ready for Doug to test deploy"