OpenAI
The OpenAI Data Pool lets you easily collect OpenAI ChatGPT API usage events from your application. Once the events are in the Data Pool, you can use them to power usage metering, customer-facing dashboards, reports, and data-driven workflows.
Consider using the OpenAI Data Pool when:
- You need to meter and track OpenAI API and ChatGPT token usage.
- You need to be able to retrieve that usage data via an API from your application.
- You must enforce limits, bill, and tailor the product experience based on token usage.
- You need to track additional metadata along with the events sent by OpenAI
Architecture overview​
OpenAI Data Pools provide an HTTP URL to which you can send usage events from your application.
Features​
OpenAI Data Pools supports the following features:
Feature name | Supported | Notes |
---|---|---|
Event collection | âś… | Collects events in JSON format. |
Real-time updates | âś… | See the Real-time updates section. |
Real-time deletes | âś… | See the Real-time deletes section. |
Batch Delete API | âś… | See Batch Delete API. |
Batch Update API | âś… | See Batch Update API. |
Bulk insert | âś… | Up to 500 events per HTTP request. |
API configurable | âś… | See Management API docs. |
Terraform configurable | âś… | See Propel Terraform docs. |
How does the OpenAI Data Pool work?​
The OpenAI Data Pool can collect non-streaming, streaming, and function-calling events from the OpenAI API. It implements the Webhook Data Pool with a specific schema to capture OpenAI usage events.
While setting up an OpenAI Data Pool, you can optionally add columns to the schema to capture your application's metadata. For instance, you could add a “customerId” or “userId” column to identify your applications's end users.
The OpenAI Data Pool has the following columns:
Column | Type | Description |
---|---|---|
_propel_received_at | TIMESTAMP | Auto-generated. The timestamp when the event was collected in UTC. |
_propel_payload | JSON | Auto-generated. The JSON Payload of the event. |
response_id | STRING | Required. The unique ID for the chat completion returned by the OpenAI API. |
response_object | STRING | Required. The object type returned by the OpenAI API. |
response_created | TIMESTAMP | Required. The date created returned by the OpenAI API. |
response_model | STRING | Required. The model returned by the OpenAI API. |
response_prompt_tokens | INT64 | Required. The number of tokens consumed by the prompt returned by the OpenAI API. |
response_completion_tokens | INT64 | Required. The number of tokens returned by the response returned by the OpenAI API. |
response_total_tokens | INT64 | Required. The total number of tokens consumed in the prompt and response returned by the OpenAI API. |
response_finished_reason | STRING | Optional. The finished reason returned by the OpenAI API. |
response_content | STRING | Optional. The response content returned by the OpenAI API. |
response_choices | JSON | Optional. The choices returned by the OpenAI API. |
response_headers_openai_organization | STRING | Optional. The OpenAI organization returned in the headers by the OpenAI API. |
response_headers_openai_version | STRING | Optional. The version returned in the headers by the OpenAI API. |
response_headers_openai_processing_ms | INT64 | Optional. The latency in milliseconds returned in the headers by the OpenAI API. |
response_headers_openai_ratelimit_limit_requests | INT64 | Optional. The request limit returned in the headers by the OpenAI API. |
response_headers_openai_ratelimit_limit_tokens | INT64 | Optional. The token limit returned in the headers by the OpenAI API. |
response_headers_openai_ratelimit_remaining_requests | INT64 | Optional. The remaining requests returned in the headers by the OpenAI API. |
response_headers_openai_ratelimit_remaining_tokens | INT64 | Optional. The remaining tokens returned in the headers by the OpenAI API. |
response_headers_openai_ratelimit_reset_requests_ms | INT64 | Optional. The milliseconds remaining to reset the request limit returned in the headers by the OpenAI API. |
response_headers_openai_ratelimit_reset_tokens_ms | INT64 | Optional. The milliseconds remaining to reset the token limit returned in the headers by the OpenAI API. |
request_prompt_messages | JSON | Optional. The prompt messages sent to OpenAI in the API request. |
request_max_tokens | INT64 | Optional. The max_token parameter sent to OpenAI in the API request. |
request_temperature | FLOAT | Optional. The temperature parameter sent to OpenAI in the API request. |
request_presence_penalty | FLOAT | Optional. The presence_penalty parameter sent to OpenAI in the API request. |
request_top_p | FLOAT | Optional. The top_p parameter sent to OpenAI in the API request. |
request_frequency_penalty | FLOAT | Optional. The frequency_penalty parameter sent to OpenAI in the API request. |
request_logit_bias | JSON | Optional. The logit_bias parameter sent to OpenAI in the API request. |
request_stop | JSON | Optional. The stop parameter sent to OpenAI in the API request. |
request_n | INT64 | Optional. The n parameter sent to OpenAI in the API request. |
Authentication​
Optionally, you can add basic authentication to your HTTP endpoint to secure your URL. If these parameters are not provided, anyone with the URL to your webhook can post data. While testing without HTTP Basic authentication is okay. We recommend enabling it.
Sending non-streaming and function-calling usage events​
You can send non-streaming and function-calling events to the OpenAI Data Pool by making an HTTP POST request to the Data Pool URL with the JSON event in the request body.
The examples below demonstrate sending the event using cURL, Node.js, and Python.
- cURL
- Node.js
- Python
curl https://webhooks.us-east-2.propeldata.com/v1/WHK... \
-X POST \
-H "Content-Type: application/json" \
-d '{
"response_id": "chatcmpl-7HyD2Hdb8j7T2lMsn5FE1SpcTR9mV",
"response_object": "chat.completion",
"response_created": 1684517376,
"response_model": "gpt-4-0314",
"response_prompt_tokens": 23,
"response_completion_tokens": 100,
"response_total_tokens": 123,
"response_finished_reason": "length",
"response_content": "The most popular taco in the world is Al Pastor.",
"response_choices": [{
"message": {
"role": "assistant",
"content": "The most popular taco in the world is Al Pastor."
},
"finish_reason": "length",
"index": 0
},
{
"message": {
"role": "assistant",
"content": "It is difficult to tell with certainty, but it seems it is Al Pastor."
},
"finish_reason": "length",
"index": 1
}],
"response_headers_openai_organization": "propel-1",
"response_headers_openai_version": "2020-10-01",
"response_headers_openai_processing_ms": 10705,
"response_headers_openai_ratelimit_limit_requests": 200,
"response_headers_openai_ratelimit_limit_tokens": 40000,
"response_headers_openai_ratelimit_remaining_requests": 199,
"response_headers_openai_ratelimit_remaining_tokens": 39868,
"response_headers_openai_ratelimit_reset_requests_ms": 300,
"response_headers_openai_ratelimit_reset_tokens_ms": 198,
"request_prompt_messages": [{
"role": "system",
"content": "You are a helpful assistant."
},{
"role": "user",
"content": "What is France famous for?"
}],
"request_max_tokens": 50,
"request_temperature": 0,
"request_presence_penalty": null,
"request_top_p": null,
"request_frequency_penalty": null,
"request_logit_bias": {},
"request_stop": [],
"request_n": 2
}'
const fetch = require('node-fetch')
const url = 'https://webhooks.us-east-2.propeldata.com/v1/WHK...'
const data = {
response_id: 'chatcmpl-7HyD2Hdb8j7T2lMsn5FE1SpcTR9mV',
response_object: 'chat.completion',
response_created: 1684517376,
response_model: 'gpt-4-0314',
response_prompt_tokens: 23,
response_completion_tokens: 100,
response_total_tokens: 123,
response_finished_reason: 'length',
response_content: 'The most popular taco in the world is Al Pastor.',
response_choices: [
{
message: {
role: 'assistant',
content: 'The most popular taco in the world is Al Pastor.'
},
finish_reason: 'length',
index: 0
},
{
message: {
role: 'assistant',
content:
'It is difficult to tell with certainty, but it seems it is Al Pastor.'
},
finish_reason: 'length',
index: 1
}
],
response_headers_openai_organization: 'propel-1',
response_headers_openai_version: '2020-10-01',
response_headers_openai_processing_ms: 10705,
response_headers_openai_ratelimit_limit_requests: 200,
response_headers_openai_ratelimit_limit_tokens: 40000,
response_headers_openai_ratelimit_remaining_requests: 199,
response_headers_openai_ratelimit_remaining_tokens: 39868,
response_headers_openai_ratelimit_reset_requests_ms: 300,
response_headers_openai_ratelimit_reset_tokens_ms: 198,
request_prompt_messages: [
{
role: 'system',
content: 'You are a helpful assistant.'
},
{
role: 'user',
content: 'What is France famous for?'
}
],
request_max_tokens: 50,
request_temperature: 0,
request_presence_penalty: null,
request_top_p: null,
request_frequency_penalty: null,
request_logit_bias: {},
request_stop: [],
request_n: 2
}
fetch(url, {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify(data)
})
.then((response) => response.json())
.then((data) => {
console.log('Success:', data)
})
.catch((error) => {
console.error('Error:', error)
})
import requests
import json
url = 'https://webhooks.us-east-2.propeldata.com/v1/WHK...'
data = {
"response_id": "chatcmpl-7HyD2Hdb8j7T2lMsn5FE1SpcTR9mV",
"response_object": "chat.completion",
"response_created": 1684517376,
"response_model": "gpt-4-0314",
"response_prompt_tokens": 23,
"response_completion_tokens": 100,
"response_total_tokens": 123,
"response_finished_reason": "length",
"response_content": "The most popular taco in the world is Al Pastor.",
"response_choices": [
{
"message": {
"role": "assistant",
"content": "The most popular taco in the world is Al Pastor."
},
"finish_reason": "length",
"index": 0
},
{
"message": {
"role": "assistant",
"content": "It is difficult to tell with certainty, but it seems it is Al Pastor."
},
"finish_reason": "length",
"index": 1
}
],
"response_headers_openai_organization": "propel-1",
"response_headers_openai_version": "2020-10-01",
"response_headers_openai_processing_ms": 10705,
"response_headers_openai_ratelimit_limit_requests": 200,
"response_headers_openai_ratelimit_limit_tokens": 40000,
"response_headers_openai_ratelimit_remaining_requests": 199,
"response_headers_openai_ratelimit_remaining_tokens": 39868,
"response_headers_openai_ratelimit_reset_requests_ms": 300,
"response_headers_openai_ratelimit_reset_tokens_ms": 198,
"request_prompt_messages": [
{
"role": "system",
"content": "You are a helpful assistant."
},
{
"role": "user",
"content": "What is France famous for?"
}
],
"request_max_tokens": 50,
"request_temperature": 0,
"request_presence_penalty": None,
"request_top_p": None,
"request_frequency_penalty": None,
"request_logit_bias": {},
"request_stop": [],
"request_n": 2
}
headers = {'Content-Type': 'application/json'}
response = requests.post(url, headers=headers, data=json.dumps(data))
if response.status_code == 200:
print('Success:', response.json())
else:
print('Error:', response.text)
Sending streaming usage events​
The OpenAI streaming API returns data-only server-sent events as soon as they are available. The challenge is that the data-only response doesn't include token usage metadata. To track and attribute token usage, you must tokenize messages and count usage yourself.
The example below demonstrates how to process the streamed chunks of a chat completion response and count the tokens to publish the event to Propel.
// Import necessary libraries
const openai = require('openai');
const tiktoken = require('js-tiktoken');
const model = 'gpt-3.5-turbo';
const enc = tiktoken.encodingForModel(model);
let completionTokens = 0;
let totalTokens = 0;
const messages: Array<ChatCompletionMessageParam> = [
{ role: 'user', content: 'Say Hello World!' },
];
// Calculate the Prompt tokens
const promptTokens = messages.reduce((total, msg) => total + enc.encode(msg.content ?? ').length,0,);
const stream = await openai.chat.completions.create({
model,
messages: messages,
stream: true,
});
// Process steam chunks and calculate completion tokens
for await (const chunk of stream) {
const content = chunk.choices[0]?.delta?.content || '';
const tokenList = enc.encode(content);
completionTokens += tokenList.length;
console.log(content);
}
totalTokens = completionTokens + promptTokens;
Once you have the completionTokens
, promptTokens
, and totalTokens
, you can use the same code in the non-streaming example to send the events.
Metrics​
When you create an OpenAI Data Pool, Propel will automatically create the following Metrics for you. You can always define additional Metrics or modify the existing ones to meet your needs.
Metric | Type | Description |
---|---|---|
Total token usage | SUM | The sum of the response_total_tokens values. |
Prompt token usage | SUM | The sum of the response_prompt_tokens values. |
Completion token usage | SUM | The sum of the response_completion_tokens values. |
Event count | COUNT | The number of requests. |
Average latency | AVERAGE | The average of the response_headers_openai_processing_ms values. |
P95 latency | CUSTOM | The 95th percentile of the response_headers_openai_processing_ms values. |
Schema changes​
The OpenAI Data Pool, at its core, handles semi-structured schema-less JSON data. That means you can add new properties to your payload whenever necessary. The entire payload will always be available in the _propel_payload
column.
Propel will enforce the schema for required fields. Thus, if you stop providing data for a field that was previously unpacked into its own column and marked as required when creating the Data Pool, Propel will return an error.
Adding Columns​
At any point, you can add columns to your OpenAI Data Pool to evolve your schema with non-breaking changes.
Once you click the “Add Column” button, you will be able to define the new column. The fields includes the JSON property to extract from the event, the name of the new column, and the type.
Clicking “Add Column” starts an asynchronous operation to add the column to the Data Pool. You can monitor the progress of the operation in the “Operations” tab.
Note that when you add a column, Propel will not backfill. To backfill existing rows, you can run a batch update operation.
Column deletions, column modifications, and data type changes are not supported because they are breaking changes to the schema.
Data types​
The table below describes default data type mappings from JSON to Propel types.
JSON type | Propel type |
---|---|
String | STRING |
Number | DOUBLE |
Object | JSON |
Array | JSON |
Boolean | BOOLEAN |
Null | JSON |
Limits​
- Up to 500 events (as a JSON array) can be sent for each POST.
Key guides​
- Querying JSON data
- Understanding and configuring Tenant ID for your data pool
- Defining Metrics with JSON Fields
Frequently Asked Questions​
How long does it take for an event to be available via the API?
When an event is collected, the data will be available in Propel and served via the API in 1-3 minutes.
Management API​
Below is the relevant API documentation for the Webhook Data Pool.
Queries​
Mutations​
- Create Data Pool
- Modify Data Pool
- Delete Data Pool by ID
- Delete Data Pool by unique name
- Create Webhook Data Source
- Modify Webhook Data Source
- Delete Data Source by ID
- Delete Data Source by unique name
Limits​
- Up to 500 events (as a JSON array) can be sent for each POST.
- The payload can be up to 1,048,320 bytes.