legislature-qa-database
Query and analyze the Legislature (M2) QA database using PowerShell functions. Use when working with committees, committee meetings, bills, legislators, or any Legislature/M2 application data in the QA environment.
When & Why to Use This Skill
The Legislature (M2) QA Database skill enables seamless interaction with legislative data environments using specialized PowerShell functions. It allows users to perform secure SQL queries and data analysis on committees, bills, and legislator records within QA, DEMO, and UAT environments. By providing a structured interface for the M2 database, it streamlines the development and testing workflow for legislative applications while ensuring data safety through restricted command execution.
Use Cases
- Retrieving and verifying active committee lists, meeting agendas, and membership records during the software testing lifecycle.
- Inserting mock legislative data into the QA environment to simulate real-world scenarios for application debugging.
- Analyzing complex relationships between legislative entities like CommitteeMeetingAttendanceRecord and CommitteeRole for data validation.
- Comparing data consistency across different staging environments such as M2-QA, M2-DEMO, and M2-UAT using standardized PowerShell commands.
| name | legislature-qa-database |
|---|---|
| description | Query and analyze the Legislature (M2) QA database using PowerShell functions. Use when working with committees, committee meetings, bills, legislators, or any Legislature/M2 application data in the QA environment. |
| author | michigan-senate |
| version | "1.0" |
| environments | "M2-QA, M2-DEMO, M2-UAT, M2-Local" |
Legislature (M2) QA Database Skill
This skill provides access to the Legislature database (also called M2) in the QA environment using PowerShell functions.
Available Functions
Query Data (SELECT)
Use Invoke-EvaSql with M2 environments for SELECT queries:
Invoke-EvaSql -Query "SELECT * FROM Committee" -Environment M2-QA
Security Note: Invoke-EvaSql is restricted to SELECT queries only. UPDATE, DELETE, DROP, and other dangerous operations are blocked for safety.
Insert Data (INSERT)
Use Invoke-EvaSqlNonQuery for inserting new records:
Invoke-EvaSqlNonQuery -Query "INSERT INTO Committee (CommitteeName, SessionID) VALUES ('Test Committee', 16)" -Environment M2-QA
Security Note: Invoke-EvaSqlNonQuery is restricted to INSERT queries only. UPDATE and DELETE operations are blocked to prevent accidental data modification.
Environment Options
M2-QA- Legislature database on tvmwsqls01 (most common)M2-DEMO- Legislature database on MLSQL-QA.lsb.legislature.mi.govM2-UAT- Legislature database on MLSQL-SUP.lsb.legislature.mi.govM2-Local- Local development Legislature database
Common Tables
See DATABASE-SCHEMA.md for complete schema information.
Core Tables
Committee- Committee definitionsCommitteeMeeting- Committee meeting recordsCommitteeMember- Committee membershipCommitteeMeetingAgendaItem- Agenda itemsCommitteeMeetingAttendanceRecord- Attendance trackingCommitteeRole- Committee role definitionsCommitteeType- Committee type classifications
Usage Examples
See COMMON-QUERIES.md for frequently used queries.
Get Active Committees
Invoke-EvaSql -Query "SELECT CommitteeID, CommitteeName, SessionID FROM Committee WHERE IsActive = 1" -Environment M2-QA
Get Latest Committee
Invoke-EvaSql -Query "SELECT TOP 1 * FROM Committee ORDER BY CommitteeID DESC" -Environment M2-QA
Find Committee by Name
Invoke-EvaSql -Query "SELECT * FROM Committee WHERE CommitteeName LIKE '%Finance%'" -Environment M2-QA
Best Practices
- Use M2-QA environment - This is the QA environment for Legislature data
- Committee vs Committees - Table is singular "Committee" not "Committees"
- SessionID is important - Many tables link to SessionID (16 = current)
- ChamberID - 1 = House, 2 = Senate
- Check IsActive - Many tables have IsActive flag for filtering
Connection Details
Connection strings are managed in the EvaSql.psm1 module. See CONNECTION-STRINGS.md for details.
Key Differences from Eva Database
- Legislature database has Committee (singular) not "Committees"
- Uses CommitteeID as primary key (not just "Id")
- Has more normalized structure with lookup tables
- Contains LSB (Legislative Service Bureau) integration data
- SessionID and ChamberID are critical foreign keys
Troubleshooting
Error: "Invalid object name 'Committees'"
- Table name is singular: use
CommitteenotCommittees
Error: "Invalid column name 'Name'"
- Column is
CommitteeNamenotName
Error: "Invalid column name 'CreatedDate'"
- Column is
DateAddednotCreatedDate