Bear Query

Bear EHR Database Query Skill

You have access to the Bear EHR MariaDB database via MCP tools (mcp__bear-db__execute_query, mcp__bear-db__get_table_info, mcp__bear-db__get_schema).

Before Writing Any Query

1. Read the semantic layer docs in /home/andres/code/claude/semantic/ to understand the schema
2. Start with 00_query_guide.md for query patterns, naming conventions, and caveats
3. Read the relevant domain file (01-15) for the specific tables involved

Domain Files Quick Reference

Critical Rules

MariaDB Syntax (NOT PostgreSQL)

• Use CASE WHEN not FILTER(WHERE)
• Use CURDATE() not CURRENT_DATE
• Use TIMESTAMPDIFF() for date math
• Use DATE_FORMAT() for date formatting
• tinyint(1) is boolean — use = 1 / = 0, NOT IS TRUE / IS FALSE

Soft Deletes

• Most tables use deleted_at IS NULL to filter active records
• Always include this filter unless the user wants historical data
• Some tables (like cssrs_suicides, suicidal_ideations, client_high_risks) do NOT have deleted_at — check with get_table_info first if unsure

Names Are in a Separate Table

• Client names: JOIN names n ON n.person_id = p.id AND n.deleted_at IS NULL
• Staff names: JOIN people p ON p.identifiable_id = u.id AND p.identifiable_type = 'User' then JOIN names n ON n.person_id = p.id
• For latest name: use MAX(n.id) subquery or ORDER BY n.id DESC LIMIT 1

STI Tables

• people.type = 'Client' for patients
• companies.type = 'Payor' for insurance companies
• Hidden/merged clients: filter with (p.hidden IS NULL OR p.hidden = 0)

Money Columns

• ALL financial amounts are *_cents columns (integers)
• Always divide by 100.0 for dollar display: amount_cents / 100.0 AS amount_dollars

Appointment Status (Timestamp-Based)

• No status column — derive from timestamps:
  • Completed: checked_out_at IS NOT NULL
  • No-show: noshow_at IS NOT NULL
  • Cancelled: cancelled_at IS NOT NULL
  • Checked in: checked_in_at IS NOT NULL AND checked_out_at IS NULL
  • Valid: deleted_by IS NULL AND cancelled_at IS NULL AND noshow_at IS NULL

Insurance

• Self-pay payor: companies.id = 1 (scope self_pay / not_self_pay in Rails)
• Priority values: first, second, third, fourth, fifth, self
• Cards (images): pictures table with picturable_type = 'InsurancePolicy'

Vitals

• BP columns: bp_right_arm_sys, bp_right_arm_dia, bp_left_arm_sys, bp_left_artm_dia (note typo on left arm diastolic)
• High BP threshold: systolic >= 130 OR diastolic >= 80

Suicide Risk (C-SSRS)

• Main table: cssrs_suicides (no deleted_at column)
• Ideations: suicidal_ideations joined via cssrs_suicide_id
• Risk assessments: risk_assessments (has deleted_at)

Query Workflow

1. Read the relevant semantic layer file(s)
2. If unsure about a column, use mcp__bear-db__get_table_info to verify
3. Write the query using MariaDB syntax
4. Present results in a clear table format
5. Highlight key insights and anomalies
6. When results are large, get summary counts first, then drill down

Responding

• Always show the data in a formatted markdown table
• Add key takeaways / insights after the data
• Flag data quality issues (nulls, suspicious values, inconsistencies)
• Offer to drill deeper or pivot the analysis
• When providing ActiveRecord equivalents, give them as one-liners for easy console paste

$ARGUMENTS