About

Legislative Opportunity Detector Complete Developer Specification EXECUTIVE SUMMARY What This Does: Automatically scans new legislation from 7 states (CA, NY, IL, WA, NJ, TX, FL) daily to identify laws that create mandatory business opportunities—specifically, laws that force companies to purchase compliance systems, software, hardware, or services. How It Works: • Pulls new/updated bills from LegiScan API every day • Sends full bill text to OpenAI GPT-4.1-mini for analysis • AI scores each bill 0-100 on likelihood it creates a business opportunity • Results populate a Google Sheet, sorted newest-first • Bills scoring 70+ are flagged as "YES" opportunities with detailed summaries What I Get: A Google Sheet with columns: date processed, state, bill number, confidence score (0-100), YES/NO opportunity flag, summary of what businesses must buy/implement, affected industry, effective date, and more. High-scoring opportunities (70+) are highlighted in green. Confidence Score Explained: • 90-100: Law explicitly mandates specific purchases with "shall/must" language • 70-89: Strong regulatory requirements that will require vendor solutions • 50-69: Possible opportunity but mandate unclear • Below 50: No clear business opportunity Output: One Google Sheet updated daily with newest bills on top, filterable by state, confidence score, industry, etc.   1. EXECUTION FREQUENCY Run once daily at 2 AM EST. Optional second run at 2 PM EST during peak legislative sessions (Jan-May). 2. DATA SOURCE (LEGISCAN API) API Documentation: https://legiscan.com/legiscan Required Endpoints: • getSearchRaw or getMasterList: Get bills by state with date filters • getBillText: Get full bill text by bill ID Target States: CA, NY, IL, WA, NJ, TX, FL API Key Requirements: • Minimum tier: Paid plan (free tier's 5k requests/day insufficient) • Expected usage: ~300-500 requests/day (7 states × ~20-50 bills × 1.5 for text fetches) Rate Limiting: • LegiScan enforces rate limits per tier • Add 2-second delay between API calls • Implement exponential backoff on 429 errors (retry 3x max) 3. DATA PROCESSING FLOW For each bill: • Extract: state, bill_id, bill_number, title, date • Fetch full bill text via getBillText • Decode base64 if doc.mime = application/pdf or encoded format • Handle text extraction errors (some bills have no text yet) Send to AI: • State • Bill number • Bill title • Full bill text (truncated to 120k tokens if exceeds limit) • Date introduced/updated 4. MODEL & CONFIGURATION Primary Model: OpenAI GPT-4.1-mini (gpt-4o-mini-2024-07-18) Fallback Model: GPT-4.1 (gpt-4-turbo-2024-04-09) if text 128k tokens Settings: • Temperature: 0.1 (deterministic) • Max tokens: 2000 (response) • Response format: JSON object • Timeout: 60 seconds 5. CONFIDENCE SCORE DEFINITION Confidence Score Scale (0-100): 90-100 (VERY HIGH): Law explicitly mandates businesses purchase or implement specific systems, devices, or services. Language includes "shall implement," "must obtain," "required to use," with clear compliance deadlines and penalties for non-compliance. 70-89 (HIGH): Law creates strong regulatory requirements that will require businesses to purchase compliance solutions, reporting systems, or technology. Intent is clear but specific implementation details may be left to regulatory agencies. 50-69 (MEDIUM): Law suggests new compliance burdens that likely require vendor solutions, but mandate is not explicit or may have exemptions. Could create market opportunity but uncertain scope. 30-49 (LOW): Law mentions compliance or reporting but may be achievable through existing systems or manual processes. Business opportunity unclear or minimal. 0-29 (VERY LOW): No clear business opportunity. Law is policy-focused, aspirational, or does not create actionable compliance requirements for businesses. Opportunity Summary Guidelines: • Score 70: Must include specific summary of what businesses are required to purchase/implement • Score 70: Leave summary blank   6. AI PROMPT (USE VERBATIM) You are evaluating legislation to determine whether it creates a mandatory business opportunity. Use the bill text provided. A mandatory business opportunity means: A new law forces businesses, facilities, or agencies to buy hardware, software, compliance systems, reporting tools, or services. Example categories: worker-safety devices, electronic visit verification, labor tracking, environmental reporting, facility audits, required sensors/devices, training platforms, payroll changes, cannabis tracking, emissions reporting, accessibility, cybersecurity standards, warehouse quota tracking, food traceability, building automation. Confidence Score Scale (0-100): 90-100 (VERY HIGH): Law explicitly mandates purchase/implementation of specific systems with "shall," "must," "required to" language and compliance deadlines. 70-89 (HIGH): Law creates strong regulatory requirements that will require businesses to purchase compliance solutions. Intent is clear. 50-69 (MEDIUM): Law suggests new compliance burdens likely requiring vendor solutions, but mandate not explicit or has exemptions. 30-49 (LOW): Compliance mentioned but may be achievable through existing systems. Business opportunity unclear. 0-29 (VERY LOW): No clear business opportunity. Policy-focused or aspirational without actionable requirements. Output valid JSON only: "confidence_score": 0, "has_opportunity": false, "opportunity_summary": "", "industry": null, "effective_date": null, "requires_technology": false, "estimated_market_size": null Rules: • If confidence_score 70: set has_opportunity = false and opportunity_summary = "" • If confidence_score 70: set has_opportunity = true and write 2-3 sentence summary describing what businesses must purchase/implement and why • industry: specific industry affected (e.g. "janitorial services", "healthcare facilities") or null • effective_date: extract from bill text or null • requires_technology: true if solution likely needs software/hardware • estimated_market_size: "small" ($10M affected businesses), "medium" ($10-100M), "large" ($100M), or null Confidence Threshold for Flagging: 70   7. GOOGLE SHEETS STORAGE Sheet Name: "Legislative Opportunities" Column Structure (A-N): A - Processed Date: Timestamp when bill was processed (newest first) B - State: Two-letter state code C - Bill Number: Official bill number (e.g., "SB-123") D - Bill Title: Official title of bill E - Bill Date: Date bill was introduced/updated F - Confidence Score: 0-100 score G - Has Opportunity: "YES" or "NO" H - Opportunity Summary: 2-3 sentence description of mandate I - Industry: Affected industry J - Effective Date: When law takes effect K - Requires Technology: "YES" or "NO" L - Market Size: "Small", "Medium", "Large", or blank M - LegiScan URL: Link to bill on LegiScan N - Processing Status: "Success", "No Text", or "Error" Formatting Rules: • Header row (row 1): Bold, frozen, background color #4285F4 (blue) • Confidence Score column: Conditional formatting • - 90-100: Dark green background • - 70-89: Light green background • - 50-69: Yellow background • - 0-49: No formatting • Has Opportunity = "YES": Entire row light green background (#D9EAD3) • Sort order: Column A (Processed Date) descending (newest on top) Sheet Configuration: • Auto-resize columns to fit content • Filter views enabled on all columns • Data validation on specific columns: • - Confidence Score: 0-100 only • - Has Opportunity: "YES" or "NO" only • - Market Size: "Small", "Medium", "Large", or blank   8. N8N WORKFLOW ARCHITECTURE Nodes & Flow: 1. Schedule Trigger (Cron: 0 2 * * *) 2. Set Variables (API keys, state list, date range, Sheet ID) 3. Loop Over States (Split In Batches) 4. HTTP: LegiScan getMasterList (get bills for state) 5. Wait 2 seconds (rate limit protection) 6. Function: Parse bill list JSON 7. Loop Over Bills (Split In Batches: 10 per batch) 8. HTTP: LegiScan getBillText (get full text) 9. Wait 2 seconds 10. Function: Decode base64 if needed, truncate to 120k tokens 11. IF: Check if text exists 12. OpenAI: Run prompt with structured output 13. Function: Validate JSON response & format for Sheets 14. Google Sheets: Append row to sheet 15. Google Sheets: Append error row (Processing Status='No Text') 16. Google Sheets: Sort by Processed Date descending 17. Error Handler: Catch failures → append to error log sheet Duplicate Handling: • n8n will check if bill already exists in sheet before appending • Compare State + Bill Number combination • If exists: Skip (do not append duplicate) • If exists but updated: Update existing row with new processed date Error Handling: Create second sheet: "Processing Errors" Columns: Processed Date, State, Bill ID, Error Type, Error Message Error scenarios: • API timeout: Retry 3x with exponential backoff • Rate limit (429): Wait 60s, retry • No bill text: Append to main sheet with Status='No Text' • OpenAI error: Append to errors sheet • Invalid JSON: Append to errors sheet Retry Logic: • HTTP requests: 3 retries, 5-second delay • OpenAI requests: 2 retries, 10-second delay • Google Sheets API: 2 retries, 3-second delay   9. ADDITIONAL REQUIREMENTS LegiScan Date Filtering: • Query only bills from last 7 days to reduce API calls • Parameter: &year=2025&from=YYYY-MM-DD Text Truncation: • If bill text 120,000 tokens: truncate to first 100k tokens • Add note in Processing Status: "Success (Truncated)" Daily Summary (Optional): After workflow completes, send summary email/Slack: • Total bills processed • High-confidence opportunities (score ≥ 70) • Errors encountered • Link to Google Sheet Google Sheets Maintenance: • Sheet will grow by ~300 rows/day = ~110k rows/year • Google Sheets limit: 10 million cells • Recommend archiving to separate sheet quarterly (optional)   10. DEVELOPER DELIVERABLES • n8n workflow JSON (export ready to import) • Google Sheet template with pre-formatted headers, conditional formatting rules, filter views, and "Processing Errors" tab • Setup documentation: LegiScan API key configuration, OpenAI API key configuration, Google Sheets connection (OAuth or Service Account), Sheet ID configuration in workflow, Cron schedule setup • Testing checklist: Run against single state first, verify duplicate detection works, test error handling (simulate API failures), validate OpenAI JSON parsing, check sorting maintains newest-first order • Video walkthrough (optional): How to import workflow, how to configure API keys, how to read the sheet, how to filter/sort results 11. QUICK START CHECKLIST • ☐ LegiScan API key (paid tier) • ☐ OpenAI API key with billing enabled • ☐ Google account with Sheets access • ☐ Create Google Sheet from template • ☐ Copy Sheet ID from URL • ☐ n8n instance running (cloud or self-hosted) • ☐ Import workflow JSON into n8n • ☐ Configure API credentials in n8n • ☐ Add Google Sheets OAuth connection • ☐ Configure Sheet ID in workflow variables • ☐ Test with single state (CA) first • ☐ Verify row appears in sheet correctly • ☐ Enable daily cron trigger • ☐ Monitor first 3 days for issues 12. EXAMPLE OUTPUT ROWS High Confidence Example (Score: 92): • State: CA • Bill Number: SB-789 • Confidence Score: 92 • Has Opportunity: YES • Opportunity Summary: "Requires all commercial buildings over 50,000 sq ft to install automated energy monitoring systems with real-time reporting to state registry by January 2026. Building owners must purchase certified hardware and software from approved vendors. Non-compliance results in daily fines." • Industry: Commercial Real Estate / Facility Management • Requires Technology: YES • Market Size: Large Low Confidence Example (Score: 35): • State: TX • Bill Number: HB-456 • Confidence Score: 35 • Has Opportunity: NO • Opportunity Summary: (blank) • Industry: (blank) • Requires Technology: NO • Market Size: (blank) This specification is complete and ready for development. The Google Sheets approach provides easy visibility, filtering, and sharing while maintaining the newest-first sort order you requested.