CREATE INDEX idx_precedent_tenant ON precedent_objects(tenant_id);
CREATE INDEX idx_appeal_tenant ON appeals(tenant_id, status);

Tier 2: Exact Lookups (B-tree)

CREATE INDEX idx_table_field ON table_name(tenant_id, field) WHERE status != 'Archived';

Tier 3: Timeline/Range Queries (DESC Indexes)

CREATE INDEX idx_table_field_desc ON table_name(tenant_id, field DESC);

-- Find all precedents not reviewed in 1 year
SELECT precedent_name, last_validated_date
FROM precedent_objects
WHERE tenant_id = $1 AND last_validated_date < CURRENT_DATE - INTERVAL '1 year'
ORDER BY last_validated_date ASC
LIMIT 20;

Tier 4: JSONB Searches (GIN Indexes)

CREATE INDEX idx_table_field_gin ON table_name USING GIN(jsonb_column);

-- Find all plans requiring prior auth for IOP
SELECT payer_name, plan_year
FROM payer_plans
WHERE coverage_data -> 'behavioral_health' -> 'iop' ->> 'requires_prior_auth' = 'true';

-- Find precedents with evidence_score="Strong" in evidence_kit
SELECT precedent_name
FROM precedent_objects
WHERE evidence_kit @> '{"strength": "strong"}';

Composite Index Strategy

-- Multi-field searches common enough to warrant composite index
CREATE INDEX idx_precedent_payer_denial_loc ON precedent_objects(
  tenant_id,      -- Always first (tenant isolation)
  payer,          -- Filter 1
  denial_type,    -- Filter 2
  loc             -- Filter 3
);
-- Query: WHERE tenant_id = $1 AND payer = $2 AND denial_type = $3 AND loc = $4

-- Time-range queries
CREATE INDEX idx_appeal_tenant_created_desc ON appeals(
  tenant_id,
  created_at DESC  -- Allows "ORDER BY created_at DESC" without post-sort
);

-- Contribution scoring lookups
CREATE INDEX idx_contribution_tenant_score ON contribution_scores(
  tenant_id,
  total_score DESC  -- Rank contributors
);

Index Coverage for Common Queries

Query: Browse Library (Payer/Denial Type/LOC Filter)

SELECT  FROM precedent_objects
WHERE tenant_id = $1 AND payer = $2 AND denial_type = $3 AND loc = $4
ORDER BY created_at DESC
LIMIT 25;

Query: Compliance Audit (SUD Data Access)

SELECT user_id, event_type, timestamp
FROM precedent_audit_log
WHERE tenant_id = $1 AND precedent_id IN (
  SELECT id FROM precedent_objects WHERE data_origin_type = 'SUD'
)
ORDER BY timestamp DESC
LIMIT 100;

Query: Credit Balance (Sum All Transactions)

SELECT SUM(amount) AS balance
FROM credit_ledger
WHERE tenant_id = $1 AND status = 'posted';

Query: Timeline/Versioning (Get All Versions)

SELECT version_number, changed_at, changed_by, change_reason
FROM precedent_version_history
WHERE precedent_id = $1 AND tenant_id = $2
ORDER BY version_number DESC;

Performance Expectations

Index Monitoring

Unused Indexes (Remove to Save Storage)

-- Find indexes not used in past 30 days
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Slow Queries (Identify Missing Indexes)

-- Enable logging of slow queries (>1 second)
SET log_min_duration_statement = 1000;

-- Then check logs for queries without USING INDEX in EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT ...;

Index Size

-- Find largest indexes (potential bloat)
SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

Maintenance

Reindex (Annual or After Large Deletes)

-- Rebuild all indexes on a table
REINDEX TABLE precedent_objects;

-- Rebuild all indexes (system-wide)
REINDEX DATABASE stratum;

Analyze (After Major Data Changes)

-- Update statistics so Postgres query planner knows index selectivity
ANALYZE precedent_objects;

Bloat Detection

-- Monitor for index bloat (pages wasted by updates/deletes)
SELECT schemaname, tablename, indexname, idx_blks_hit, idx_blks_read
FROM pg_stat_user_indexes
WHERE idx_blks_read > idx_blks_hit  10  -- Many disk reads = potential bloat
ORDER BY idx_blks_read DESC;

-- Create partitioned table
CREATE TABLE precedent_objects_partitioned (
  ...
) PARTITION BY LIST (tenant_id);

-- Create partition per tenant
CREATE TABLE precedent_objects_tenant_a PARTITION OF precedent_objects_partitioned
  FOR VALUES IN ('tenant-a-uuid');