wellwalla_labs Schema
Primary database for lab test catalog and pricing.
Core Tables
| Table |
Purpose |
Key Columns |
lab_tests |
Canonical test catalog |
id, name, description, category, specimen_type |
lab_vendors |
Lab networks (Labcorp, Quest, etc.) |
id, name, code, website, is_active |
lab_test_codes |
Vendor-specific test codes |
id, lab_test_id, vendor_id, vendor_code, vendor_name |
lab_test_pricing |
Wholesale and retail prices |
id, lab_test_code_id, wholesale_price, retail_price, effective_date |
competitor_pricing |
Competitor price tracking |
id, lab_test_id, competitor_name, competitor_price, url |
Key Views
-- Cross-reference Labcorp ↔ Quest codes
CREATE VIEW code_crossref AS
SELECT
lt.id as test_id,
lt.name as test_name,
MAX(CASE WHEN v.code = 'LABCORP' THEN c.vendor_code END) as labcorp_code,
MAX(CASE WHEN v.code = 'QUEST' THEN c.vendor_code END) as quest_code,
MAX(CASE WHEN v.code = 'LABCORP' THEN p.wholesale_price END) as labcorp_wholesale
FROM lab_tests lt
JOIN lab_test_codes c ON lt.id = c.lab_test_id
JOIN lab_vendors v ON c.vendor_id = v.id
LEFT JOIN lab_test_pricing p ON c.id = p.lab_test_code_id
GROUP BY lt.id, lt.name;
Key Functions
-- Get price by test code
SELECT * FROM get_test_price('706994');
-- Calculate margin at a given retail price
SELECT * FROM calculate_margin('706994', 67.99);
-- Get cross-reference by either code
SELECT * FROM get_code_crossref('31789'); -- Quest code → shows Labcorp equivalent
Connection Details
Local PostgreSQL (Development)
Host: localhost
Port: 5432
Path: /Volumes/+1.512.666.4847.../postgresql-data/postgresql@17
psql: /opt/homebrew/Cellar/postgresql@17/17.7_1/bin/psql
# Connect
psql -d wellwalla_labs
psql -d wellwalla_corp
AWS MySQL (Production)
Host: (AWS EC2 internal)
Port: 3306
Database: wellwalla_wp
User: (see 1Password)
Neon (Planned Production)
Provider: Neon.tech
Plan: Scale (HIPAA BAA available)
Region: TBD (likely us-east)
Status: Migration pending