Plan: Sales Tax Report — FO + Shiplap + Sundance

Objective

Upgrade the existing Shopify-based sales tax portal to produce a clean, CPA-ready quarterly sales tax report broken down by state/nexus. Output should be something Martin can open and use directly for Q1 2026 filing.

Context

• Existing portal was built from scratch using Shopify order data
• Portal location: check ~/ai-projects-local/mission-control/ for existing tax report scripts
• Shopify orders have billing_address.province_code (state) + total_tax fields
• QuickBooks MCP is connected for all 5 companies — can cross-reference
• CPA name: Martin
• Q1 2026 filing = Jan 1 – Mar 31, 2026
• Stores: Fabric Outlet, Shiplap Quilt Shop, Sundance Quilting

Key files to check first:

• ~/ai-projects-local/mission-control/scripts/ — search for any existing tax, sales_tax, or nexus scripts
• ~/ai-projects-local/shopify-bigquery-pipeline/data/shopify.db — primary data source
• QuickBooks MCP — cross-reference with qb_get_profit_loss and qb_list_transactions

Phase 1: Audit Existing Portal + Data Assessment

DISPATCH tasks:

1. Search for all existing sales tax scripts: grep -rl "tax" ~/ai-projects-local/mission-control/scripts/
2. Read any found scripts and document: what they do, what data they use, what's broken/missing

3. Query shopify.db to assess tax data quality: sql SELECT strftime('%Y-%m', created_at) as month, billing_province, COUNT(*) as order_count, SUM(total_tax) as total_tax, SUM(total_price) as total_revenue FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-04-01' GROUP BY month, billing_province ORDER BY month, total_tax DESC

4. Run same query for each store (use store column or separate databases)

5. Identify: which states have tax collected? Any states with $0 tax where we likely have nexus? Any NULL/missing state fields?
6. Pull QuickBooks trial balance Q1 2026 via MCP for cross-reference totals
7. Write findings report: reports/sales-tax-data-audit-q1-2026.md

Acceptance Criteria:

• Complete picture of Q1 tax data per state per store
• Data quality issues documented
• QuickBooks totals available for cross-reference
• Any data gaps identified before building the report

[STOP — Ready for UAT: Phase 1] Cole reviews: audit report. Do the state totals look roughly right? Any states that seem missing? Any data quality concerns to fix before proceeding?

Phase 2: Build State-Level Tax Report

DISPATCH tasks:

1. Write scripts/sales_tax_report.py with: - Date range parameter (default: previous quarter) - Per-store breakdown - State-level aggregation: state | taxable_sales | tax_collected | order_count - Nexus states summary (states where tax was collected) - Combined totals across all stores + per-store subtotals
2. Cross-reference each state total against QuickBooks via MCP
3. Flag any discrepancy >$10 between Shopify and QuickBooks
4. Generate two output formats: - Google Sheet (for Martin to work in): one tab per store + combined tab - PDF summary (for filing reference): clean formatted table
5. Include metadata: report generated date, date range, data source, version

Acceptance Criteria:

• Report runs without errors for Q1 2026
• All 3 stores represented
• State totals reconcile within $10 of QuickBooks
• Both output formats produced
• Discrepancies flagged clearly

[STOP — Ready for UAT: Phase 2] Cole reviews: open the Google Sheet. Do the state totals look correct? Do they reconcile with what you expect? Any states that seem wrong?

Phase 3: Polish + Second-Opinion Pass

DISPATCH tasks:

1. Run the second-opinion skill on the report script and output
2. Implement any critical feedback from second-opinion
3. Polish the Google Sheet: - Professional formatting (not raw data dump) - Summary tab with key numbers (total tax collected, total taxable sales, # of nexus states) - Color coding: states with high tax liability highlighted - Column: "Filing Status" (blank — Martin fills this in) - Column: "Notes" (blank — Martin fills this in)
4. Add a "How to Read This Report" tab with brief instructions for Martin
5. Test edge cases: months with $0 tax, orders with missing state, refunds

Acceptance Criteria:

• Second-opinion review completed
• Sheet is clean, professional, ready to hand to a CPA
• Summary tab gives instant overview
• Refunds handled correctly (negative tax)

[STOP — Ready for UAT: Phase 3 — Final] Cole reviews: would you hand this to Martin right now? If yes, approve. If not, note what needs changing.

Phase 4: Automate for Future Quarters

DISPATCH tasks:

1. Add CLI arguments: --quarter Q1 --year 2026 (and --month for monthly runs)
2. Add to LaunchAgent or cron: run on the 5th of each month for prior month
3. Auto-save to ~/ai-projects/mission-control/reports/sales-tax-YYYY-QX.xlsx
4. Slack notification when report is ready: "Q1 2026 sales tax report ready for review"
5. Document: docs/sales-tax-report-guide.md

Acceptance Criteria:

• Quarterly and monthly modes both work
• Auto-save working
• Slack alert fires
• Guide written

[STOP — Ready for UAT: Phase 4] Cole reviews: run the script for Q4 2025 as a historical test. Does it produce correct output?

Execution Log

(Mission Control updates this as phases complete)