cbapp API Audit Report¶

Date: 2025-12-05 Auditor: Claude Code

Executive Summary¶

The cbapp API has 10 route modules with approximately 60+ endpoints. Testing revealed a mix of fully functional, partially working, and broken components. The core person/contact management works well, but several advanced features (segments, i360, chat) have missing infrastructure.

Test Coverage Status¶

Current Status: NO TESTS EXIST - No tests/ directory found - No pytest files found - Recommendation: Create test suite (see "Next Steps" below)

Endpoint Status by Module¶

✅ WORKING (Core Functionality)¶

1. Health & Root (`/api`)¶

Endpoint	Status	Notes
`GET /api`	✅ Working	Returns app info
`GET /api/health`	✅ Working	Returns `{"status": "healthy"}`
`GET /api/protected`	✅ Working	Requires auth

2. Authentication (`/api/auth`)¶

Endpoint	Status	Notes
`POST /token`	✅ Working	JWT token generation
`GET /me`	✅ Working	Returns current user

3. Users (`/api/users`) - Admin Only¶

Endpoint	Status	Notes
`GET /`	✅ Working	List users with filtering
`POST /`	✅ Working	Create user
`GET /{id}`	✅ Working	Get user by ID
`PUT /{id}`	✅ Working	Update user
`DELETE /{id}`	✅ Working	Delete user
`POST /{id}/password`	✅ Working	Change password
`POST /{id}/toggle-active`	✅ Working	Toggle active status

4. Persons (`/api/persons`)¶

Endpoint	Status	Notes
`GET /`	⚠️ Slow	Hangs on large datasets (81K records)
`POST /`	✅ Working	Create person
`GET /{id}`	✅ Working	Get person by ID
`PUT /{id}`	✅ Working	Update person
`DELETE /{id}`	✅ Working	Delete person
`POST /batch`	✅ Working	Batch import (JWT or API key)
`POST /bulk-import`	✅ Working	Direct DuckDB CSV import
`POST /search`	✅ Working	Full-text search
`POST /semantic-search`	⚠️ Depends	Requires Ollama for embeddings

5. Tags (`/api/tags`)¶

Endpoint	Status	Notes
`GET /`	✅ Working	List tags with filtering
`GET /search`	✅ Working	Search tags by name (autocomplete)
`GET /hierarchy`	✅ Working	Hierarchical view
`POST /`	✅ Working	Create tag
`GET /{id}`	✅ Working	Get tag by ID
`PUT /{id}`	✅ Working	Update tag
`DELETE /{id}`	✅ Working	Delete tag
`GET /for/{type}/{id}`	✅ Working	Get tags for any record
`POST /for/{type}/{id}`	✅ Working	Add tags to any record
`DELETE /for/{type}/{id}/{tag_id}`	✅ Working	Remove tag from record
`GET /{tag_id}/records`	✅ Working	Get records with tag
`GET /stats/usage`	✅ Working	Tag usage statistics

6. Events (`/api/events`)¶

Endpoint	Status	Notes
`GET /`	✅ Working	List events (0 in DB)
`POST /`	✅ Working	Create event
`GET /{id}`	✅ Working	Get event by ID
`PUT /{id}`	✅ Working	Update event
`DELETE /{id}`	✅ Working	Delete event
`POST /{id}/registrations`	✅ Working	Register person
`PUT /{id}/registrations/{rid}`	✅ Working	Update registration
`DELETE /{id}/registrations/{rid}`	✅ Working	Cancel registration

7. Embeddings (`/api/embeddings`)¶

Endpoint	Status	Notes
`GET /stats`	✅ Working	Returns cache/Ollama status
`GET /models`	⚠️ Depends	Requires Ollama
`POST /embed`	⚠️ Depends	Requires Ollama
`POST /embed/batch`	⚠️ Depends	Requires Ollama
`GET /cache/stats`	✅ Working	Cache statistics
`POST /cache/clear`	✅ Working	Clear cache

❌ BROKEN (Missing Dependencies)¶

8. Segments (`/api/segments`)¶

Endpoint	Status	Issue
`GET /`	❌ 500 Error	Missing `segment` table
`POST /`	❌ 500 Error	Missing `segment` table
`GET /{id}`	❌ 500 Error	Missing `segment` table
`PUT /{id}`	❌ 500 Error	Missing `segment` table
`DELETE /{id}`	❌ 500 Error	Missing `segment` table
`POST /{id}/execute`	❌ 500 Error	Missing `segment` table
`GET /{id}/persons`	❌ 500 Error	Missing `segment_person` table

Required Tables (not in schema): - segment - segment_person - segment_execution_log

9. i360 (`/api/i360`)¶

Endpoint	Status	Issue
`GET /search`	❌ 503 Error	Missing `i360.db` database
`GET /filters`	❌ 503 Error	Missing `i360.db` database
`GET /voter/{svid}`	❌ 503 Error	Missing `i360.db` database
`GET /stats`	❌ 503 Error	Missing `i360.db` database

Required: Separate db/i360.db with i360_voters table

10. OSM Auth (`/api/auth/osm`)¶

Endpoint	Status	Issue
`GET /login`	❌ 500 Error	Missing env vars
`GET /callback`	❌ 500 Error	Missing env vars
`POST /link`	❌ 500 Error	Missing env vars
`GET /link-callback`	❌ 500 Error	Missing env vars
`DELETE /unlink`	⚠️ Partial	Works but `osm_id` column missing
`GET /status`	⚠️ Partial	Works but `osm_id` column missing

Required: - Environment variables: OSM_CLIENT_ID, OSM_CLIENT_SECRET, OSM_REDIRECT_URI - User table columns: osm_id, osm_access_token

11. Chat (`/api/chat`)¶

Endpoint	Status	Issue
`GET /health`	✅ Working	Returns Ollama status
`POST /query`	⚠️ Error	Ollama connection issues
`GET /workflows`	⚠️ Depends	Requires CampaignBrain module
`POST /reload`	⚠️ Depends	Requires CampaignBrain module

Required: - Working Ollama installation - CampaignBrain Python module

Database Schema Analysis¶

Tables Present (16)¶

Table	Rows	Status
person	81,232	✅ Active use
person_custom_field	2,030,893	✅ Active use
custom_field	32	✅ Active use
tag	3	✅ Working
record_tag	-	✅ Working (universal)
person_tag	3	⚠️ Legacy (synced with record_tag)
user	3	✅ Working
user_organization	0	✅ Ready
organization	0	✅ Ready
event	0	✅ Ready
event_registration	0	✅ Ready
communication	0	✅ Ready
whip_status	1	✅ Ready
template	0	✅ Ready
file	0	✅ Ready
goal	0	✅ Ready

Tables Missing (Required by API)¶

Table	Required By	Priority
`segment`	/api/segments	HIGH
`segment_person`	/api/segments	HIGH
`segment_execution_log`	/api/segments	MEDIUM
`i360_voters` (separate DB)	/api/i360	MEDIUM

Columns Missing¶

Table	Column	Required By
`user`	`osm_id`	/api/auth/osm
`user`	`osm_access_token`	/api/auth/osm

WebSocket Status¶

Endpoint	Status	Notes
`WS /api/ws/audience-search`	✅ Defined	Basic echo implementation

Performance Issues¶

GET /api/persons/ - Times out on 81K records
Needs pagination enforcement
Default limit should be lower
Bulk operations - Consider streaming for large datasets

Security Observations¶

✅ All sensitive endpoints require authentication
✅ API key auth works for service-to-service
✅ Admin-only routes protected by role check
⚠️ CORS allows "*" in development mode
✅ SQL injection prevented via parameterized queries
✅ Bulk import restricts file paths to /tmp/imports/

Next Steps (Priority Order)¶

HIGH Priority¶

Add segment tables to schema - Blocking segments feature
Fix GET /api/persons/ timeout - Add pagination limit
Create pytest test suite - No tests currently exist

MEDIUM Priority¶

Add OSM columns to user table - If OSM auth is needed
Document i360 database setup - Separate database requirement
Set up Ollama - For embeddings/chat features

LOW Priority¶

Configure OSM OAuth - Environmental setup
Implement CampaignBrain - Advanced NLP features
Add WebSocket authentication - Currently open

Recommended Test Suite Structure¶

tests/
├── conftest.py              # Test fixtures, DB setup
├── test_auth.py             # Authentication tests
├── test_users.py            # User CRUD tests
├── test_persons.py          # Person CRUD + batch import
├── test_tags.py             # Tag hierarchy tests
├── test_events.py           # Event + registration tests
├── test_segments.py         # Segment tests (after schema fix)
├── test_api_key.py          # API key authentication
└── test_bulk_import.py      # Bulk import performance

Files Analyzed¶

src/api/main.py - Main app, router registration
src/api/auth.py - Authentication logic
src/api/routes/auth.py - Auth endpoints
src/api/routes/users.py - User management
src/api/routes/persons.py - Contact management
src/api/routes/tags.py - Tag management
src/api/routes/events.py - Event management
src/api/routes/segments.py - Segment management (broken)
src/api/routes/i360.py - Voter database (needs DB)
src/api/routes/chat.py - NLP chat (needs Ollama)
src/api/routes/embeddings.py - Vector embeddings
src/api/routes/osm_auth.py - OSM OAuth (needs config)
scripts/create_schema.py - Database schema