Centaur Software APIs

# CipherStream Enterprise Data Extraction Platform

**Customer API Documentation & Testing Collection**

---

## About CipherStream

**CipherStream** is Centaur Software's proprietary enterprise bulk data extraction platform, designed for high-performance data extraction from cloud-hosted databases with intelligent processing optimisation.

### Key Features
- **39 Specialised Procedures**: Complete coverage of dental practice management data
- **Intelligent Processing**: Auto-switching between streaming (≤100K rows) and job processing (>100K rows)
- **Enterprise Security**: AES-256-GCM encryption, API key authentication, IP whitelisting
- **Multiple Formats**: JSON, NDJSON, CSV with optional GZIP/ZIP compression
- **Real-time & Batch**: Sub-second streaming for small datasets, S3-backed jobs for large datasets
- **Webhook Integration**: Real-time notifications for job completion

---

## Authentication

### API Key Types
| Key Type | Format | Access Level | Use Case |
|----------|--------|--------------|----------|
| **Customer Live** | `cs_live_*` | Production API access | Live data extraction |
| **Customer Demo** | `cs_demo_*` | Demo/testing access | Testing and evaluation |

### Headers Required
```http
Authorization: Bearer <your-api-key>
Content-Type: application/json
```

---

## Execution Modes

CipherStream automatically determines the optimal processing method based on data volume:

### Auto Mode (Recommended)
- **≤100K rows**: Streaming response (immediate)
- **>100K rows**: Background job with S3 download
- **>50MB estimated**: Automatic job mode regardless of row count
- **System decides**: Based on current load and data characteristics

### Stream Mode
- **Force streaming**: Real-time response for all requests
- **Recommended limit**: ≤100K rows for optimal performance
- **Timeout**: 30 seconds maximum
- **Use case**: Small datasets requiring immediate response

### Job Mode
- **Force background processing**: All requests create jobs
- **S3 storage**: Secure file storage with presigned URLs
- **Extended timeout**: 180 seconds for large extractions
- **Use case**: Large datasets, scheduled extractions

---

## Date Modifiers

Control which records are returned based on their creation and modification dates:

### Available Options
- **All** (default): Returns all records within the date range regardless of creation/update dates
- **Created**: Returns only records created within the specified date range
- **Updated**: Returns only records updated within the specified date range

### Usage Examples
```json
{
  "date_modifier": "Created",
  "from_date": "2024-01-01",
  "to_date": "2024-12-31"
}
```

### Behavior by Procedure Type
- **Reference Data**: Date modifiers not applicable (always returns current data)
- **Data Procedures**: Uses ts_4_insert (Created) and ts_4_update (Updated) fields
- **Special Procedures**: Applies to underlying table timestamp fields

---

## Output Formats

### NDJSON (Default)
- **Format**: Newline-delimited JSON
- **Structure**: One JSON object per line
- **Benefits**: Streaming-friendly, memory efficient
- **Use case**: Large datasets, real-time processing

```
{"id": 1, "name": "John Doe", "date": "2024-01-01"}
{"id": 2, "name": "Jane Smith", "date": "2024-01-02"}
{"metadata": {"execution_time": 2.5, "record_count": 2}}
```

### JSON
- **Format**: Standard JSON array
- **Structure**: Array of objects with metadata
- **Benefits**: Standard format, easy parsing
- **Use case**: Small to medium datasets, standard integrations

```json
{
  "data": [
    {"id": 1, "name": "John Doe", "date": "2024-01-01"},
    {"id": 2, "name": "Jane Smith", "date": "2024-01-02"}
  ],
  "metadata": {"execution_time": 2.5, "record_count": 2}
}
```

### CSV
- **Format**: Comma-separated values
- **Structure**: Header row followed by data rows
- **Benefits**: Universal compatibility, Excel-friendly
- **Use case**: Reporting, data analysis, spreadsheet import

```csv
id,name,date
1,John Doe,2024-01-01
2,Jane Smith,2024-01-02
```

---

## Compression Options

### None (Default)
- **No compression**: Raw output format
- **Use case**: Small datasets, immediate processing

### GZIP
- **Compression ratio**: 70-90% size reduction
- **Format**: .gz compressed files
- **Use case**: Large datasets, bandwidth optimisation

### ZIP
- **Compression ratio**: 60-80% size reduction
- **Format**: .zip archive files
- **Use case**: Multiple files, Windows compatibility

---

## Security & Encryption

### Data in Transit
- **TLS 1.3**: All API communications encrypted
- **Certificate pinning**: Enhanced security for production
- **HSTS**: HTTP Strict Transport Security enabled

### Data at Rest
- **AES-256-GCM**: S3 bucket encryption for job results
- **Presigned URLs**: Time-limited access (12 hours)
- **Automatic cleanup**: Files removed after expiry

### Authentication
- **Bearer tokens**: API key-based authentication
- **IP whitelisting**: Optional IP restriction
- **Rate limiting**: Prevents abuse and ensures fair usage

---

## Webhook System

### Supported Events
- **job.completed**: Job finished successfully with download URL
- **job.failed**: Job encountered an error with details
- **job.cancelled**: Job was manually cancelled

### Security Features
- **HMAC-SHA256**: Payload signature verification
- **Automatic retries**: Up to 3 attempts with exponential backoff
- **Delivery tracking**: Monitor success/failure rates
- **Timeout handling**: 30-second response timeout

### Webhook Payload Example
```json
{
  "event": "job.completed",
  "timestamp": "2024-09-26T10:02:15Z",
  "signature": "sha256=abc123...",
  "data": {
    "job_id": "appointments_1758630144658_c7511999",
    "customer_id": "your-customer-id",
    "status": "completed",
    "rows_processed": 45678,
    "execution_time_seconds": 64.75,
    "s3_url": "https://secure-download-url",
    "expires_at": "2024-09-26T22:02:15Z",
    "file_size_bytes": 2048576,
    "output_format": "ndjson",
    "compression": "gzip"
  }
}
```

---

## Job Management Lifecycle

### Job States
1. **Queued**: Job created and waiting for processing
2. **Running**: Data extraction in progress with real-time progress updates
3. **Completed**: Data available for download via S3 presigned URL
4. **Failed**: Error occurred during processing with detailed error information
5. **Cancelled**: Job manually cancelled by user or system timeout

### Job Features
- **Progress tracking**: Real-time percentage completion (0-100%)
- **Row counting**: Live count of processed records
- **Time estimation**: Estimated completion time based on current progress
- **Resource monitoring**: Memory and CPU usage tracking
- **Error handling**: Detailed error messages and recovery suggestions

### Download Management
- **S3 presigned URLs**: Secure, time-limited download links
- **12-hour expiry**: URLs automatically expire for security
- **Resume support**: Partial download recovery for large files
- **Bandwidth optimisation**: CDN-accelerated downloads

---

## Data Procedures Overview

### Reference Data (16 procedures)
- **Always streamed**: Immediate response (no job creation)
- **5-minute cache**: Optimal performance for frequently accessed data
- **No parameters required**: Simple, consistent access pattern
- **Small datasets**: Typically <1000 records per procedure
- **Use cases**: Dropdown population, validation, system configuration

### Data Procedures (22 procedures)
- **Intelligent mode selection**: Auto streaming/job based on volume
- **Date range filtering**: Flexible from_date/to_date parameters
- **Date modifier support**: Created, Updated, or All records
- **High-volume capable**: Handles millions of records efficiently
- **Use cases**: Bulk data extraction, reporting, analytics

### Special Procedures (1 procedure)
- **CALL syntax**: Advanced stored procedure execution
- **Direct table access**: Extract from any accessible database table
- **Extended timeout**: 180-second timeout for large extractions
- **Flexible parameters**: Custom table names and date filtering
- **Use cases**: Custom extractions, ad-hoc queries, data migration

---

## Performance Characteristics

### Streaming Response
- **Latency**: <2 seconds for reference data
- **Throughput**: Up to 10,000 records/second
- **Memory usage**: Constant memory footprint
- **Concurrent requests**: Up to 10 simultaneous streams

### Job Processing
- **Throughput**: Up to 100,000 records/second
- **Scalability**: Auto-scaling based on queue depth
- **Resource allocation**: Dedicated processing resources
- **Monitoring**: Real-time progress and performance metrics

---

## Error Handling

### HTTP Status Codes
- **200**: Success - Data returned or job created
- **400**: Bad Request - Invalid parameters or format
- **401**: Unauthorized - Invalid or missing API key
- **403**: Forbidden - Access denied or rate limited
- **404**: Not Found - Endpoint or resource not found
- **429**: Too Many Requests - Rate limit exceeded
- **500**: Internal Server Error - System error occurred

### Error Response Format
```json
{
  "error": {
    "code": "INVALID_DATE_RANGE",
    "message": "The specified date range is invalid",
    "details": "from_date must be earlier than to_date",
    "timestamp": "2024-09-26T10:00:00Z",
    "request_id": "req_abc123"
  }
}
```

---

## Environment URLs

- **Production**: https://api.cipherstream.com
- **UAT**: https://cipherstream-uat.centaur.software

---

## Support

**Centaur Software** - Enterprise Support
- **Email**: support@centaur.software
- **Documentation**: docs.cipherstream.com
- **Professional Services**: Custom integration and optimisation
- **SLA**: 99.9% uptime guarantee for production environment

---

**© 2024 Centaur Software. CipherStream is a proprietary enterprise platform.**

CipherStream Enterprise Data Extraction Platform

Data Security and Decryption

## Patient Management

**Core patient data and analytics** for comprehensive patient relationship management.

**Characteristics:**
- **Patient demographics** - Core patient information and contact details
- **Patient analytics** - Lifetime value and business intelligence
- **Follow-up management** - Recall scheduling and tracking

**Use Cases:**
- Patient management systems
- Contact information updates
- Demographics analysis
- Marketing and communication
- Patient retention analysis
- Follow-up scheduling

1. Patient Management

**Patient Data**: Extract comprehensive patient demographic and contact information.

**Parameters:**
- `customer_id` (required): Your unique customer identifier
- `from_date` (required): Start date for filtering (YYYY-MM-DD format)
- `to_date` (required): End date for filtering (YYYY-MM-DD format)
- `date_modifier` (optional): Filter mode - "All" (default), "Created", or "Updated"
- `mode` (optional): Execution mode - "auto" (default), "stream", or "job"
- `output_format` (optional): Response format - "ndjson" (default), "json", or "csv"
- `compression` (optional): Compression type - "none" (default), "gzip", or "zip"

**Date Modifier Behaviour:**
- **All**: Returns all patient records regardless of creation/update dates
- **Created**: Returns patients created between from_date and to_date (uses ts_4_insert)
- **Updated**: Returns patients updated between from_date and to_date (uses ts_4_update)

**Output Fields:**
- `patient_id`: Unique patient identifier
- `family_head_id`: Family head patient ID (if applicable)
- `patient_card`: Patient card number
- `created_date`: Patient record creation timestamp
- `updated_date`: Patient record last update timestamp
- `is_deleted`: Deletion status (0=active, 1=deleted)
- `patient_status`: Current patient status (Active/Inactive)
- `title`: Patient title (Mr, Mrs, Dr, etc.)
- `first_name`: Patient first name
- `surname`: Patient surname
- `address1`: Primary address line
- `address2`: Secondary address line
- `postcode`: Postal/ZIP code
- `suburb`: Suburb/city
- `state`: State/province
- `gender`: Patient gender
- `birthdate`: Date of birth
- `email_contact`: Email address
- `phone_contact`: Home phone number
- `mobile_contact`: Mobile phone number
- `contact_preference`: Preferred communication method
- `no_contact`: No contact flag (0=contact allowed, 1=no contact)
- `chart_first_seen_date`: First appointment/chart date
- `first_treat_date`: First treatment date
- `new_patient_date`: Calculated new patient date
- `latest_seen_date`: Most recent treatment date
- `practitioner_id`: Assigned practitioner ID
- `location_id`: Primary practice location ID
- `new_patient_location_id`: Location where patient was first seen
- `health_fund_id`: Primary health fund/insurance ID
- `insurance_fee_level`: Fee level for insurance billing
- `discount`: Default discount percentage
- `recall_for_check_up_date`: Next recall appointment date
- `medical_conditions`: Has medical conditions flag (0=no, 1=yes)
- `medical_conditions_recorded`: Comma-separated list of medical conditions
- `patient_alerts`: Patient alert flags
- `patient_alert_notes`: Detailed alert notes
- `allergies_present`: Has allergies flag (0=no, 1=yes)
- `allergies_recorded`: Comma-separated list of allergies
- `med_hist_last_updated`: Last medical history update date
- `referring_patient_id`: Referring patient ID (if patient referral)
- `referral_source_id`: Referral source type code
- `referral_date`: Date of referral

**Use Cases:**
- Patient management systems integration
- Contact information synchronization
- Demographics analysis and reporting
- Marketing and communication campaigns
- Patient lifecycle tracking
- Referral source analysis

Patients

**Analytics Data**: Extract patient lifetime value and statistics.

**Use Cases:**
- Patient value analysis
- Business intelligence reporting
- Customer relationship insights

Patient Lifetime

**Recall Data**: Extract patient recall and follow-up schedules.

Recalls

## Scheduling & Appointments

**Complete appointment management** including scheduling, status tracking, and optimisation.

**Characteristics:**
- **Core appointments** - Main appointment records with intelligent processing
- **Status management** - Appointment statuses and types for workflow control
- **Schedule optimisation** - Cancellation tracking and waitlist management
- **Communication** - Reminder and notification systems

**Use Cases:**
- Appointment booking systems
- Schedule management
- Cancellation analysis
- Waitlist optimisation
- Patient communication
- Scheduling templates

2. Scheduling & Appointments

**Core Appointment Data**: Extract comprehensive appointment records with intelligent processing.

**Parameters:**
- `customer_id` (required): Your unique customer identifier
- `from_date` (required): Start date for filtering (YYYY-MM-DD format)
- `to_date` (required): End date for filtering (YYYY-MM-DD format)
- `date_modifier` (optional): Filter mode - "All" (default), "Created", or "Updated"
- `mode` (optional): Execution mode - "auto" (default), "stream", or "job"
- `output_format` (optional): Response format - "ndjson" (default), "json", or "csv"
- `compression` (optional): Compression type - "none" (default), "gzip", or "zip"

**Date Modifier Behaviour:**
- **All**: Returns all appointments regardless of creation/update dates
- **Created**: Returns appointments created between from_date and to_date (uses ts_4_insert)
- **Updated**: Returns appointments updated between from_date and to_date (uses ts_4_update)

**Processing Modes:**
- **Auto** (Recommended): System automatically chooses streaming (≤100K rows) or job processing (>100K rows)
- **Stream**: Forces real-time streaming response (recommended for ≤100K appointments)
- **Job**: Forces background job processing with S3 download (for large datasets)

**Output Fields:**
- `appointment_id`: Unique appointment identifier
- `appointment_status`: Human-readable appointment status
- `appointment_status_codes`: Status abbreviation codes
- `cancelled`: Cancellation flag (0=not cancelled, 1=cancelled)
- `failed`: Failed appointment flag (0=not failed, 1=failed)
- `attended`: Patient attendance flag (0=not attended, 1=attended)
- `online`: Online booking flag (0=not online, 1=online booking)
- `appointment_type_code`: Appointment type identifier
- `appointment_type`: Appointment type description
- `appointment_date`: Date of appointment (YYYY-MM-DD)
- `start_time`: Scheduled start time (YYYY-MM-DD HH:MM:SS)
- `end_time`: Scheduled end time (YYYY-MM-DD HH:MM:SS)
- `appointment_arrival_time`: Actual patient arrival time
- `actual_start_time`: Actual appointment start time
- `actual_end_time`: Actual appointment end time
- `duration`: Scheduled duration in minutes
- `recall_set_linked`: Linked to recall system flag (0=no, 1=yes)
- `patient_id`: Associated patient identifier
- `practitioner_id`: Assigned practitioner identifier
- `location_id`: Practice location identifier
- `created_date`: Appointment creation timestamp
- `original_date`: Original creation date (for rescheduled appointments)
- `created_by`: User who created the appointment
- `updated_date`: Last update timestamp
- `updated_by`: User who last updated the appointment
- `is_deleted`: Deletion status (0=active, 1=deleted)
- `confirmed`: Confirmation status (0=not confirmed, 1=confirmed)
- `schedule_id`: Appointment book/schedule identifier

**Key Features:**
- **Intelligent Processing**: Auto-switching between streaming and job processing
- **High-Volume Support**: Handles millions of appointment records efficiently
- **Real-Time Data**: Includes both active and deleted appointments for complete audit trail
- **Status Tracking**: Comprehensive appointment status and workflow tracking
- **Time Accuracy**: Scheduled vs actual times for performance analysis

**Use Cases:**
- Appointment management system integration
- Schedule optimisation and analysis
- Patient flow and wait time analysis
- Practitioner utilization reporting
- Appointment outcome tracking
- Business intelligence and KPI dashboards

Appointments

**Reference Data**: Retrieve all appointment status codes and descriptions.

**Use Cases:**
- Populate appointment status dropdowns
- Validate appointment status values
- Display human-readable status names

**Performance:** <2 seconds, 5-minute cache

Appointment Statuses

**Reference Data**: Retrieve all appointment type definitions and configurations.

**Use Cases:**
- Appointment booking interfaces
- Scheduling system integration
- Service type validation

**Performance:** <2 seconds, 5-minute cache

Appointment Types

**Scheduling Data**: Extract appointment cancellation records and reasons.

**Use Cases:**
- Cancellation pattern analysis
- No-show tracking
- Schedule optimisation insights

Cancellations

**Scheduling Data**: Extract standby and waitlist information.

**Use Cases:**
- Waitlist management
- Appointment optimisation
- Schedule gap filling

Standby

**Reference Data**: Retrieve schedule templates and configurations.

Schedules

**Reminder Data**: Extract appointment reminders and notifications.

Reminders

## Financial Management

**Comprehensive financial data** including transactions, payments, invoicing, and accounts receivable.

**Characteristics:**
- **Core financials** - Main financial transaction records
- **Payment processing** - Receipts, deposits, and payment allocations
- **Billing management** - Invoices and discount tracking
- **Accounts receivable** - Debtor management and outstanding balances
- **Pricing structure** - Fee schedules and payment types

**Use Cases:**
- Financial reporting and analysis
- Revenue tracking
- Accounting system integration
- Business intelligence dashboards
- Payment reconciliation
- Debt collection workflows

3. Financial Management

**Core Financial Data**: Extract comprehensive financial transaction records with detailed breakdown.

**Parameters:**
- `customer_id` (required): Your unique customer identifier
- `from_date` (required): Start date for filtering (YYYY-MM-DD format)
- `to_date` (required): End date for filtering (YYYY-MM-DD format)
- `date_modifier` (optional): Filter mode - "All" (default), "Created", or "Updated"
- `mode` (optional): Execution mode - "auto" (default), "stream", or "job"
- `output_format` (optional): Response format - "ndjson" (default), "json", or "csv"
- `compression` (optional): Compression type - "none" (default), "gzip", or "zip"

**Date Modifier Behaviour:**
- **All**: Returns all financial records regardless of creation/update dates
- **Created**: Returns transactions created between from_date and to_date
- **Updated**: Returns transactions updated between from_date and to_date

**Output Fields (Based on Financial Data Model):**
- `fin_key`: Unique financial transaction key
- `date_effective`: Transaction effective date
- `surname`: Patient surname
- `title`: Patient title
- `first_name`: Patient first name
- `transaction_id`: Unique transaction identifier
- `invoice_total`: Total invoice amount
- `invoice_discount`: Applied discount amount
- `invoice_involvement`: Invoice involvement percentage
- `payment_total`: Total payment amount
- `payment_involvement`: Payment involvement percentage
- `lab_fees`: Laboratory fees amount
- `lab_fees_involvement`: Lab fees involvement percentage
- `transaction_type_1`: Primary transaction type code
- `practitioner_id`: Associated practitioner identifier
- `location_id`: Practice location identifier
- `patient_id`: Associated patient identifier
- `item_code`: Service/procedure item code
- `item_description`: Service/procedure description
- `tooth_number`: Tooth number (for dental procedures)
- `surface`: Tooth surface (for dental procedures)
- `fee_amount`: Individual fee amount
- `discount_amount`: Individual discount amount
- `payment_amount`: Individual payment amount
- `transaction_date`: Transaction processing date
- `created_date`: Record creation timestamp
- `updated_date`: Record update timestamp

**Processing Features:**
- **High-Volume Processing**: Efficiently handles millions of financial records
- **Detailed Breakdown**: Includes invoice, payment, and discount components
- **Multi-Currency Support**: Handles different currency formats
- **Audit Trail**: Complete transaction history with timestamps

**Use Cases:**
- Financial reporting and analysis
- Revenue tracking and forecasting
- Accounting system integration
- Business intelligence dashboards
- Tax reporting and compliance
- Practice performance analysis
- Patient billing reconciliation
- Insurance claim processing

Financials

**Payment Data**: Extract receipt and payment records.

**Use Cases:**
- Payment reconciliation
- Cash flow analysis
- Receipt generation
- Payment method tracking

Financials - Receipts

**Discount Data**: Extract discount and adjustment records.

Financials - Discounts

**Deposit Data**: Extract deposit and prepayment records.

Financials - Deposits

**Invoice Data**: Extract invoice and billing records.

Financials - Invoices

**Payment Allocation Data**: Extract payment allocation and application records.

Financials - Payment Allocations

**Financial Data**: Extract debtor information and outstanding balances.

**Use Cases:**
- Accounts receivable reporting
- Debt collection workflows
- Financial analysis and reporting

Debtors

Debtors Detailed

**Reference Data**: Retrieve payment method types and configurations.

Payment Types

**Reference Data**: Retrieve fee schedules and pricing information.

Fees

**Reference Data**: Retrieve fee level structures and pricing tiers.

Fee Level

## Clinical & Treatment

**Clinical data management** including completed treatments and treatment planning.

**Characteristics:**
- **Treatment records** - Completed procedures and clinical work
- **Treatment planning** - Proposed treatments and care plans
- **Service catalogue** - Available procedures and service items

**Use Cases:**
- Clinical reporting and analysis
- Treatment outcome tracking
- Care plan management
- Service utilisation analysis
- Clinical decision support
- Quality assurance

4. Clinical & Treatment

**Treatment Data**: Extract comprehensive completed treatment and procedure records.

**Parameters:**
- `customer_id` (required): Your unique customer identifier
- `from_date` (required): Start date for filtering (YYYY-MM-DD format)
- `to_date` (required): End date for filtering (YYYY-MM-DD format)
- `date_modifier` (optional): Filter mode - "All" (default), "Created", or "Updated"
- `mode` (optional): Execution mode - "auto" (default), "stream", or "job"
- `output_format` (optional): Response format - "ndjson" (default), "json", or "csv"
- `compression` (optional): Compression type - "none" (default), "gzip", or "zip"

**Date Modifier Behaviour:**
- **All**: Returns all treatment records regardless of creation/update dates
- **Created**: Returns treatments created between from_date and to_date (uses ts_4_insert)
- **Updated**: Returns treatments updated between from_date and to_date (uses ts_4_update)

**Output Fields:**
- `patient_id`: Associated patient identifier
- `treatment_date`: Date treatment was performed
- `item_code`: Procedure/service item code
- `item_qty`: Quantity of items/procedures performed
- `item_fee`: Fee charged for the item/procedure
- `invoice_id`: Associated invoice identifier
- `item_paid`: Payment status (0=unpaid, 1=paid)
- `practitioner_id`: Practitioner who performed treatment
- `tooth_number`: Tooth number and surface (e.g., "12 (MO)")
- `location_id`: Practice location where treatment occurred
- `plan_id`: Associated treatment plan identifier
- `fee_discount`: Discount applied to the fee
- `ada_item`: ADA/standard procedure code
- `treatment_id`: Unique treatment record identifier
- `non_treatment`: Non-treatment item flag (0=treatment, 1=non-treatment)
- `non_reporting`: Non-reporting item flag (0=reportable, 1=non-reportable)
- `health_fund_id`: Associated health fund/insurance identifier
- `created_date`: Treatment record creation timestamp
- `updated_date`: Treatment record update timestamp
- `is_deleted`: Deletion status (0=active, 1=deleted)

**Key Features:**
- **Clinical Accuracy**: Complete treatment history with procedure details
- **Financial Integration**: Links to invoicing and payment systems
- **Audit Trail**: Full creation and modification tracking
- **Tooth-Level Detail**: Specific tooth and surface information for dental procedures
- **Insurance Integration**: Health fund and billing information

**Use Cases:**
- Clinical reporting and analysis
- Treatment outcome tracking
- Provider productivity analysis
- Insurance claim processing
- Patient treatment history
- Quality assurance and compliance
- Revenue per procedure analysis
- Clinical decision support

Treatment

**Treatment Planning Data**: Extract treatment plans and proposed procedures.

Treatment Plans

**Reference Data**: Retrieve service items and procedure codes.

Items

## Business Operations

**Business expense management** and operational cost tracking.

**Characteristics:**
- **Expense tracking** - Business expense records with date filtering
- **Expense categorisation** - Expense categories for financial reporting

**Use Cases:**
- Expense tracking and reporting
- Budget analysis
- Tax preparation
- Cost centre reporting
- Financial planning
- Operational efficiency analysis

5. Business Operations

**Financial Data**: Extract expense records and categorisations.

**Use Cases:**
- Expense tracking and reporting
- Budget analysis
- Tax preparation
- Cost centre reporting

Expenses

**Reference Data**: Retrieve expense category classifications for financial reporting.

**Use Cases:**
- Expense tracking applications
- Financial reporting categorisation
- Budget planning tools

Expense Categories

## Practice Setup & Configuration

**Practice management configuration** including staff, locations, and system setup.

**Characteristics:**
- **Staff management** - Practitioners and users with role definitions
- **Location management** - Practice locations and facilities
- **System configuration** - User roles and access control
- **External relationships** - Health funds, third parties, and referral sources
- **Recall management** - Recall types and follow-up configurations

**Use Cases:**
- Practice setup and configuration
- Staff management systems
- Location and facility management
- User access control
- External partner integration
- Follow-up system configuration

6. Practice Setup & Configuration

**Reference Data**: Retrieve practitioner information and credentials.

Practitioners

**Reference Data**: Retrieve practice locations and facility information.

Locations

**Reference Data**: Retrieve system user information and access levels.

Users

**Reference Data**: Retrieve user roles and permission definitions.

Roles

**Reference Data**: Retrieve health fund provider information and codes.

Health Funds

**Reference Data**: Retrieve third-party provider and vendor information.

Third Parties

**Reference Data**: Retrieve referral source information and tracking codes.

Referral Sources

**Reference Data**: Retrieve recall type definitions and schedules.

Recall Types

## Advanced Tools

**Advanced data extraction capabilities** for power users and custom requirements.

**Characteristics:**
- **Direct table access** - Extract from any accessible database table
- **CALL syntax** - Advanced stored procedure execution
- **Extended timeout** - 180-second timeout for large extractions
- **Flexible parameters** - Custom table names and date filtering

**Use Cases:**
- Custom data extractions
- Ad-hoc reporting requirements
- Data migration projects
- Specialised analytics queries
- Direct database access
- Custom integration needs

7. Advanced Tools

**Advanced Extraction**: Direct table extraction with flexible filtering.

**Key Features:**
- **Direct table access** - Extract from any accessible database table
- **CALL syntax** - Uses advanced stored procedure execution
- **Extended timeout** - 180-second timeout for large extractions
- **Flexible parameters** - Custom table names and date filtering

**Common Table Names:**
- `a_appointments` - Appointment records
- `a_patients` - Patient information
- `a_financials` - Financial transactions
- `a_treatment` - Treatment records

**Performance:**
- Timeout: 180 seconds (3 minutes)
- Optimised for large datasets
- Automatic job creation for >100K rows

Extract From Table

## Job Management

**Background job management** for large data extractions and processing with comprehensive monitoring and control.

**When Jobs Are Created:**
- **Large datasets**: >100,000 rows automatically trigger job processing
- **File size threshold**: >50MB estimated output size
- **Explicit job mode**: User specifically requests job processing
- **System load balancing**: High system load triggers job queuing
- **Complex queries**: Resource-intensive extractions
- **Scheduled extractions**: Automated recurring data pulls

**Job Lifecycle States:**
1. **Queued** - Job created and waiting for available processing resources
2. **Running** - Data extraction in progress with real-time progress updates
3. **Completed** - Data successfully extracted and available for download via S3
4. **Failed** - Error occurred during processing with detailed error information
5. **Cancelled** - Job manually cancelled by user or system timeout

**Job Processing Features:**
- **Real-time progress tracking** - Live percentage completion (0-100%)
- **Row count monitoring** - Current number of processed records
- **Time estimation** - Estimated completion time based on current progress
- **Resource allocation** - Dedicated processing resources for optimal performance
- **Error handling** - Detailed error messages and recovery suggestions
- **Automatic retries** - Built-in retry logic for transient failures

**Download Management:**
- **S3 secure storage** - Enterprise-grade cloud storage with encryption
- **Presigned URLs** - Time-limited, secure download links
- **12-hour expiry** - URLs automatically expire for security
- **Resume support** - Partial download recovery for large files
- **CDN acceleration** - Global content delivery for faster downloads
- **Bandwidth optimisation** - Compressed files for efficient transfer

**Monitoring & Notifications:**
- **Webhook integration** - Real-time job completion alerts
- **Email notifications** - Optional email alerts for job status changes
- **Progress callbacks** - Periodic progress updates via webhooks
- **Performance metrics** - Execution time, throughput, and resource usage
- **Audit logging** - Complete job history and user actions

**Security & Compliance:**
- **Data encryption** - AES-256-GCM encryption for stored files
- **Access control** - Customer-specific job isolation
- **Audit trail** - Complete job lifecycle logging
- **Automatic cleanup** - Files removed after expiry for data protection
- **IP restrictions** - Optional IP-based access control

8. Job Management

**Job Listing**: Retrieve a comprehensive list of your background jobs with advanced filtering and sorting.

**Query Parameters:**
- `limit` (optional): Number of jobs to return (1-100, default: 50)
- `status` (optional): Filter by job status
- `offset` (optional): Pagination offset for large result sets
- `sort` (optional): Sort order - "created_desc" (default), "created_asc", "updated_desc", "updated_asc"
- `procedure` (optional): Filter by specific procedure name
- `from_date` (optional): Filter jobs created after this date
- `to_date` (optional): Filter jobs created before this date

**Status Values:**
- `queued` - Job created and waiting for available processing resources
- `running` - Data extraction currently in progress with real-time updates
- `completed` - Job finished successfully with download URL available
- `failed` - Error occurred during processing with detailed error information
- `cancelled` - Job manually cancelled by user or system timeout

**Response Fields:**
- `job_id`: Unique job identifier for tracking and reference
- `procedure_name`: Name of the data extraction procedure
- `status`: Current job status with detailed state information
- `progress_percent`: Real-time completion percentage (0-100)
- `rows_processed`: Current number of records processed
- `estimated_rows`: Estimated total number of rows to process
- `estimated_completion_seconds`: Estimated time to completion
- `created_at`: Job creation timestamp
- `started_at`: Job processing start timestamp
- `completed_at`: Job completion timestamp (if completed)
- `execution_time_seconds`: Total processing time
- `file_size_bytes`: Size of generated output file
- `output_format`: Requested output format (ndjson, json, csv)
- `compression`: Applied compression type (none, gzip, zip)
- `s3_url`: Secure download URL (available when completed)
- `expires_at`: Download URL expiration timestamp
- `error_message`: Detailed error information (if failed)
- `retry_count`: Number of automatic retry attempts

**Use Cases:**
- Monitor job progress and status
- Retrieve download URLs for completed jobs
- Track job performance and execution times
- Manage job queues and priorities
- Audit job history and usage patterns

List Jobs

**Job Details**: Get detailed status and progress information for a specific job.

**Response Fields:**
- `job_id`: Unique job identifier
- `status`: Current job status
- `progress_percent`: Completion percentage (0-100)
- `rows_processed`: Number of records processed
- `s3_url`: Download URL (when completed)
- `expires_at`: Download URL expiration time
- `execution_time_seconds`: Total processing time

Get Job Status

**Job Cancellation**: Cancel a running or queued job.

**Cancellation Rules:**
- Only jobs in `queued` or `running` status can be cancelled
- Completed or failed jobs cannot be cancelled
- Cancellation is immediate for queued jobs
- Running jobs may take a few seconds to stop

Cancel Job

## Integrations

**Advanced webhook system** for real-time notifications, system integration, and automated workflow triggers.

**Supported Events:**
- `job.completed` - Job finished successfully with download URL and metadata
- `job.failed` - Job encountered an error with detailed failure information
- `job.cancelled` - Job was manually cancelled or timed out
- `job.progress` - Periodic progress updates during job execution (optional)
- `system.maintenance` - Scheduled maintenance notifications
- `api.rate_limit` - Rate limit threshold warnings

**Webhook Security Features:**
- **HMAC-SHA256 signature** - Cryptographic payload verification using shared secret
- **Timestamp validation** - Prevents replay attacks with time-based verification
- **IP whitelisting** - Optional source IP restrictions for enhanced security
- **TLS encryption** - All webhook deliveries use HTTPS/TLS 1.3
- **Signature verification** - Complete payload integrity checking

**Delivery & Reliability:**
- **Automatic retries** - Up to 3 retry attempts with exponential backoff
- **Delivery tracking** - Complete success/failure monitoring and logging
- **Timeout handling** - 30-second response timeout with configurable settings
- **Dead letter queue** - Failed deliveries stored for manual retry
- **Circuit breaker** - Automatic endpoint disabling for persistent failures
- **Rate limiting** - Configurable delivery rate limits to prevent overwhelming

**Webhook Payload Structure:**
```json
{
  "event": "job.completed",
  "timestamp": "2024-09-26T10:02:15Z",
  "signature": "sha256=abc123def456...",
  "delivery_id": "del_1758630144658",
  "attempt": 1,
  "data": {
    "job_id": "appointments_1758630144658_c7511999",
    "customer_id": "your-customer-id",
    "procedure_name": "appointments",
    "status": "completed",
    "rows_processed": 45678,
    "execution_time_seconds": 64.75,
    "file_size_bytes": 2048576,
    "s3_url": "https://secure-download-url",
    "expires_at": "2024-09-26T22:02:15Z",
    "output_format": "ndjson",
    "compression": "gzip",
    "metadata": {
      "from_date": "2024-01-01",
      "to_date": "2024-12-31",
      "date_modifier": "Created"
    }
  }
}
```

**Signature Verification:**
```python
import hmac
import hashlib

def verify_webhook_signature(payload, signature, secret):
    expected_signature = hmac.new(
        secret.encode('utf-8'),
        payload.encode('utf-8'),
        hashlib.sha256
    ).hexdigest()
    return hmac.compare_digest(f"sha256={expected_signature}", signature)
```

**Configuration Options:**
- **Event filtering** - Subscribe to specific events only
- **Custom headers** - Add custom HTTP headers to webhook requests
- **Retry configuration** - Customize retry attempts and backoff strategy
- **Timeout settings** - Configure response timeout values
- **Batch delivery** - Group multiple events into single webhook call

**Monitoring & Debugging:**
- **Delivery logs** - Complete webhook delivery history
- **Response tracking** - HTTP status codes and response times
- **Error analysis** - Detailed failure reasons and troubleshooting
- **Performance metrics** - Delivery success rates and latency statistics
- **Test endpoints** - Webhook testing and validation tools

**Use Cases:**
- **Automated workflows** - Trigger business processes when jobs complete
- **Real-time notifications** - Instant alerts when data extraction finishes
- **System integration** - Connect CipherStream to other business systems
- **Data pipeline automation** - Chain multiple data processing steps
- **Monitoring and alerting** - Track job completion and system health
- **Business intelligence** - Trigger report generation and dashboard updates
- **Customer notifications** - Inform end users when their data is ready

9. Integrations

**Webhook Configuration**: Set up comprehensive webhook URL and event subscriptions with advanced security and delivery options.

**Request Body Parameters:**
- `webhook_url` (required): Your HTTPS endpoint URL for receiving webhooks
- `events` (required): Array of event types to subscribe to
- `enabled` (optional): Enable/disable webhook delivery (default: true)
- `secret` (optional): Shared secret for HMAC signature verification
- `custom_headers` (optional): Custom HTTP headers to include in webhook requests
- `retry_config` (optional): Custom retry configuration settings
- `timeout_seconds` (optional): Response timeout in seconds (default: 30, max: 60)
- `batch_delivery` (optional): Enable batching multiple events (default: false)
- `ip_whitelist` (optional): Array of allowed source IP addresses

**Event Types:**
- `job.completed` - Job finished successfully with download URL
- `job.failed` - Job encountered an error with detailed failure information
- `job.cancelled` - Job was manually cancelled or timed out
- `job.progress` - Periodic progress updates during execution (optional)
- `system.maintenance` - Scheduled maintenance notifications
- `api.rate_limit` - Rate limit threshold warnings

**Advanced Configuration Example:**
```json
{
  "webhook_url": "https://your-api.com/webhooks/cipherstream",
  "events": ["job.completed", "job.failed"],
  "enabled": true,
  "secret": "your-webhook-secret-key",
  "custom_headers": {
    "X-API-Version": "v1",
    "X-Client-ID": "your-client-id"
  },
  "retry_config": {
    "max_attempts": 3,
    "backoff_multiplier": 2,
    "initial_delay_seconds": 1
  },
  "timeout_seconds": 30,
  "batch_delivery": false,
  "ip_whitelist": ["203.0.113.0/24"]
}
```

**Security Requirements:**
- **HTTPS only**: Webhook URLs must use HTTPS protocol
- **Valid SSL certificate**: Endpoint must have valid SSL/TLS certificate
- **Response requirements**: Endpoint must return HTTP 2xx status codes
- **Signature verification**: Implement HMAC-SHA256 signature validation
- **Timestamp validation**: Check webhook timestamp to prevent replay attacks

**Response Validation:**
Your webhook endpoint should:
1. Return HTTP status 200-299 for successful processing
2. Respond within the configured timeout period
3. Validate the HMAC signature if secret is configured
4. Handle duplicate deliveries gracefully (idempotent processing)
5. Log webhook deliveries for debugging and monitoring

**Error Handling:**
- **4xx responses**: Webhook delivery will not be retried
- **5xx responses**: Webhook will be retried according to retry configuration
- **Timeout**: Request timeout will trigger retry attempts
- **Connection errors**: Network failures will trigger retry attempts
- **SSL errors**: Certificate validation failures will not be retried

**Use Cases:**
- Real-time job completion notifications
- Automated data pipeline triggers
- System integration and workflow automation
- Monitoring and alerting systems
- Business process automation
- Customer notification systems

Configure Webhook

**Webhook Testing**: Send a test ping to verify your webhook endpoint.

**Expected Response:**
Your endpoint should return HTTP 200-299 status code.

Test Webhook

**Delivery History**: View webhook delivery attempts and their status.

**Query Parameters:**
- `limit`: Number of deliveries to return (1-100)
- `status`: Filter by delivery status

**Delivery Status:**
- `pending` - Queued for delivery
- `delivered` - Successfully delivered
- `failed` - Delivery failed (will retry)

List Webhook Deliveries

**Manual Retry**: Manually retry a failed webhook delivery.

**Use Cases:**
- Retry after fixing endpoint issues
- Force delivery of important notifications
- Testing webhook endpoint changes

Retry Failed Webhook

## Documentation

**API documentation** and OpenAPI schemas for integration and development.

**Available Documentation:**
- **Interactive Docs** - Swagger UI for testing endpoints
- **OpenAPI Schema** - Machine-readable API specification
- **Integration Guides** - Code examples and best practices

**Use Cases:**
- Interactive API testing and exploration
- Code generation for client libraries
- API documentation for development teams
- Integration planning and validation
- Request/response format reference

10. Documentation

**OpenAPI Specification**: Machine-readable API schema for code generation and integration.

**Use Cases:**
- **Code generation** - Generate client libraries
- **API documentation** - Import into documentation tools
- **Validation** - Validate requests and responses
- **Integration** - Import into API management tools

**Format:** OpenAPI 3.0 JSON specification

OpenAPI Schema

Default

CipherStream

api_key

CipherStream Documentation

Production

<p class="slate-p " ><strong class="slate-bold">Advanced Extraction</strong>: Direct table extraction with flexible filtering.</p><p class="slate-p " ><strong class="slate-bold">Key Features:</strong></p><ul class="slate-ul " ><li class="slate-li " ><div style="position:relative"><strong class="slate-bold">Direct table access</strong> - Extract from any accessible database table</div></li><li class="slate-li " ><div style="position:relative"><strong class="slate-bold">CALL syntax</strong> - Uses advanced stored procedure execution</div></li><li class="slate-li " ><div style="position:relative"><strong class="slate-bold">Extended timeout</strong> - 180-second timeout for large extractions</div></li><li class="slate-li " ><div style="position:relative"><strong class="slate-bold">Flexible parameters</strong> - Custom table names and date filtering</div></li></ul><p class="slate-p " ><strong class="slate-bold">Common Table Names:</strong></p><ul class="slate-ul " ><li class="slate-li " ><div style="position:relative"><code class="slate-code">a_appointments</code> - Appointment records</div></li><li class="slate-li " ><div style="position:relative"><code class="slate-code">patients</code> - Patient information</div></li><li class="slate-li " ><div style="position:relative"><code class="slate-code">a_cancellation_list</code> - Cancellation records</div></li><li class="slate-li " ><div style="position:relative"><code class="slate-code">treat</code> - Treatment records</div></li></ul><p class="slate-p " ><strong class="slate-bold">Performance:</strong></p><ul class="slate-ul " ><li class="slate-li " ><div style="position:relative">Timeout: 180 seconds (3 minutes)</div></li><li class="slate-li " ><div style="position:relative">Optimised for large datasets</div></li><li class="slate-li " ><div style="position:relative">Automatic job creation for &gt;100K rows</div></li></ul><p class="slate-p " ></p>

: Direct table extraction with flexible filtering.

customer_id

The name of the database table from which data will be extracted

table_name

Start date for filtering (YYYY-MM-DD format)

from_date

End date for filtering (YYYY-MM-DD format)

to_date

Filter mode - "All" (default), "Created", or "Updated"

date_modifier

Execution mode - "auto" (default), "stream", or "job"

mode

Response format - "ndjson" (default), "json", or "csv"

output_format

Compression type - "none" (default), "gzip", or "zip"

Sections

Extract From Table

Header Parameters

Body Parameters

What made this section unhelpful for you?

What made this section unhelpful for you?