CipherStream Documentation

## Sections
• [CipherStream Enterprise Data Extraction Platform](https://developer.cipherstream.centaur.software/cipherstream-customer-api.md): 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 38 Specialised Data Endpoints : Complete coverage of Dental4Web 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 Title Description Title Description 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 Plain text 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 Usage Examples JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "ndjson", "compression": "gzip", "encrypted": true } JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "job", "output_format": "csv", "compression": "zip", "encrypted": true } JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "stream", "output_format": "ndjson", "compression": "gzip", "encrypted": true } 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 Plain text { "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 Plain text {"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 Plain text { "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 Plain text id,name,date 1,John Doe,2024-01-01 2,Jane Smith,2024-01-02 Usage Examples JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "ndjson", "compression": "none", "encrypted": false } JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "json", "compression": "none", "encrypted": false } JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "csv", "compression": "none", "encrypted": false } Compression Options None (Default) No compression : Raw output format Use case : Small datasets, immediate processing Available in : Streaming and Job modes GZIP Compression ratio : 70-96% size reduction (tested) Format : .gz compressed files Smart threshold : Only compresses data >8KB for optimal performance Available in : Streaming mode and Job mode Use case : Large datasets, bandwidth optimisation HTTP Header : Content-Encoding: gzip ZIP Compression ratio : 60-97% size reduction (tested) Format : .zip archive files with proper structure Smart threshold : Only compresses data >8KB for optimal performance Available in : Streaming mode and Job mode Use case : Multiple files, Windows compatibility HTTP Header : Content-Encoding: deflate Compression ratio : 70-90% size reduction Format : .gz compressed files Use case : Large datasets, bandwidth optimisation Usage Examples JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "ndjson", "compression": "gzip", "encrypted": false } JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "json", "compression": "zip", "encrypted": false } 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 End-to-End Encryption AES-256-GCM : Customer-specific encryption keys Available in : Streaming mode and Job mode Chunk-based : Each data chunk encrypted individually Key rotation : Key rotation support Usage : Set "encrypted": true in request body Format : {"encrypted_data": "key_id:iv:ciphertext"} Authentication Bearer tokens : API key-based authentication IP whitelisting : Optional IP restriction Rate limiting : Prevents abuse and ensures fair usage Usage Examples JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "ndjson", "compression": "none", "encrypted": true } JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "auto", "output_format": "csv", "compression": "zip", "encrypted": false } 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 Usage Examples JSON { "customer_id": "sandbox", "from_date": "2024-01-01", "to_date": "2024-12-31", "date_modifier": "All", "mode": "job", "output_format": "ndjson", "compression": "gzip", "encrypted": false, "webhook_url": "https://yourwebhook.com/9e7212ee-c41f-4aca-a8f5-c850357fd6a9" } Webhook Payload Example Plain text { "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 Queued : Job created and waiting for processing Running : Data extraction in progress with real-time progress updates Completed : Data available for download via S3 presigned URL Failed : Error occurred during processing with detailed error information 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 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 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 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 Plain text { "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://cipherstream.centaur.software UAT : https://cipherstream-uat.centaur.software
• [Data Security and Decryption](https://developer.cipherstream.centaur.software/data-security-and-decryption.md): You may opt to receive your export encrypted at rest . Along with your API key, we’ll issue a base64-encoded 32-byte AES key (“Customer Encryption Key”). Important: Store your key in a secrets manager. Never share it with us. Encrypted delivery format To request encryption: { "compression": "zip", "encrypted": true } You’ll receive: A ZIP from a presigned S3 URL Inside, a single .ndjson file Each line contains: {"encrypted_data":"key_id:iv_b64:ciphertext_plus_tag_b64"} Title Description Part Meaning key_id Identifier of the key used (informational) iv_b64 12-byte IV (nonce), base64 ciphertext_plus_tag_b64 Base64 of `ciphertext Example (shortened): {"encrypted_data":"3e9c..e2f1:0R0y8kS3g8m2s6v8:AAABBBCCC...zzz"} Cryptography profile Cipher: AES-256-GCM Key: your 32-byte key (base64-decode first) IV: 12 bytes (from iv_b64 ) Auth tag: 16 bytes (trailing bytes of the decoded blob) AAD: stream:{customer_id}:{row_index} row_index is zero-based for each NDJSON line. Decryption steps Unzip to get job_id.ndjson . Read file line by line . Parse JSON; get encrypted_data . split(":") → [key_id, iv_b64, ctext_tag_b64] . iv = base64(iv_b64) . raw = base64(ctext_tag_b64) ; then: ciphertext = raw[0 : len(raw) - 16] tag = raw[len(raw) - 16 : ] aad = "stream:{customer_id}:{row_index}" . Decrypt with AES-256-GCM ( key , iv , aad , ciphertext , tag ). Parse the UTF-8 JSON result. Reference implementations Replace {{customer_key_b64}} and {{customer_id}} with your values (or Theneo variables). Python Python from cryptography.hazmat.primitives.ciphers.aead import AESGCM import base64 import json # Your pre-shared values derived_key_base64 = "your-pre-shared-key" customer_id = "your-customer-id" # Decode your key encryption_key = base64.b64decode(derived_key_base64) # For each encrypted line (row_index starts at 0) def decrypt_line(encrypted_data, row_index): # Split the encrypted data parts = encrypted_data.split(":") iv = base64.b64decode(parts[1]) ciphertext = base64.b64decode(parts[2]) # Create AAD aad = f"stream:{customer_id}:{row_index}".encode('utf-8') # Decrypt aesgcm = AESGCM(encryption_key) decrypted_bytes = aesgcm.decrypt(iv, ciphertext, aad) # Parse JSON return json.loads(decrypted_bytes.decode('utf-8')) Node.js JavaScript const crypto = require("node:crypto"); const CUSTOMER_KEY_B64 = "{{customer_key_b64}}"; const CUSTOMER_ID = "{{customer_id}}"; const KEY = Buffer.from(CUSTOMER_KEY_B64, "base64"); function decryptLine(encryptedData, rowIndex) { const [keyId, iv_b64, ctext_tag_b64] = encryptedData.split(":"); const iv = Buffer.from(iv_b64, "base64"); const raw = Buffer.from(ctext_tag_b64, "base64"); const ciphertext = raw.slice(0, -16); const tag = raw.slice(-16); const aad = Buffer.from(`stream:${CUSTOMER_ID}:${rowIndex}`, "utf8"); const decipher = crypto.createDecipheriv("aes-256-gcm", KEY, iv); decipher.setAAD(aad); decipher.setAuthTag(tag); const decrypted = Buffer.concat([decipher.update(ciphertext), decipher.final()]); return JSON.parse(decrypted.toString("utf8")); } Java Plain text import javax.crypto.Cipher; import javax.crypto.spec.GCMParameterSpec; import javax.crypto.spec.SecretKeySpec; import java.util.Base64; import java.nio.charset.StandardCharsets; import java.util.Arrays; final class Decryptor { private static final String CUSTOMER_KEY_B64 = "{{customer_key_b64}}"; private static final String CUSTOMER_ID = "{{customer_id}}"; private static final byte[] KEY = Base64.getDecoder().decode(CUSTOMER_KEY_B64); public static String decryptLine(String encryptedData, int rowIndex) throws Exception { String[] parts = encryptedData.split(":"); byte[] iv = Base64.getDecoder().decode(parts[1]); byte[] raw = Base64.getDecoder().decode(parts[2]); byte[] ciphertext = Arrays.copyOfRange(raw, 0, raw.length - 16); byte[] tag = Arrays.copyOfRange(raw, raw.length - 16, raw.length); Cipher cipher = Cipher.getInstance("AES/GCM/NoPadding"); cipher.init(Cipher.DECRYPT_MODE, new SecretKeySpec(KEY, "AES"), new GCMParameterSpec(128, iv)); cipher.updateAAD(("stream:" + CUSTOMER_ID + ":" + rowIndex).getBytes(StandardCharsets.UTF_8)); byte[] combined = new byte[ciphertext.length + tag.length]; System.arraycopy(ciphertext, 0, combined, 0, ciphertext.length); System.arraycopy(tag, 0, combined, ciphertext.length, tag.length); byte[] plain = cipher.doFinal(combined); return new String(plain, StandardCharsets.UTF_8); } } C# (.NET) C# using System; using System.Text; using System.Text.Json; using System.Security.Cryptography; public static class Decrypter { private static readonly string CustomerKeyB64 = "{{customer_key_b64}}"; private static readonly string CustomerId = "{{customer_id}}"; private static readonly byte[] Key = Convert.FromBase64String(CustomerKeyB64); public static string DecryptLine(string encryptedData, int rowIndex) { var parts = encryptedData.Split(':'); var iv = Convert.FromBase64String(parts[1]); var raw = Convert.FromBase64String(parts[2]); var ciphertext = raw[..^16]; var tag = raw[^16..]; var aad = Encoding.UTF8.GetBytes($"stream:{CustomerId}:{rowIndex}"); var plaintext = new byte[ciphertext.Length]; using var aes = new AesGcm(Key); aes.Decrypt(iv, ciphertext, tag, plaintext, aad); return Encoding.UTF8.GetString(plaintext); } } Validation & troubleshooting Key decodes to exactly 32 bytes AAD exactly stream:{customer_id}:{row_index} (row index is zero-based) IV is 12 bytes; tag is the last 16 bytes of the decoded blob Don’t reuse IV/AAD on your side when testing
• [Webhook Signature Verification](https://developer.cipherstream.centaur.software/webhook-signature-verification.md): Every webhook delivery includes an HMAC-SHA256 signature for authenticity verification. Always verify signatures before processing webhook payloads. Signature Header Plain text X-CipherStream-Signature: sha256= X-CipherStream-Timestamp: 1706745600 X-CipherStream-Delivery-ID: dlv_abc123 Verification Steps Extract the signature from X-CipherStream-Signature header (remove sha256= prefix) Get the raw request body (do not parse JSON first) Compute HMAC-SHA256 using your webhook secret Compare signatures using constant-time comparison Reference Implementations Python Python import hmac import hashlib def verify_signature(payload: bytes, signature: str, secret: str) -> bool: expected = hmac.new( secret.encode('utf-8'), payload, hashlib.sha256 ).hexdigest() return hmac.compare_digest(f"sha256={expected}", signature) # Usage raw_body = request.body # Raw bytes, not parsed JSON signature = request.headers.get('X-CipherStream-Signature') if not verify_signature(raw_body, signature, your_webhook_secret): return Response(status=401) Node.js JavaScript const crypto = require('crypto'); function verifySignature(payload, signature, secret) { const expected = 'sha256=' + crypto .createHmac('sha256', secret) .update(payload, 'utf8') .digest('hex'); return crypto.timingSafeEqual( Buffer.from(expected), Buffer.from(signature) ); } // Usage const rawBody = req.rawBody; // Raw string, not parsed const signature = req.headers['x-cipherstream-signature']; if (!verifySignature(rawBody, signature, yourWebhookSecret)) { return res.status(401).send('Invalid signature'); } C# (.NET) Plain text using System.Security.Cryptography; using System.Text; public static bool VerifySignature(string payload, string signature, string secret) { using var hmac = new HMACSHA256(Encoding.UTF8.GetBytes(secret)); var hash = hmac.ComputeHash(Encoding.UTF8.GetBytes(payload)); var expected = "sha256=" + BitConverter.ToString(hash).Replace("-", "").ToLower(); return CryptographicOperations.FixedTimeEquals( Encoding.UTF8.GetBytes(expected), Encoding.UTF8.GetBytes(signature) ); } Secret Rotation CipherStream supports zero-downtime webhook secret rotation with dual-secret validation. Grace Period : When a secret is rotated, both the old and new secrets are valid for a configurable grace period (default: 7 days) Seamless Transition : Update your verification code to accept either secret during rotation Rotation Notification : You'll receive notification before rotation occurs Dual-Secret Verification Example (Python) Python def verify_with_rotation(payload: bytes, signature: str, current_secret: str, previous_secret: str = None) -> bool: # Try current secret first if verify_signature(payload, signature, current_secret): return True # Fall back to previous secret during rotation if previous_secret and verify_signature(payload, signature, previous_secret): return True return False Security Best Practices Title Description Practice Description Constant-time comparison Always use timing-safe comparison functions Verify before processing Reject requests with invalid signatures immediately Use raw payload Verify against raw bytes, not parsed/reformatted JSON Secure secret storage Store webhook secrets in a secrets manager Monitor failures Alert on repeated signature verification failures Retry Behaviour Title Description Title Attempt Delay Total Wait 1 Immediate 0s 2 1 minute 1m 3 5 minutes 6m 4 15 minutes 21m 5 1 hour 1h 21m 6 2 hours 3h 21m Webhooks are retried on 5xx errors and timeouts. Return 2xx to acknowledge successful receipt.
• [1. Patient Management](https://developer.cipherstream.centaur.software/1-patient-management.md): 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
  • [Patients](https://developer.cipherstream.centaur.software/1-patient-management/patients.md): Patient Data : Extract comprehensive patient demographic and contact information. Attributes Title Description Title Attribute Type Description patient_id integer Unique patient identifier family_head_id integer Family head patient ID patient_card string Patient card number family_role enum: Family Head | Family Member Family relationship to the family head patient_status enum: Active | Inactive Current patient status record_status enum: Active | Inactive | Deleted Current record status title string Patient title (Mr, Mrs, Dr, etc.) first_name string Patient first name middle_name string Patient middle name surname string Patient surname preferred_name string Preferred name gender string Patient gender birthdate date Date of birth occupation string Occupation email string Email address phone string Home phone number mobile string Mobile phone number work_phone string Work phone number address1 string Primary address line 1 address2 string Primary address line 2 postcode string Postcode suburb string Suburb state string State work_address1 string Work address line 1 work_address2 string Work address line 2 work_postcode string Work postcode work_suburb string Work suburb work_state string Work state recall_comm_preference enum: SMS | Email | Letter Recall communications preference appointment_comm_preference enum: SMS | Email | Letter Appointment communications preference invoice_comm_preference enum: SMS | Email | Letter Invoice communications preference queries_comm_preference enum: SMS | Email | Letter Queries (marketing) communications preference first_seen_date date First seen date first_treatment_date date First treatment date new_patient_date date Calculated new patient date latest_seen_date date Most recent treatment date practitioner_id integer Assigned practitioner ID location_id integer Primary practice location ID new_patient_location_id integer Location where patient was first seen health_fund_name string Health fund name health_fund_ref_member_id string Health fund membership number health_fund_ref_number string Individual reference number (IRN) fee_level string Fee level for billing medicare_no string Medicare number medicare_IRN string Medicare IRN discount_percent number Default discount percentage recall_date date Next recall appointment date medical_conditions string Comma-separated list of medical conditions alerts string Patient alert notes allergies string Comma-separated list of allergies personal_details string Patient personal notes med_hist_last_updated date Last medical history update date referring_patient_id integer Referring patient ID (if patient referral) referral_source_id string Referral source type code referral_date date Date of referral created_date date-time Patient record creation timestamp updated_date date-time Patient record last update timestamp custom_field_* string Practice-defined custom fields flattened into top-level keys using the custom_field_ prefix followed by the actual custom name (e.g. custom_field_Preferred Hygienist ) Use Cases: Patient management systems integration Contact information synchronization Demographics analysis and reporting Marketing and communication campaigns Patient lifecycle tracking Referral source analysis
  • [Patient Lifetime](https://developer.cipherstream.centaur.software/1-patient-management/patient-lifetime.md): Analytics Data : Extract patient lifetime value and statistics. Attributes Title Description Title Attribute Type Description patient_id integer Unique patient identifier location_id integer Practice location identifier lv_visits integer Total billable visits used in the lifetime-value calculation lv_value string Patient lifetime value (AUD) as a fixed-point string, e.g. "21855.31" Use Cases: Patient value analysis Business intelligence reporting Customer relationship insights
  • [Recalls](https://developer.cipherstream.centaur.software/1-patient-management/recalls.md): Recall Data : Extract patient recall and follow-up schedules. Attributes Title Description Title Attribute Type Description recall_type_id integer Recall type identifier patient_id integer Unique patient identifier recall_date date Date of recall (YYYY-MM-DD) reminder_date date Reminder date (YYYY-MM-DD) reminder_type string Reminder channel (SMS, Email, Letter) reminder_no integer Reminder sequence number practitioner_id integer Assigned practitioner identifier location_id integer Practice location identifier created_date date-time Record creation timestamp updated_date date-time Last update timestamp
• [2. Scheduling & Appointments](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments.md): 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
  • [Appointments](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments/appointments.md): Core Appointment Data : Extract comprehensive appointment records with intelligent processing. 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) Attributes Title Description Title Attribute Type Description appointment_id integer Unique appointment identifier appointment_status string Human-readable appointment status appointment_status_codes string Status abbreviation codes cancelled integer (0/1) Cancellation flag (0 = not cancelled, 1 = cancelled) failed integer (0/1) Failed appointment flag (0 = not failed, 1 = failed) attended integer (0/1) Patient attendance flag (0 = not attended, 1 = attended) online integer (0/1) Online booking flag (0 = not online, 1 = online booking) appointment_type_code string Appointment type identifier appointment_type string Appointment type description appointment_date date Date of appointment (YYYY-MM-DD) start_time date-time Scheduled start time (YYYY-MM-DD HH:MM:SS) end_time date-time Scheduled end time (YYYY-MM-DD HH:MM:SS) appointment_arrival_time date-time, nullable Actual patient arrival time actual_start_time date-time, nullable Actual appointment start time actual_end_time date-time, nullable Actual appointment end time duration integer (minutes) Scheduled duration in minutes recall_set_linked integer (0/1) Linked to recall system flag (0 = no, 1 = yes) patient_id integer Associated patient identifier practitioner_id integer Assigned practitioner identifier location_id integer Practice location identifier created_date date-time Appointment creation timestamp original_date date, nullable Original creation date (for rescheduled appointments) created_by string User who created the appointment updated_date date-time Last update timestamp updated_by string User who last updated the appointment is_deleted integer (0/1) Deletion status (0 = active, 1 = deleted) confirmed integer (0/1) Confirmation status (0 = not confirmed, 1 = confirmed) schedule_id integer 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
  • [Appointment Statuses](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments/appointment-statuses.md): Reference Data : Retrieve all appointment status codes and descriptions. Attributes Title Description Title Attribute Type Description status_code string Status code (e.g. "A") status_name string Status name (e.g. "Attended") Use Cases: Populate appointment status dropdowns Validate appointment status values Display human-readable status names
  • [Appointment Types](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments/appointment-types.md): Reference Data : Retrieve all appointment type definitions and configurations. Attributes Title Description Title Attribute Type Description appointment_type_code integer Appointment type code appointment_type_name string Appointment type name created_date date-time Record creation timestamp updated_date date-time Last update timestamp is_active integer Active status Use Cases: Appointment booking interfaces Scheduling system integration Service type validation
  • [Cancellations](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments/cancellations.md): Scheduling Data : Extract appointment cancellation records and reasons. Attributes Title Description Title Attribute Type Description appointment_id integer Unique appointment identifier appointment_date date Date of appointment (YYYY-MM-DD) appointment_time time Scheduled start time (HH:MM:SS) appointment_duration integer Scheduled duration in minutes patient_id integer Patient identifier practitioner_id integer Practitioner identifier schedule_id integer Appointment book/schedule identifier location_id integer Practice location identifier cancel_date date Cancellation date (if applicable) cancel_type string Cancellation type (e.g. "Failed") Use Cases: Cancellation pattern analysis No-show tracking Schedule optimisation insights
  • [Standby](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments/standby.md): Scheduling Data : Extract standby and waitlist information. Attributes Title Description Title Attribute Type Description standby_id integer Standby list entry identifier patient_id integer Patient identifier schedule_id integer Appointment book/schedule identifier practitioner_id integer Practitioner identifier standby_duration integer Requested duration in minutes standby_date date-time Requested standby date/time reason string Reason or notes for standby request location_id integer Practice location identifier Use Cases: Waitlist management Appointment optimisation Schedule gap filling
  • [Schedules](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments/schedules.md): Reference Data : Retrieve schedule templates and configurations. Attributes Title Description Title Attribute Type Description schedule_id integer Schedule identifier Schedule_name string Human-readable schedule name location_id integer Practice location identifier start_time time Schedule start time (HH:MM:SS) end_time time Schedule end time (HH:MM:SS) is_active integer Active status flag (0 = inactive, 1 = active)
  • [Reminders](https://developer.cipherstream.centaur.software/2-scheduling-and-appointments/reminders.md): Reminder Data : Extract appointment reminders and notifications. Attributes Title Description Title Attribute Type Description reminder_id integer Reminder record identifier patient_id integer Patient identifier practitioner_id integer Practitioner identifier appointment_id integer Associated appointment identifier reminder_sent_time date-time Reminder sent timestamp reminder_reply_time date-time Reminder reply timestamp confirmed integer (0/1) Confirmation status (0 = not confirmed, 1 = confirmed) location_id integer Practice location identifier
• [3. Financial Management](https://developer.cipherstream.centaur.software/3-financial-management.md): 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
  • [Financials](https://developer.cipherstream.centaur.software/3-financial-management/financials.md): Attributes Title Description Title Attribute Type Description fin_key string Unique financial transaction key date_effective date Transaction effective date (YYYY-MM-DD) surname string Patient surname title string Patient title first_name string Patient first name transaction_id integer Unique transaction identifier invoice_total string Total invoice amount (fixed-point, e.g. "560.0000" ) invoice_discount string Applied discount amount (fixed-point) invoice_involvement string Invoice involvement percentage (fixed-point) payment_total string, nullable Total payment amount (fixed-point) payment_involvement string, nullable Payment involvement percentage (fixed-point) lab_fees string, nullable Laboratory fees amount (fixed-point) lab_fees_involvement string, nullable Lab fees involvement percentage (fixed-point) transaction_type_1 string Primary transaction type code practitioner_id integer Associated practitioner identifier practitioner_code string Practitioner code main_practitioner integer, nullable Main practitioner ID (if applicable) allocation_id integer, nullable Allocation identifier (if applicable) gst_amount string GST amount (fixed-point) gst_discount string, nullable GST discount amount (fixed-point) commission_percent string Commission percentage (fixed-point) location_id_invoice integer Invoice location identifier location_id_payment integer Payment location identifier transaction_type_2 string Secondary transaction type code commission_expenses string, nullable Commission expenses amount (fixed-point) PPA_Status string, nullable PPA status payment_id integer, nullable Payment identifier date_processing date-time Transaction processing timestamp (YYYY-MM-DD HH:MM:SS) updated_date date-time Record update timestamp (YYYY-MM-DD HH:MM:SS) payment_type string, nullable Payment method/type health_fund_id integer, nullable Health fund identifier payment_amount string, nullable Individual payment amount (fixed-point) chequename string Cheque payer name (if applicable) chequebank string Cheque bank (if applicable) chequebranch string Cheque branch (if applicable) 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 - Receipts](https://developer.cipherstream.centaur.software/3-financial-management/financials-receipts.md): Payment Data : Extract receipt and payment records. Attributes Title Description Title Attribute Type Description receipt_key string Unique receipt key receipt_id integer Receipt identifier receipt_installment_id integer Receipt instalment identifier payment_type_id integer Payment type identifier practitioner_location_id integer Practitioner’s location identifier receipt_location_id integer Receipt location identifier date_effective date Effective date (YYYY-MM-DD) date_processing date Processing date (YYYY-MM-DD) updated_date date-time Last update timestamp receipt_amount string Receipt amount (fixed-point, e.g. "148.4000" ) deposit integer (0/1) Deposit flag (0 = no, 1 = yes) deposit_refund integer (0/1) Deposit refund flag (0 = no, 1 = yes) reversal integer (0/1) Reversal flag (0 = no, 1 = yes) Use Cases: Payment reconciliation Cash flow analysis Receipt generation Payment method tracking
  • [Financials - Discounts](https://developer.cipherstream.centaur.software/3-financial-management/financials-discounts.md): Discount Data : Extract discount and adjustment records. Attributes Title Description Title Attribute Type Description discount_key string Unique discount record key invoice_id integer Invoice identifier date_effective date Effective date (YYYY-MM-DD) date_processing date Processing date (YYYY-MM-DD) updated_date date-time Last update timestamp discount_amount string Discount amount (fixed-point; may be negative) reversal integer (0/1) Reversal flag (0 = no, 1 = yes) location_id integer Practice location identifier
  • [Financials - Deposits](https://developer.cipherstream.centaur.software/3-financial-management/financials-deposits.md): Deposit Data : Extract deposit and prepayment records. Attributes Title Description Title Attribute Type Description receipt_key string Unique receipt key receipt_id integer Receipt identifier receipt_installment_id integer Receipt instalment identifier payment_type_id integer Payment type identifier patient_id integer Patient identifier practitioner_id integer Practitioner identifier practitioner_location_id integer Practitioner’s location identifier receipt_location_id integer Receipt location identifier date_effective date Effective date (YYYY-MM-DD) date_processing date Processing date (YYYY-MM-DD) updated_date date-time Last update timestamp receipt_amount string Receipt amount (fixed-point, e.g. "100.0000" ) deposit integer (0/1) Deposit flag (0 = no, 1 = yes) deposit_refund integer (0/1) Deposit refund flag (0 = no, 1 = yes) reversal integer (0/1) Reversal flag (0 = no, 1 = yes)
  • [Financials - Invoices](https://developer.cipherstream.centaur.software/3-financial-management/financials-invoices.md): Invoice Data : Extract invoice and billing records. Attributes Title Description Title Attribute Type Description invoice_key string Unique invoice record key invoice_id integer Invoice identifier date_effective date Effective date (YYYY-MM-DD) date_processing date Processing date (YYYY-MM-DD) updated_date date-time Last update timestamp invoice_amount string Invoice amount (fixed-point, e.g. "150.0000" ) reversal integer (0/1) Reversal flag (0 = no, 1 = yes) location_id integer Practice location identifier
  • [Financials - Payment Allocations](https://developer.cipherstream.centaur.software/3-financial-management/financials-payment-allocations.md): Payment Allocation Data : Extract payment allocation and application records. Attributes Title Description Title Attribute Type Description allocation_key string Unique allocation record key receipt_id integer Receipt identifier invoice_id integer Invoice identifier date_effective date Effective date (YYYY-MM-DD) date_processing date Processing date (YYYY-MM-DD) updated_date date-time Last update timestamp allocated_amount string Allocated amount (fixed-point, e.g. "76.0000") reversal integer Reversal flag (0 = no, 1 = yes) invoice_location_id integer Invoice location identifier receipt_location_id integer Receipt location identifier
  • [Debtors](https://developer.cipherstream.centaur.software/3-financial-management/debtors.md): Financial Data : Extract debtor information and outstanding balances. Attributes Title Description Title Attribute Type Description responsible_party_id integer Responsible party identifier responsible_party string Responsible party name (e.g. "Leach Ms Aliyah") invoice_id integer Invoice identifier date_due date Invoice due date (YYYY-MM-DD) days_over integer Days overdue invoice_amount string Invoice amount (fixed-point, e.g. "450.00") invoice_balance string Outstanding balance (fixed-point, e.g. "340.00") is_third_party integer (0/1) Third-party payer flag (0 = no, 1 = yes) is_bad_debt integer (0/1) Bad debt flag (0 = no, 1 = yes) location_id integer Practice location identifier Use Cases: Accounts receivable reporting Debt collection workflows Financial analysis and reporting
  • [Debtors Detailed](https://developer.cipherstream.centaur.software/3-financial-management/debtors-detailed.md): Financial Data : Extract detailed debtor information and outstanding balances. Attributes Title Description Title Attribute Type Description invoice_id integer Invoice identifier instalment_number integer Instalment sequence number date_due date Instalment due date instalment string Instalment amount balance string Outstanding balance rem_1 string First reminder note rem_2 string Second reminder note rem_3 string Third reminder note rem_bad string Bad debt reminder note send_acc_to_third_party integer Send account to third party flag patient_firstname string Patient given name patient_surname string Patient surname third_party_name string Third party name send_acc_to_pat_id integer Patient identifier to send account to days_due integer Days overdue account_payment_plan_id integer Payment plan identifier notes string Notes doctors_list string Comma delimited practitioner identifiers patient_id_list string Comma delimited patient identifiers balance_for_doctor string Balance for doctor patient_head_id integer Responsible party patient identifier bad_debt string Bad debt flag as string printed integer Printed flag reminder_printed integer Reminder printed flag location_id integer Practice location identifier
  • [Payment Types](https://developer.cipherstream.centaur.software/3-financial-management/payment-types.md): Reference Data : Retrieve payment method types and configurations. Attributes Title Description Title Attribute Type Description payment_type_id integer Payment type identifier payment_type_description string Payment type name reporting_description string Reporting label/description show_in_reports integer (0/1) Visibility flag for reports (0 = no, 1 = yes) is_deleted integer (0/1) Deletion status (0 = active, 1 = deleted)
  • [Fees](https://developer.cipherstream.centaur.software/3-financial-management/fees.md): Reference Data : Retrieve fee schedules and pricing information. Attributes Title Description Title Attribute Type Description location_id integer Practice location identifier fee_level integer Fee level identifier item_code string Service/procedure item code valid_from date Start date of fee validity (YYYY-MM-DD) valid_to date End date of fee validity (YYYY-MM-DD) fee string Fee amount (fixed-point, e.g. "0.00")
  • [Fee Level](https://developer.cipherstream.centaur.software/3-financial-management/fee-level.md): Reference Data : Retrieve fee level structures and pricing tiers. Attributes Title Description Title Attribute Type Description location_id integer Practice location identifier fee_level integer Fee level identifier description string Fee level description is_default_fee integer (0/1) Default fee level flag (0 = no, 1 = yes) is_active integer (0/1) Active status flag (0 = inactive, 1 = active)
• [4. Clinical & Treatment](https://developer.cipherstream.centaur.software/4-clinical-and-treatment.md): ## 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
  • [Treatment](https://developer.cipherstream.centaur.software/4-clinical-and-treatment/treatment.md): Attributes Title Description Title Attribute Type Description patient_id integer Associated patient identifier treatment_date date Date treatment was performed (YYYY-MM-DD) item_code string Procedure/service item code item_qty integer Quantity of items/procedures performed item_fee string Fee charged for the item/procedure (fixed-point, e.g. "120.00" ) invoice_id integer Associated invoice identifier item_paid integer (0/1) Payment status (0 = unpaid, 1 = paid) practitioner_id integer Practitioner who performed treatment tooth_number string Tooth number and surface (e.g. "12 (MO)" ) location_id integer Practice location where treatment occurred plan_id integer, nullable Associated treatment plan identifier fee_discount string Discount applied to the fee (fixed-point) ada_item string ADA/standard procedure code treatment_id integer Unique treatment record identifier non_treatment integer (0/1) Non-treatment item flag (0 = treatment, 1 = non-treatment) non_reporting integer (0/1) Non-reporting item flag (0 = reportable, 1 = non-reportable) health_fund_id integer, nullable Associated health fund/insurance identifier created_date date-time Treatment record creation timestamp updated_date date-time Treatment record update timestamp is_deleted integer (0/1) 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 Plans](https://developer.cipherstream.centaur.software/4-clinical-and-treatment/treatment-plans.md): Treatment Planning Data : Extract treatment plans and proposed procedures. Attributes Title Description Title Attribute Type Description plan_id integer Treatment plan identifier plan_status string Plan status (e.g. "Completed") treatment_date date Date of treatment (YYYY-MM-DD) patient_id integer Patient identifier practitioner_id integer Practitioner identifier treatment_type string, nullable Treatment type/category (if recorded) item_code string Procedure/service item code item_qty integer Quantity of items/procedures tooth_number string Tooth number/surface (e.g. "47", "12 (MO)") appointment_id integer, nullable Linked appointment identifier item_fee string Fee charged (fixed-point, e.g. "289.00") created_date_plan date-time Plan creation timestamp created_by string User who created the plan updated_date_plan date-time, nullable Plan last update timestamp treatment_id integer Treatment record identifier location_id integer Practice location identifier plan_treatment_id integer Plan treatment line identifier ada_item string ADA/standard procedure code created_date_treatment date-time Treatment creation timestamp updated_date_treatment date-time Treatment last update timestamp created_date date-time Record creation timestamp updated_date date-time Record last update timestamp primary_plan_id integer Primary/parent plan identifier is_active integer (0/1) Active status flag (0 = inactive, 1 = active) is_deleted integer (0/1) Deletion status (0 = active, 1 = deleted)
  • [Items](https://developer.cipherstream.centaur.software/4-clinical-and-treatment/items.md): Reference Data : Retrieve service items and procedure codes. Attributes Title Description Title Attribute Type Description category string Top-level item category (e.g. "DIAGNOSTIC") sub_category string Item sub-category (e.g. "Examinations") item_code string Practice item code ADA_Code string ADA/standard procedure code description string Item description (e.g. "Letter Of Referral") non_reporting integer (0/1) Non-reporting flag (0 = reportable, 1 = non-reportable) non_treatment integer (0/1) Non-treatment flag (0 = treatment, 1 = non-treatment) created_date date-time Record creation timestamp updated_date date-time Last update timestamp
• [5. Business Operations](https://developer.cipherstream.centaur.software/5-business-operations.md): ## 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
  • [Expenses](https://developer.cipherstream.centaur.software/5-business-operations/expenses.md): Financial Data : Extract expense records and categorisations. Attributes Title Description Title Attribute Type Description expense_id integer Expense identifier location_id integer Practice location identifier practitioner_id integer Practitioner identifier category_id integer Expense category identifier patient_id integer Patient identifier amount string Expense amount (fixed-point) gst string GST amount (fixed-point) invoice_id integer Linked invoice identifier expense_date date Expense date is_deleted integer Deletion status flag (0 or 1) Use Cases: Expense tracking and reporting Budget analysis Tax preparation Cost centre reporting
  • [Expense Categories](https://developer.cipherstream.centaur.software/5-business-operations/expense-categories.md): Reference Data : Retrieve expense category classifications for financial reporting. Attributes Title Description Title Attribute Type Description category_id integer Category identifier category_name string Category name is_active integer Active status flag Use Cases: Expense tracking applications Financial reporting categorisation Budget planning tools
• [6. Practice Setup & Configuration](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration.md): 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
  • [Practitioners](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/practitioners.md): Reference Data : Retrieve practitioner information and credentials. Attributes Title Description Title Attribute Type Description practitioner_id integer Practitioner identifier is_active integer Active status flag (0 or 1) practitioner_code string Practitioner code full_name string Full name first_name string First name surname string Surname title string Title role_id integer Role identifier provider_number string Provider number created_date date-time Record creation timestamp updated_date date-time Last update timestamp qualifications string Qualifications location_id integer Practice location identifier
  • [Locations](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/locations.md): Reference Data : Retrieve practice locations and facility information. Attributes Title Description Title Attribute Type Description location_id integer Practice location identifier location_name string Location name is_active integer Active status flag
  • [Users](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/users.md): Reference Data : Retrieve system user information and access levels. Attributes Title Description Title Attribute Type Description user_id integer User identifier username string Username firstname string First name surname string Surname location_id integer Practice location identifier is_active integer Active status flag user_type string User type created_date date-time Record creation timestamp updated_date date-time Last update timestamp
  • [Roles](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/roles.md): Reference Data : Retrieve user roles and permission definitions. Attributes Title Description Title Attribute Type Description role_id integer Role identifier role_name string Role name
  • [Health Funds](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/health-funds.md): Reference Data : Retrieve health fund provider information and codes. Attributes Title Description Title Attribute Type Description location_id integer Practice location identifier health_fund_id integer Health fund identifier health_fund_name string Health fund name is_preferred_provider integer Preferred provider flag fee_level integer Fee level identifier created_date date-time Record creation timestamp updated_date date-time Last update timestamp
  • [Third Parties](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/third-parties.md): Reference Data : Retrieve third-party provider and vendor information. Attributes Title Description Title Attribute Type Description third_party_id integer Third-party identifier third_party_name string Third-party name third_party_type string Third-party type
  • [Referral Sources](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/referral-sources.md): Reference Data : Retrieve referral source information and tracking codes. Attributes Title Description Title Attribute Type Description referral_source_id string Referral source identifier referral_source_name string Referral source name is_active integer Active status flag
  • [Recall Types](https://developer.cipherstream.centaur.software/6-practice-setup-and-configuration/recall-types.md): Reference Data : Retrieve recall type definitions and schedules. Attributes Title Description Title Attribute Type Description recall_type_id integer Recall type identifier recall_type string Recall type name
• [7. Advanced Tools](https://developer.cipherstream.centaur.software/7-advanced-tools.md): ## 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
  • [Extract From Table](https://developer.cipherstream.centaur.software/7-advanced-tools/extract-from-table.md): 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 patients - Patient information a_cancellation_list - Cancellation records treat - Treatment records Performance: Timeout: 180 seconds (3 minutes) Optimised for large datasets Automatic job creation for >100K rows
• [8. Job Management](https://developer.cipherstream.centaur.software/8-job-management.md): 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: Queued - Job created and waiting for available processing resources Running - Data extraction in progress with real-time progress updates Completed - Data successfully extracted and available for download via S3 Failed - Error occurred during processing with detailed error information 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
  • [List Jobs](https://developer.cipherstream.centaur.software/8-job-management/list-jobs.md): Job Listing : Retrieve a comprehensive list of your background jobs with advanced filtering and sorting. Status Values Title Description Title Attribute Type Description queued string Job created and waiting for processing resources running string Data extraction in progress completed string Job finished successfully and download URL available failed string Error occurred during processing cancelled string Job cancelled by user or timeout Response Fields Title Description Title Attribute Type Description job_id string Unique job identifier for tracking and reference procedure_name string Name of the data extraction procedure status string Current job status progress_percent number Completion percentage from 0 to 100 rows_processed integer Current number of records processed estimated_rows integer Estimated total number of rows to process estimated_completion_seconds integer Estimated time to completion in seconds created_at date-time Job creation timestamp started_at date-time Job processing start timestamp completed_at date-time Job completion timestamp execution_time_seconds number Total processing time in seconds file_size_bytes integer Size of generated output file in bytes output_format string Requested output format compression string Applied compression type s3_url string Secure download URL expires_at date-time Download URL expiry timestamp error_message string Error information retry_count integer 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
  • [Get Job Status](https://developer.cipherstream.centaur.software/8-job-management/get-job-status.md): Job Details : Get detailed status and progress information for a specific job. Attributes Title Description Title Attribute Type Description job_id string Job identifier status string Current job status customer_id string Customer identifier job_type string Job type created_at date-time Time the job was created started_at date-time Time the job started completed_at date-time Time the job completed progress_percent number Progress percentage rows_processed integer Rows processed total_rows integer Total rows in scope file_size_bytes integer Export file size in bytes s3_url string Pre-signed S3 download URL expires_at date-time S3 URL expiry time error_message string Error message execution_time_seconds number Execution time in seconds webhook_status string Webhook delivery status metadata object Additional metadata fields: endpoint, estimated_rows, estimated_size_mb, reason
  • [Cancel Job](https://developer.cipherstream.centaur.software/8-job-management/cancel-job.md): 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
• [9. Integrations](https://developer.cipherstream.centaur.software/9-integrations.md): ## 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
  • [Configure Webhook](https://developer.cipherstream.centaur.software/9-integrations/configure-webhook.md): Webhook Configuration : Set up comprehensive webhook URL and event subscriptions with advanced security and delivery options. 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) Advanced Configuration Example: Plain text { "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: Return HTTP status 200-299 for successful processing Respond within the configured timeout period Validate the HMAC signature if secret is configured Handle duplicate deliveries gracefully (idempotent processing) 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
  • [Test Webhook](https://developer.cipherstream.centaur.software/9-integrations/test-webhook.md): Webhook Testing : Send a test ping to verify your webhook endpoint. Expected Response: Your endpoint should return HTTP 200-299 status code.
  • [List Webhook Deliveries](https://developer.cipherstream.centaur.software/9-integrations/list-webhook-deliveries.md): 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)
  • [Retry Failed Webhook](https://developer.cipherstream.centaur.software/9-integrations/retry-failed-webhook.md): Manual Retry : Manually retry a failed webhook delivery. Use Cases: Retry after fixing endpoint issues Force delivery of important notifications Testing webhook endpoint changes
• [10. Documentation](https://developer.cipherstream.centaur.software/10-documentation.md): ## 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
  • [OpenAPI Schema](https://developer.cipherstream.centaur.software/10-documentation/openapi-schema.md): 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