CusipService

A REST API service for loading and querying CUSIP securities data from CUSIP Global Services. The service ingests daily PIP (Pipe-delimited) files and provides queryable REST endpoints for securities data.

Architecture

The service consists of two API planes:

Port	Service	Purpose
8000	FastAPI	Control plane - file loading jobs, health checks
3000	PostgREST	Query plane - read-only REST API for views
8080	Swagger UI	Query plane documentation

Data Flow

PIP Files → FastAPI /jobs/* → PostgreSQL → PostgREST → Consumers

File Types

Suffix	Type	Description
`R.PIP`	issuer	Issuer master data (~400K records)
`E.PIP`	issue	Security/issue data (~10M records)
`A.PIP`	issue_attr	Extended attributes per issue

Quick Start

Prerequisites

Docker and Docker Compose
Python 3.11+ (for local development)
uv package manager

Running with Docker Compose

cd docker

# Start all services
docker-compose up -d

# Check service health
curl http://localhost:8000/health

# View logs
docker-compose logs -f api

Services

Once running, access:

FastAPI docs: http://localhost:8000/docs
PostgREST docs: http://localhost:8080
PostgREST API: http://localhost:3000

API Reference

Control Plane (Port 8000)

All job endpoints require bearer token authentication.

Health Checks

# Health check (includes DB connectivity)
curl http://localhost:8000/health

# Kubernetes probes
curl http://localhost:8000/ready
curl http://localhost:8000/live

Load Jobs

Load files for a specific date (defaults to today):

# Load all file types (issuer → issue → issue_attr)
curl -X POST http://localhost:8000/jobs/load-all \
  -H "Authorization: Bearer ${CUSIP_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"date": "2024-01-15"}'

# Load individual file types
curl -X POST http://localhost:8000/jobs/load-issuer \
  -H "Authorization: Bearer ${CUSIP_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"date": "2024-01-15"}'

curl -X POST http://localhost:8000/jobs/load-issue \
  -H "Authorization: Bearer ${CUSIP_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"date": "2024-01-15"}'

curl -X POST http://localhost:8000/jobs/load-issue-attr \
  -H "Authorization: Bearer ${CUSIP_API_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"date": "2024-01-15"}'

File naming convention: Files must match pattern CED{mm-dd}*.PIP:

CED01-15R.PIP - Issuer file for January 15
CED01-15E.PIP - Issue file for January 15
CED01-15A.PIP - Issue attribute file for January 15

Query Plane (Port 3000)

PostgREST provides automatic REST endpoints for database views.

Available Views

Endpoint	Description
`/v_issuer`	Issuer data with decoded reference values
`/v_issue`	Issue/security data with decoded reference values
`/v_security_summary`	Combined issuer + issue summary

Filtering Examples

# Case-insensitive search
curl "http://localhost:3000/v_issuer?issuer_name=ilike.*APPLE*"

# Exact match
curl "http://localhost:3000/v_issue?cusip=eq.037833100"

# Multiple conditions
curl "http://localhost:3000/v_issue?issue_status=eq.A&security_type=eq.COM"

# Pagination
curl "http://localhost:3000/v_issuer?limit=100&offset=200"

# Select specific columns
curl "http://localhost:3000/v_issuer?select=issuer_id,issuer_name,city,state"

# Order results
curl "http://localhost:3000/v_issuer?order=issuer_name.asc"

Full-Text Search

curl -X POST http://localhost:3000/rpc/search_securities \
  -H "Content-Type: application/json" \
  -d '{"search_query": "KEURIG"}'

PostgREST Operators

Operator	Description	Example
`eq`	Equals	`?status=eq.active`
`neq`	Not equals	`?status=neq.deleted`
`gt`, `gte`	Greater than	`?amount=gt.100`
`lt`, `lte`	Less than	`?amount=lt.1000`
`like`	LIKE (case-sensitive)	`?name=like.Corp`
`ilike`	LIKE (case-insensitive)	`?name=ilike.corp`
`in`	In list	`?status=in.(A,B,C)`
`is`	IS NULL/TRUE/FALSE	`?deleted=is.null`

Local Development

Setup

# Install dependencies
uv sync

# Run the API locally
uv run uvicorn cusipservice.api.main:app --reload

# Or use the CLI
uv run python -m cusipservice

Environment Variables

Database Settings

You can configure database credentials either directly or via AWS Secrets Manager (recommended for production with rotating secrets).

Direct configuration:

Variable	Description	Default
`CUSIP_DB_HOST`	PostgreSQL host	`localhost`
`CUSIP_DB_PORT`	PostgreSQL port	`5432`
`CUSIP_DB_NAME`	Database name	`cusip`
`CUSIP_DB_USER`	Database user	`cusip_app`
`CUSIP_DB_PASSWORD`	Database password	(required)
`CUSIP_DB_SSLMODE`	SSL mode for connection	`prefer`

SSL mode options: disable, allow, prefer, require, verify-ca, verify-full. Use require for AWS RDS.

AWS Secrets Manager (overrides direct settings if set):

Variable	Description	Default
`CUSIP_DB_SECRET_ARN`	ARN of secret containing DB credentials	-
`CUSIP_DB_SECRET_REGION`	AWS region for Secrets Manager	(optional)

When CUSIP_DB_SECRET_ARN is set, the application fetches credentials at startup from Secrets Manager. The secret must be JSON with standard RDS keys: host, port, dbname, username, password.

File Source Settings

Variable	Description	Default
`CUSIP_FILE_SOURCE`	File source: `local` or `s3`	`local`
`CUSIP_FILE_DIR`	Local directory for PIP files	`/data/pip_files`
`CUSIP_S3_BUCKET`	S3 bucket name (required if `s3`)	-
`CUSIP_S3_PREFIX`	S3 prefix/path for PIP files	`pip/`
`CUSIP_S3_REGION`	AWS region for S3 bucket	(optional)

API Settings

Variable	Description	Default
`CUSIP_API_TOKEN`	Bearer token for job endpoints	(required)

Example `.env` for Local Development

# Database
CUSIP_DB_HOST=localhost
CUSIP_DB_PORT=5432
CUSIP_DB_NAME=cusip
CUSIP_DB_USER=postgres
CUSIP_DB_PASSWORD=postgres

# File source (local)
CUSIP_FILE_SOURCE=local
CUSIP_FILE_DIR=./pip_files

# API
CUSIP_API_TOKEN=changeme

Example `.env` for S3 File Source

# Database
CUSIP_DB_HOST=localhost
CUSIP_DB_NAME=cusip
CUSIP_DB_USER=postgres
CUSIP_DB_PASSWORD=postgres

# File source (S3)
CUSIP_FILE_SOURCE=s3
CUSIP_S3_BUCKET=cusip-pip-files-shared
CUSIP_S3_PREFIX=pip/
CUSIP_S3_REGION=us-east-1

# API
CUSIP_API_TOKEN=changeme

Using S3 with AWS SSO (Local Development)

You can develop locally against a local PostgreSQL while reading files from a remote S3 bucket:

# Login to AWS SSO
aws sso login --profile your-profile

# Run with S3 file source
AWS_PROFILE=your-profile \
CUSIP_FILE_SOURCE=s3 \
CUSIP_S3_BUCKET=cusip-pip-files-shared \
CUSIP_DB_HOST=localhost \
uv run uvicorn cusipservice.api.main:app --reload

CLI with S3

The CLI also supports loading from S3:

# Load all files for a date from S3
AWS_PROFILE=your-profile uv run python -m cusipservice \
  --s3-bucket cusip-pip-files-shared \
  --s3-prefix pip/ \
  --date 2024-01-15 \
  --dbname cusip --user cusip_app --password changeme

# Load a specific S3 file
AWS_PROFILE=your-profile uv run python -m cusipservice \
  --s3-bucket cusip-pip-files-shared \
  --s3-key pip/CED01-15R.PIP \
  --type issuer \
  --dbname cusip --user cusip_app --password changeme

See docs/AWS_MULTI_ACCOUNT_SETUP.md for multi-account deployment with cross-account S3 access.

S3 Bucket Organization

When using S3 as the file source, organize files like this:

s3://your-bucket/
└── pip/                    # Matches CUSIP_S3_PREFIX
    ├── CED01-15R.PIP       # Issuer file for Jan 15
    ├── CED01-15E.PIP       # Issue file for Jan 15
    ├── CED01-15A.PIP       # Issue attributes for Jan 15
    ├── CED01-16R.PIP
    ├── CED01-16E.PIP
    └── ...

Upload files:

aws s3 cp /path/to/CED01-15R.PIP s3://your-bucket/pip/
aws s3 cp /path/to/CED01-15E.PIP s3://your-bucket/pip/
aws s3 cp /path/to/CED01-15A.PIP s3://your-bucket/pip/

Code Quality

# Run tests
uv run pytest

# Run tests with coverage
uv run pytest --cov

# Type checking
uv run mypy src

# Lint
uv run ruff check .

# Format
uv run ruff format .

# Fix lint issues
uv run ruff check --fix .

Database Migrations

This project uses Alembic for database migrations. Migrations read database connection settings from the same CUSIP_* environment variables as the application.

Specifying Target Database

Set the database connection via environment variables:

# Option 1: Export variables
export CUSIP_DB_HOST=your-rds-host.amazonaws.com
export CUSIP_DB_PORT=5432
export CUSIP_DB_NAME=cusip
export CUSIP_DB_USER=cusip_app
export CUSIP_DB_PASSWORD=your-password
uv run alembic upgrade head

# Option 2: Inline (useful for one-off commands)
CUSIP_DB_HOST=localhost CUSIP_DB_PASSWORD=postgres uv run alembic upgrade head

# Option 3: Use .env file (automatically loaded)
uv run alembic upgrade head

Running Migrations

# Apply all migrations
uv run alembic upgrade head

# Rollback one migration
uv run alembic downgrade -1

# View migration history
uv run alembic history

# View current revision
uv run alembic current

# Show SQL without executing (useful for review)
uv run alembic upgrade head --sql

Creating New Migrations

# Auto-generate migration from model changes
uv run alembic revision --autogenerate -m "description"

# Create empty migration
uv run alembic revision -m "description"

Docker vs Migrations

Local Docker development: Uses init scripts in docker/init/ (runs on first container creation)
Production deployment: Use Alembic migrations for schema management

Production Migration Example

Using Secrets Manager (recommended for rotating secrets):

# Run migrations using Secrets Manager ARN
CUSIP_DB_SECRET_ARN=arn:aws:secretsmanager:us-east-1:123456789:secret:cusip/db-AbCdEf \
uv run alembic upgrade head

Or fetch credentials manually:

# Run migrations against production RDS
CUSIP_DB_HOST=cusip-db.xxxx.us-east-1.rds.amazonaws.com \
CUSIP_DB_NAME=cusip \
CUSIP_DB_USER=cusip_app \
CUSIP_DB_PASSWORD=$(aws secretsmanager get-secret-value --secret-id cusip/db --query SecretString --output text | jq -r .password) \
uv run alembic upgrade head

Production Deployment

AWS ECS/Fargate

Build and push Docker image:

docker build -t cusip-service -f docker/Dockerfile .
docker tag cusip-service:latest <account>.dkr.ecr.<region>.amazonaws.com/cusip-service:latest
docker push <account>.dkr.ecr.<region>.amazonaws.com/cusip-service:latest

Run migrations (from a task or CI/CD pipeline):

CUSIP_DB_SECRET_ARN=arn:aws:secretsmanager:us-east-1:123456789:secret:cusip/db \
uv run alembic upgrade head

Configure ECS task definition with Secrets Manager:

{
  "containerDefinitions": [
    {
      "name": "cusip-api",
      "image": "<account>.dkr.ecr.<region>.amazonaws.com/cusip-service:latest",
      "portMappings": [{"containerPort": 8000}],
      "environment": [
        {"name": "CUSIP_DB_SECRET_ARN", "value": "arn:aws:secretsmanager:us-east-1:123456789:secret:cusip/db"},
        {"name": "CUSIP_FILE_SOURCE", "value": "s3"},
        {"name": "CUSIP_S3_BUCKET", "value": "cusip-pip-files"},
        {"name": "CUSIP_S3_PREFIX", "value": "pip/"}
      ],
      "secrets": [
        {
          "name": "CUSIP_API_TOKEN",
          "valueFrom": "arn:aws:secretsmanager:us-east-1:123456789:secret:cusip/api-token"
        }
      ]
    }
  ]
}

The application fetches DB credentials from the secret ARN at startup, which works seamlessly with RDS rotating secrets.

Required IAM permissions for the ECS task role:

{
  "Effect": "Allow",
  "Action": [
    "secretsmanager:GetSecretValue"
  ],
  "Resource": [
    "arn:aws:secretsmanager:us-east-1:123456789:secret:cusip/*"
  ]
}

Configure ALB routing:
- /jobs/*, /health, /ready, /live → FastAPI (8000)
- /api/* → PostgREST (3000)

For a complete step-by-step guide with AWS CLI commands, see docs/ECS_DEPLOYMENT_CHEATSHEET.md.

Health Checks

Configure your load balancer/orchestrator health checks:

Endpoint	Purpose	Recommended Interval
`/live`	Liveness probe	10s
`/ready`	Readiness probe	5s
`/health`	Full health check	30s

Project Structure

CusipService/
├── pyproject.toml              # Dependencies and tool config
├── alembic.ini                 # Alembic configuration
├── migrations/                 # Database migrations
│   ├── env.py
│   └── versions/
│       └── 001_initial_schema.py
├── docker/
│   ├── Dockerfile              # FastAPI container
│   ├── docker-compose.yml      # Local development stack
│   └── init/                   # DB init scripts (dev only)
│       ├── 01-cusip_ddl.sql
│       ├── 02-cusip_ref_data.sql
│       ├── 03-cusip_views.sql
│       ├── 04-full_text_search.sql
│       └── 05-postgrest_roles.sql
├── docs/
│   └── AWS_MULTI_ACCOUNT_SETUP.md  # Multi-account S3 deployment guide
├── sql/                        # Source SQL files
│   ├── cusip_ddl.sql
│   ├── cusip_ref_data.sql
│   └── cusip_views.sql
└── src/cusipservice/
    ├── __init__.py
    ├── __main__.py             # CLI entry point
    ├── config.py               # Configuration management
    ├── loader.py               # Core loading logic
    ├── file_source.py          # File source abstraction (local + S3)
    ├── file_discovery.py       # File pattern matching (legacy compat)
    └── api/
        ├── main.py             # FastAPI application
        ├── dependencies.py     # Auth and config injection
        ├── models.py           # Pydantic models
        └── routers/
            ├── health.py       # Health check endpoints
            └── jobs.py         # Job endpoints

License

Proprietary - Internal use only.

Name		Name	Last commit message	Last commit date
Latest commit History 4 Commits
docker		docker
docs		docs
migrations		migrations
sql		sql
src/cusipservice		src/cusipservice
.gitignore		.gitignore
CLAUDE.md		CLAUDE.md
README.md		README.md
alembic.ini		alembic.ini
pyproject.toml		pyproject.toml
uv.lock		uv.lock

OptionMetrics/CusipService

Folders and files

Latest commit

History

Repository files navigation

CusipService

Architecture

Data Flow

File Types

Quick Start

Prerequisites

Running with Docker Compose

Services

API Reference

Control Plane (Port 8000)

Health Checks

Load Jobs

Query Plane (Port 3000)

Available Views

Filtering Examples

Full-Text Search

PostgREST Operators

Local Development

Setup

Environment Variables

Database Settings

File Source Settings

API Settings

Example .env for Local Development

Example .env for S3 File Source

Using S3 with AWS SSO (Local Development)

CLI with S3

S3 Bucket Organization

Code Quality

Database Migrations

Specifying Target Database

Running Migrations

Creating New Migrations

Docker vs Migrations

Production Migration Example

Production Deployment

AWS ECS/Fargate

Health Checks

Project Structure

License

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors 2

Uh oh!

Languages

Example `.env` for Local Development

Example `.env` for S3 File Source

Packages