Skip to main content

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.

The architectural overview when connecting OpenAI to Propel.

Features​

OpenAI Data Pools supports the following features:

Feature nameSupportedNotes
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 deletesâś…Via the Delete Job API.
Bulk insertâś…Up to 500 events per HTTP request.
API configurableâś…See API reference 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.

A screenshot of Propel's OpenAI Data Pool.

The OpenAI Data Pool has the following columns:

ColumnTypeDescription
_propel_received_atTIMESTAMPAuto-generated. The timestamp when the event was collected in UTC.
_propel_payloadJSONAuto-generated. The JSON Payload of the event.
response_idSTRINGRequired. The unique ID for the chat completion returned by the OpenAI API.
response_objectSTRINGRequired. The object type returned by the OpenAI API.
response_createdTIMESTAMPRequired. The date created returned by the OpenAI API.
response_modelSTRINGRequired. The model returned by the OpenAI API.
response_prompt_tokensINT64Required. The number of tokens consumed by the prompt returned by the OpenAI API.
response_completion_tokensINT64Required. The number of tokens returned by the response returned by the OpenAI API.
response_total_tokensINT64Required. The total number of tokens consumed in the prompt and response returned by the OpenAI API.
response_finished_reasonSTRINGOptional. The finished reason returned by the OpenAI API.
response_contentSTRINGOptional. The response content returned by the OpenAI API.
response_choicesJSONOptional. The choices returned by the OpenAI API.
response_headers_openai_organizationSTRINGOptional. The OpenAI organization returned in the headers by the OpenAI API.
response_headers_openai_versionSTRINGOptional. The version returned in the headers by the OpenAI API.
response_headers_openai_processing_msINT64Optional. The latency in milliseconds returned in the headers by the OpenAI API.
response_headers_openai_ratelimit_limit_requestsINT64Optional. The request limit returned in the headers by the OpenAI API.
response_headers_openai_ratelimit_limit_tokensINT64Optional. The token limit returned in the headers by the OpenAI API.
response_headers_openai_ratelimit_remaining_requestsINT64Optional. The remaining requests returned in the headers by the OpenAI API.
response_headers_openai_ratelimit_remaining_tokensINT64Optional. The remaining tokens returned in the headers by the OpenAI API.
response_headers_openai_ratelimit_reset_requests_msINT64Optional. The milliseconds remaining to reset the request limit returned in the headers by the OpenAI API.
response_headers_openai_ratelimit_reset_tokens_msINT64Optional. The milliseconds remaining to reset the token limit returned in the headers by the OpenAI API.
request_prompt_messagesJSONOptional. The prompt messages sent to OpenAI in the API request.
request_max_tokensINT64Optional. The max_token parameter sent to OpenAI in the API request.
request_temperatureFLOATOptional. The temperature parameter sent to OpenAI in the API request.
request_presence_penaltyFLOATOptional. The presence_penalty parameter sent to OpenAI in the API request.
request_top_pFLOATOptional. The top_p parameter sent to OpenAI in the API request.
request_frequency_penaltyFLOATOptional. The frequency_penalty parameter sent to OpenAI in the API request.
request_logit_biasJSONOptional. The logit_bias parameter sent to OpenAI in the API request.
request_stopJSONOptional. The stop parameter sent to OpenAI in the API request.
request_nINT64Optional. 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.

A screenshot of the Webhook Data Pool authentication setup in the Propel Console

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 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
}'

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.

Real-time updates​

The OpenAI Data Pool supports real-time updates. It uses each event's primary key, consisting of the created timestamp and the unique ID, to determine whether it should be inserted or updated within the Data Pool. This also means that if you send the same record twice, it will not be duplicated.

Updates are useful in the following scenarios:

  • When retrying requests: You can safely retry requests without worrying about creating duplicates.
  • When updating the properties of the event: For example, if you need to update the metadata object, you can post the event again with the updated metadata. The entire event will be updated.

Real-time deletes​

The OpenAI Data Pool supports real-time deletes. It uses each record's primary key, consisting of the primary timestamp and a unique ID, to determine whether it should be inserted or deleted within the Data Pool.

To delete a record, make an HTTP DELETE request to the OpenAI Data Pool URL with the primary timestamp and unique ID of the record you want to update.

The following example shows how to delete a record with a timestamp of "1684517376" and a unique ID of "chatcmpl-7HyD2Hdb8j7T2lMsn5FE1SpcTR9mV".

curl https://webhooks.us-east-2.propeldata.com/v1/WHK... \
  -X DELETE \
  -H "Content-Type: application/json" \
  -d '{
          "response_id": "chatcmpl-7HyD2Hdb8j7T2lMsn5FE1SpcTR9mV",
          "response_created": 1684517376
  }'

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.

MetricTypeDescription
Total token usageSUMThe sum of the response_total_tokens values.
Prompt token usageSUMThe sum of the response_prompt_tokens values.
Completion token usageSUMThe sum of the response_completion_tokens values.
Event countCOUNTThe number of requests.
Average latencyAVERAGEThe average of the response_headers_openai_processing_ms values.
P95 latencyCUSTOMThe 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.

A screen capture of where to add a column in the Data Pool schema page.

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.

A screen capture of the fields to add a column.

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.

A screen capture of the add column job in the Data Pool operations page.

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 typePropel type
StringSTRING
NumberDOUBLE
ObjectJSON
ArrayJSON
BooleanBOOLEAN
NullJSON

Limits​

  • Up to 500 events (as a JSON array) can be sent for each POST.

Key guides​

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.

API reference documentation​

Below is the relevant API documentation for the Webhook Data Pool.

Queries​

Mutations​

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.