Federal Grant Compliance and Expenditure Audit SQL Brief

Role: Principal Public Sector Data Architect specializing in statutory grant accounting and automated compliance audit systems.

Context

• Target Grant Program: {{grant_program_name}}
• Database Engine & Dialect: {{sql_dialect}}
• Audited Fiscal Interval: {{fiscal_period}}
• Statutory Compliance Framework: {{compliance_rules}}
• Flagged Cost Classifications: {{disallowed_cost_codes}}

Task

Generate a comprehensive SQL compliance audit brief that specifies production-ready, auditable SQL queries to isolate unallowable expenditures, quantify funding caps, and construct automated discrepancy views for {{grant_program_name}}.

Method

1. Parse the structural parameters of {{compliance_rules}} and map them against transactional tables in {{sql_dialect}}.
2. Design Common Table Expressions (CTEs) that filter raw expenditure ledger rows strictly for {{fiscal_period}}.
3. Establish conditional logic using CASE statements to flag transactions matching {{disallowed_cost_codes}}.
4. Build cumulative window functions (SUM() OVER (...)) to track running expense totals against statutory program appropriation ceilings.
5. Implement anti-join and existence subqueries to detect missing vendor documentation, unapproved purchase orders, and orphaned accounting entries.
6. Format query outputs into distinct compliance risk tiers with deterministic categorization codes.
7. Structure final validation aggregation views suitable for integration into public reporting dashboards.

Constraints

• MUST produce syntactically valid SQL conforming strictly to {{sql_dialect}} specifications.
• MUST NOT use non-deterministic functions that inhibit query reproducibility during external audits.
• All calculated monetary values MUST round using standard accounting precision rules (two decimal places).
• Inline comments must accompany every CTE explaining the statutory rule being enforced.
• Code must avoid vendor-specific locking hints unless necessary for read-only replica performance.

Output format

Provide a markdown brief containing:

1. Executive Accounting Schema & Assumption Matrix (1 table, max 8 rows)
2. CTE Architecture & Data Lineage Breakdown (bulleted list)
3. Production SQL Query Script (fully commented, syntax-highlighted code block)
4. Discrepancy Reconciliation & Verification Guide (max 300 words)

Self-review

• Confirm every cost code from {{disallowed_cost_codes}} is accounted for in query logic.
• Verify all date logic strictly isolates records to {{fiscal_period}} without off-by-one errors.
• Ensure dialect-specific syntax matches {{sql_dialect}} standards without generic pseudo-SQL.