bear-query

name	bear-query
description	Query the Bear EHR database using the semantic layer. Use when asked about clients, billing, appointments, insurance, clinical data, procedures, scheduling, programs, staff, vitals, diagnoses, revenue, claims, or any data question about the Bear application.
allowed-tools	Read, Grep, Glob

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

#	File	Use When Asking About
00	00_query_guide.md	NL-to-SQL patterns, status derivation, caveats
01	01_people_demographics.md	Clients, demographics, addresses, phones
02	02_programs_enrollment.md	Programs, enrollment, locations
03	03_insurance_coverage.md	Insurance policies, payors, plans, authorizations
04	04_appointments_scheduling.md	Scheduling, calendar, appointment types
05	05_encounters_forms.md	Clinical documentation, encounter forms
06	06_billing_revenue.md	Charges, claims, payments, revenue cycle
07	07_clinical_data.md	Diagnoses, medications, vitals, labs
08	08_users_staff.md	Staff, providers, credentials, roles
09	09_tfc.md	Treatment Foster Care
10	10_otp.md	Opioid Treatment Program
11	11_transportation.md	Client transportation
12	12_crew_groups.md	Group therapy sessions
13	13_bed_management.md	Residential bed tracking
14	14_prescriptions_erx.md	Electronic prescribing
15	15_labs.md	Laboratory orders and results

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