> ## Documentation Index
> Fetch the complete documentation index at: https://docs.apinizer.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Apinizer Log Table Creation Commands
> You can create the necessary database tables to store traffic and audit records using DB Connector, with appropriate SQL commands for Oracle, MySQL/MariaDB, PostgreSQL, and SQL Server databases.
The DDL commands on this page use Apinizer's default table names (`log_ApiProxyTraffic`, `log_PolicyCapture`, `log_AuditEvent`, `log_Application`, `log_UnsentMessage`, `log_TokenTraffic`, `log_LoginLog`) and no schema prefix.
If you prefer a custom schema or custom table names, configure them in the **Log Target Usage — Table Schema** section of the connection definition. You can rename the table or add a schema prefix in your DDL, but **column names and data types must match exactly** — otherwise log inserts will fail with column-not-found errors.
Especially on Oracle, if you use case-sensitive table names (e.g. `CREATE TABLE "HR"."logtablosu"`), you must enter the table name inside double quotes in the connection definition.
See [Database Connection](/en/integrations/connection-management/database) for details.
Apinizer can store traffic and audit records in relational databases using DB Connector. The following tables are prepared with appropriate data types for each supported database type.
Column names are identical across all database types (snake\_case). Only the data types differ per database.
## API Proxy Traffic Log
The main table where all HTTP traffic passing through API Proxy is recorded.
```sql theme={null}
CREATE TABLE log_ApiProxyTraffic (
id VARCHAR2(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
correlation_id VARCHAR2(255),
environment_id VARCHAR2(255),
project_id VARCHAR2(255),
application_id VARCHAR2(255),
application_name VARCHAR2(255),
api_proxy_id VARCHAR2(255),
api_proxy_name VARCHAR2(255),
enum_api_type VARCHAR2(255),
proxy_relative_path VARCHAR2(255),
proxy_method_id VARCHAR2(255),
proxy_method_name VARCHAR2(255),
x_forwarded_for VARCHAR2(255),
username_or_key VARCHAR2(255),
req_context_path VARCHAR2(255),
req_remote_addr VARCHAR2(255),
req_http_method VARCHAR2(255),
req_content_type VARCHAR2(255),
req_path_info VARCHAR2(255),
req_query_string VARCHAR2(255),
req_remote_user VARCHAR2(255),
req_session_id VARCHAR2(255),
req_request_uri VARCHAR2(255),
req_char_encoding VARCHAR2(255),
req_content_length NUMBER(10),
req_protocol VARCHAR2(255),
req_scheme VARCHAR2(255),
req_server_name VARCHAR2(255),
req_server_port NUMBER(10),
req_remote_host VARCHAR2(255),
req_remote_port NUMBER(10),
req_local_name VARCHAR2(255),
req_local_addr VARCHAR2(255),
req_local_port NUMBER(10),
from_client_ro_param CLOB,
from_client_ro_header CLOB,
from_client_ro_body CLOB,
to_backend_param CLOB,
to_backend_header CLOB,
to_backend_body CLOB,
routing_address VARCHAR2(255),
routing_retry_count NUMBER(10),
routing_failover_cnt NUMBER(10),
from_backend_ro_header CLOB,
from_backend_ro_body CLOB,
to_client_header CLOB,
to_client_body CLOB,
time_req_pipeline NUMBER(10),
time_resp_pipeline NUMBER(10),
time_routing_total NUMBER(10),
time_api_proxy_total NUMBER(10),
size_request_total NUMBER(10),
size_response_total NUMBER(10),
cache_hit NUMBER(1),
status_code NUMBER(10),
error_type VARCHAR2(255),
parent_error_type VARCHAR2(255),
result_type VARCHAR2(255),
location_lat NUMBER,
location_lon NUMBER,
is_canary_request NUMBER(1),
canary_routing_status VARCHAR2(255),
mirror_count NUMBER(10),
mirror_success_count NUMBER(10),
mirror_failure_count NUMBER(10),
mirror_total_duration NUMBER(19),
mirror_addresses CLOB
);
```
```sql theme={null}
CREATE TABLE log_ApiProxyTraffic (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP NULL,
correlation_id VARCHAR(255),
environment_id VARCHAR(255),
project_id VARCHAR(255),
application_id VARCHAR(255),
application_name VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
enum_api_type VARCHAR(255),
proxy_relative_path VARCHAR(255),
proxy_method_id VARCHAR(255),
proxy_method_name VARCHAR(255),
x_forwarded_for VARCHAR(255),
username_or_key VARCHAR(255),
req_context_path VARCHAR(255),
req_remote_addr VARCHAR(255),
req_http_method VARCHAR(255),
req_content_type VARCHAR(255),
req_path_info VARCHAR(255),
req_query_string VARCHAR(255),
req_remote_user VARCHAR(255),
req_session_id VARCHAR(255),
req_request_uri VARCHAR(255),
req_char_encoding VARCHAR(255),
req_content_length INT,
req_protocol VARCHAR(255),
req_scheme VARCHAR(255),
req_server_name VARCHAR(255),
req_server_port INT,
req_remote_host VARCHAR(255),
req_remote_port INT,
req_local_name VARCHAR(255),
req_local_addr VARCHAR(255),
req_local_port INT,
from_client_ro_param TEXT,
from_client_ro_header TEXT,
from_client_ro_body LONGTEXT,
to_backend_param TEXT,
to_backend_header TEXT,
to_backend_body LONGTEXT,
routing_address VARCHAR(255),
routing_retry_count INT,
routing_failover_cnt INT,
from_backend_ro_header TEXT,
from_backend_ro_body LONGTEXT,
to_client_header TEXT,
to_client_body LONGTEXT,
time_req_pipeline INT,
time_resp_pipeline INT,
time_routing_total INT,
time_api_proxy_total INT,
size_request_total INT,
size_response_total INT,
cache_hit TINYINT(1),
status_code INT,
error_type VARCHAR(255),
parent_error_type VARCHAR(255),
result_type VARCHAR(255),
location_lat DOUBLE,
location_lon DOUBLE,
is_canary_request TINYINT(1),
canary_routing_status VARCHAR(255),
mirror_count INT,
mirror_success_count INT,
mirror_failure_count INT,
mirror_total_duration BIGINT,
mirror_addresses TEXT
);
```
```sql theme={null}
CREATE TABLE log_ApiProxyTraffic (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
correlation_id VARCHAR(255),
environment_id VARCHAR(255),
project_id VARCHAR(255),
application_id VARCHAR(255),
application_name VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
enum_api_type VARCHAR(255),
proxy_relative_path VARCHAR(255),
proxy_method_id VARCHAR(255),
proxy_method_name VARCHAR(255),
x_forwarded_for VARCHAR(255),
username_or_key VARCHAR(255),
req_context_path VARCHAR(255),
req_remote_addr VARCHAR(255),
req_http_method VARCHAR(255),
req_content_type VARCHAR(255),
req_path_info VARCHAR(255),
req_query_string VARCHAR(255),
req_remote_user VARCHAR(255),
req_session_id VARCHAR(255),
req_request_uri VARCHAR(255),
req_char_encoding VARCHAR(255),
req_content_length INTEGER,
req_protocol VARCHAR(255),
req_scheme VARCHAR(255),
req_server_name VARCHAR(255),
req_server_port INTEGER,
req_remote_host VARCHAR(255),
req_remote_port INTEGER,
req_local_name VARCHAR(255),
req_local_addr VARCHAR(255),
req_local_port INTEGER,
from_client_ro_param TEXT,
from_client_ro_header TEXT,
from_client_ro_body TEXT,
to_backend_param TEXT,
to_backend_header TEXT,
to_backend_body TEXT,
routing_address VARCHAR(255),
routing_retry_count INTEGER,
routing_failover_cnt INTEGER,
from_backend_ro_header TEXT,
from_backend_ro_body TEXT,
to_client_header TEXT,
to_client_body TEXT,
time_req_pipeline INTEGER,
time_resp_pipeline INTEGER,
time_routing_total INTEGER,
time_api_proxy_total INTEGER,
size_request_total INTEGER,
size_response_total INTEGER,
cache_hit BOOLEAN,
status_code INTEGER,
error_type VARCHAR(255),
parent_error_type VARCHAR(255),
result_type VARCHAR(255),
location_lat DOUBLE PRECISION,
location_lon DOUBLE PRECISION,
is_canary_request BOOLEAN,
canary_routing_status VARCHAR(255),
mirror_count INTEGER,
mirror_success_count INTEGER,
mirror_failure_count INTEGER,
mirror_total_duration BIGINT,
mirror_addresses TEXT
);
```
```sql theme={null}
CREATE TABLE log_ApiProxyTraffic (
id NVARCHAR(255) PRIMARY KEY,
log_timestamp DATETIME2,
correlation_id NVARCHAR(255),
environment_id NVARCHAR(255),
project_id NVARCHAR(255),
application_id NVARCHAR(255),
application_name NVARCHAR(255),
api_proxy_id NVARCHAR(255),
api_proxy_name NVARCHAR(255),
enum_api_type NVARCHAR(255),
proxy_relative_path NVARCHAR(255),
proxy_method_id NVARCHAR(255),
proxy_method_name NVARCHAR(255),
x_forwarded_for NVARCHAR(255),
username_or_key NVARCHAR(255),
req_context_path NVARCHAR(255),
req_remote_addr NVARCHAR(255),
req_http_method NVARCHAR(255),
req_content_type NVARCHAR(255),
req_path_info NVARCHAR(255),
req_query_string NVARCHAR(255),
req_remote_user NVARCHAR(255),
req_session_id NVARCHAR(255),
req_request_uri NVARCHAR(255),
req_char_encoding NVARCHAR(255),
req_content_length INT,
req_protocol NVARCHAR(255),
req_scheme NVARCHAR(255),
req_server_name NVARCHAR(255),
req_server_port INT,
req_remote_host NVARCHAR(255),
req_remote_port INT,
req_local_name NVARCHAR(255),
req_local_addr NVARCHAR(255),
req_local_port INT,
from_client_ro_param NVARCHAR(MAX),
from_client_ro_header NVARCHAR(MAX),
from_client_ro_body NVARCHAR(MAX),
to_backend_param NVARCHAR(MAX),
to_backend_header NVARCHAR(MAX),
to_backend_body NVARCHAR(MAX),
routing_address NVARCHAR(255),
routing_retry_count INT,
routing_failover_cnt INT,
from_backend_ro_header NVARCHAR(MAX),
from_backend_ro_body NVARCHAR(MAX),
to_client_header NVARCHAR(MAX),
to_client_body NVARCHAR(MAX),
time_req_pipeline INT,
time_resp_pipeline INT,
time_routing_total INT,
time_api_proxy_total INT,
size_request_total INT,
size_response_total INT,
cache_hit BIT,
status_code INT,
error_type NVARCHAR(255),
parent_error_type NVARCHAR(255),
result_type NVARCHAR(255),
location_lat FLOAT,
location_lon FLOAT,
is_canary_request BIT,
canary_routing_status NVARCHAR(255),
mirror_count INT,
mirror_success_count INT,
mirror_failure_count INT,
mirror_total_duration BIGINT,
mirror_addresses NVARCHAR(MAX)
);
```
| Column | Description |
| ------------------------- | ------------------------------------------------------------------------ |
| id | Record identifier |
| log\_timestamp | Time the log record was created |
| correlation\_id | Request-specific correlation ID; links records in the same request chain |
| environment\_id | Identifier of the environment where the request was processed |
| project\_id | Identifier of the project the API belongs to |
| application\_id | Identifier of the application making the request |
| application\_name | Name of the application making the request |
| api\_proxy\_id | Identifier of the API Proxy used for the request |
| api\_proxy\_name | Name of the API Proxy used for the request |
| enum\_api\_type | API type (REST, SOAP, etc.) |
| proxy\_relative\_path | Relative path of the API Proxy |
| proxy\_method\_id | Identifier of the matched method |
| proxy\_method\_name | Name of the matched method |
| x\_forwarded\_for | Real client IP address (proxy/load balancer chain) |
| username\_or\_key | Username or API key used for authentication |
| req\_context\_path | Request context path |
| req\_remote\_addr | Remote address of the request |
| req\_http\_method | HTTP method (GET, POST, PUT, etc.) |
| req\_content\_type | Content type of the request |
| req\_path\_info | Request path info |
| req\_query\_string | Request query string |
| req\_remote\_user | Remote user |
| req\_session\_id | Session identifier |
| req\_request\_uri | Request URI |
| req\_char\_encoding | Request character encoding |
| req\_content\_length | Request content length (bytes) |
| req\_protocol | Protocol |
| req\_scheme | Scheme (http/https) |
| req\_server\_name | Server name |
| req\_server\_port | Server port |
| req\_remote\_host | Remote host |
| req\_remote\_port | Remote port |
| req\_local\_name | Local name |
| req\_local\_addr | Local address |
| req\_local\_port | Local port |
| from\_client\_ro\_param | Query parameters from the client (JSON) |
| from\_client\_ro\_header | Headers from the client (JSON) |
| from\_client\_ro\_body | Request body from the client |
| to\_backend\_param | Query parameters sent to the backend (JSON) |
| to\_backend\_header | Headers sent to the backend (JSON) |
| to\_backend\_body | Request body sent to the backend |
| routing\_address | Backend address routed to |
| routing\_retry\_count | Number of retry attempts |
| routing\_failover\_cnt | Number of failovers |
| from\_backend\_ro\_header | Response headers from the backend (JSON) |
| from\_backend\_ro\_body | Response body from the backend |
| to\_client\_header | Response headers sent to the client (JSON) |
| to\_client\_body | Response body sent to the client |
| time\_req\_pipeline | Request pipeline duration (ms) |
| time\_resp\_pipeline | Response pipeline duration (ms) |
| time\_routing\_total | Total routing duration (ms) |
| time\_api\_proxy\_total | Total API Proxy processing duration (ms) |
| size\_request\_total | Request size (bytes) |
| size\_response\_total | Response size (bytes) |
| cache\_hit | Whether the response was served from cache |
| status\_code | HTTP response status code |
| error\_type | Error type |
| parent\_error\_type | Parent error type |
| result\_type | Processing result |
| location\_lat | Geographic location — latitude |
| location\_lon | Geographic location — longitude |
| is\_canary\_request | Whether this is a canary routing request |
| canary\_routing\_status | Canary routing status |
| mirror\_count | Number of mirror requests |
| mirror\_success\_count | Number of successful mirror requests |
| mirror\_failure\_count | Number of failed mirror requests |
| mirror\_total\_duration | Total mirror request duration (ms) |
| mirror\_addresses | Mirror addresses (JSON) |
### Recommended Indexes
This is the highest-volume log table. The following indexes cover the most common query patterns (end-to-end tracing, time-range scans, proxy/project filtering, error analysis).
```sql theme={null}
CREATE INDEX idx_apitraffic_correlation ON log_ApiProxyTraffic(correlation_id);
CREATE INDEX idx_apitraffic_timestamp ON log_ApiProxyTraffic(log_timestamp);
CREATE INDEX idx_apitraffic_proxy ON log_ApiProxyTraffic(api_proxy_id);
CREATE INDEX idx_apitraffic_project ON log_ApiProxyTraffic(project_id);
CREATE INDEX idx_apitraffic_status ON log_ApiProxyTraffic(status_code);
CREATE INDEX idx_apitraffic_result ON log_ApiProxyTraffic(result_type);
```
```sql theme={null}
CREATE INDEX idx_apitraffic_correlation ON log_ApiProxyTraffic(correlation_id);
CREATE INDEX idx_apitraffic_timestamp ON log_ApiProxyTraffic(log_timestamp);
CREATE INDEX idx_apitraffic_proxy ON log_ApiProxyTraffic(api_proxy_id);
CREATE INDEX idx_apitraffic_project ON log_ApiProxyTraffic(project_id);
CREATE INDEX idx_apitraffic_status ON log_ApiProxyTraffic(status_code);
CREATE INDEX idx_apitraffic_result ON log_ApiProxyTraffic(result_type);
```
```sql theme={null}
CREATE INDEX idx_apitraffic_correlation ON log_ApiProxyTraffic(correlation_id);
CREATE INDEX idx_apitraffic_timestamp ON log_ApiProxyTraffic(log_timestamp);
CREATE INDEX idx_apitraffic_proxy ON log_ApiProxyTraffic(api_proxy_id);
CREATE INDEX idx_apitraffic_project ON log_ApiProxyTraffic(project_id);
CREATE INDEX idx_apitraffic_status ON log_ApiProxyTraffic(status_code);
CREATE INDEX idx_apitraffic_result ON log_ApiProxyTraffic(result_type);
```
```sql theme={null}
CREATE INDEX idx_apitraffic_correlation ON log_ApiProxyTraffic(correlation_id);
CREATE INDEX idx_apitraffic_timestamp ON log_ApiProxyTraffic(log_timestamp);
CREATE INDEX idx_apitraffic_proxy ON log_ApiProxyTraffic(api_proxy_id);
CREATE INDEX idx_apitraffic_project ON log_ApiProxyTraffic(project_id);
CREATE INDEX idx_apitraffic_status ON log_ApiProxyTraffic(status_code);
CREATE INDEX idx_apitraffic_result ON log_ApiProxyTraffic(result_type);
```
### Partitioning
This table grows very quickly under production traffic. **Daily partitioning on `log_timestamp`** is strongly recommended so that retention can be applied via `DROP PARTITION` and time-range queries prune inactive partitions. See [Partitioning Syntax](#partitioning-syntax) for database-specific examples.
## Token Traffic Log
The table that records OAuth/JWT token operations.
```sql theme={null}
CREATE TABLE log_TokenTraffic (
id VARCHAR2(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
correlation_id VARCHAR2(255),
environment_id VARCHAR2(255),
project_id VARCHAR2(255),
application_id VARCHAR2(255),
application_name VARCHAR2(255),
api_proxy_id VARCHAR2(255),
api_proxy_name VARCHAR2(255),
x_forwarded_for VARCHAR2(255),
req_remote_addr VARCHAR2(255),
req_http_method VARCHAR2(255),
req_content_type VARCHAR2(255),
username_or_key VARCHAR2(255),
time_total NUMBER(10),
status_code NUMBER(10),
error_type VARCHAR2(255),
parent_error_type VARCHAR2(255),
result_type VARCHAR2(255),
to_client_header CLOB,
to_client_body CLOB,
auth_token_type VARCHAR2(255),
audience VARCHAR2(255),
client_info VARCHAR2(255),
token_never_expires NUMBER(1),
issued_at TIMESTAMP,
expires_at TIMESTAMP,
expires_in NUMBER(10),
scope VARCHAR2(255),
refresh_token_allowed NUMBER(1),
refresh_token_issued_at TIMESTAMP,
refresh_token_expires_at TIMESTAMP,
refresh_token_expires_in NUMBER(10),
refresh_count NUMBER(10),
max_refresh_count NUMBER(10)
);
```
```sql theme={null}
CREATE TABLE log_TokenTraffic (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP NULL,
correlation_id VARCHAR(255),
environment_id VARCHAR(255),
project_id VARCHAR(255),
application_id VARCHAR(255),
application_name VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
x_forwarded_for VARCHAR(255),
req_remote_addr VARCHAR(255),
req_http_method VARCHAR(255),
req_content_type VARCHAR(255),
username_or_key VARCHAR(255),
time_total INT,
status_code INT,
error_type VARCHAR(255),
parent_error_type VARCHAR(255),
result_type VARCHAR(255),
to_client_header TEXT,
to_client_body TEXT,
auth_token_type VARCHAR(255),
audience VARCHAR(255),
client_info VARCHAR(255),
token_never_expires TINYINT(1),
issued_at TIMESTAMP NULL,
expires_at TIMESTAMP NULL,
expires_in INT,
scope VARCHAR(255),
refresh_token_allowed TINYINT(1),
refresh_token_issued_at TIMESTAMP NULL,
refresh_token_expires_at TIMESTAMP NULL,
refresh_token_expires_in INT,
refresh_count INT,
max_refresh_count INT
);
```
```sql theme={null}
CREATE TABLE log_TokenTraffic (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
correlation_id VARCHAR(255),
environment_id VARCHAR(255),
project_id VARCHAR(255),
application_id VARCHAR(255),
application_name VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
x_forwarded_for VARCHAR(255),
req_remote_addr VARCHAR(255),
req_http_method VARCHAR(255),
req_content_type VARCHAR(255),
username_or_key VARCHAR(255),
time_total INTEGER,
status_code INTEGER,
error_type VARCHAR(255),
parent_error_type VARCHAR(255),
result_type VARCHAR(255),
to_client_header TEXT,
to_client_body TEXT,
auth_token_type VARCHAR(255),
audience VARCHAR(255),
client_info VARCHAR(255),
token_never_expires BOOLEAN,
issued_at TIMESTAMP,
expires_at TIMESTAMP,
expires_in INTEGER,
scope VARCHAR(255),
refresh_token_allowed BOOLEAN,
refresh_token_issued_at TIMESTAMP,
refresh_token_expires_at TIMESTAMP,
refresh_token_expires_in INTEGER,
refresh_count INTEGER,
max_refresh_count INTEGER
);
```
```sql theme={null}
CREATE TABLE log_TokenTraffic (
id NVARCHAR(255) PRIMARY KEY,
log_timestamp DATETIME2,
correlation_id NVARCHAR(255),
environment_id NVARCHAR(255),
project_id NVARCHAR(255),
application_id NVARCHAR(255),
application_name NVARCHAR(255),
api_proxy_id NVARCHAR(255),
api_proxy_name NVARCHAR(255),
x_forwarded_for NVARCHAR(255),
req_remote_addr NVARCHAR(255),
req_http_method NVARCHAR(255),
req_content_type NVARCHAR(255),
username_or_key NVARCHAR(255),
time_total INT,
status_code INT,
error_type NVARCHAR(255),
parent_error_type NVARCHAR(255),
result_type NVARCHAR(255),
to_client_header NVARCHAR(MAX),
to_client_body NVARCHAR(MAX),
auth_token_type NVARCHAR(255),
audience NVARCHAR(255),
client_info NVARCHAR(255),
token_never_expires BIT,
issued_at DATETIME2,
expires_at DATETIME2,
expires_in INT,
scope NVARCHAR(255),
refresh_token_allowed BIT,
refresh_token_issued_at DATETIME2,
refresh_token_expires_at DATETIME2,
refresh_token_expires_in INT,
refresh_count INT,
max_refresh_count INT
);
```
| Column | Description |
| --------------------------- | ------------------------------------------ |
| id | Record identifier |
| log\_timestamp | Time the log record was created |
| correlation\_id | Correlation identifier |
| environment\_id | Environment identifier |
| project\_id | Project identifier |
| application\_id | Application identifier |
| application\_name | Application name |
| api\_proxy\_id | API Proxy identifier |
| api\_proxy\_name | API Proxy name |
| x\_forwarded\_for | Real client IP address |
| req\_remote\_addr | Remote address |
| req\_http\_method | HTTP method |
| req\_content\_type | Request content type |
| username\_or\_key | Username or API key |
| time\_total | Total processing duration (ms) |
| status\_code | HTTP response status code |
| error\_type | Error type |
| parent\_error\_type | Parent error type |
| result\_type | Processing result |
| to\_client\_header | Response headers sent to the client (JSON) |
| to\_client\_body | Response body sent to the client |
| auth\_token\_type | Token type (Bearer, Basic, etc.) |
| audience | Target audience of the token |
| client\_info | Client information |
| token\_never\_expires | Whether the token has no expiry |
| issued\_at | Time the token was issued |
| expires\_at | Token expiry time |
| expires\_in | Token validity duration (seconds) |
| scope | Token scope information |
| refresh\_token\_allowed | Whether refresh tokens are allowed |
| refresh\_token\_issued\_at | Time the refresh token was issued |
| refresh\_token\_expires\_at | Refresh token expiry time |
| refresh\_token\_expires\_in | Refresh token validity duration (seconds) |
| refresh\_count | Number of times refreshed |
| max\_refresh\_count | Maximum number of refreshes allowed |
### Recommended Indexes
Token operations are frequently investigated per user/application and per time window. Correlation joins with the API traffic table are also common when tracing an end-to-end call.
```sql theme={null}
CREATE INDEX idx_tokentraffic_correlation ON log_TokenTraffic(correlation_id);
CREATE INDEX idx_tokentraffic_timestamp ON log_TokenTraffic(log_timestamp);
CREATE INDEX idx_tokentraffic_proxy ON log_TokenTraffic(api_proxy_id);
CREATE INDEX idx_tokentraffic_user ON log_TokenTraffic(username_or_key);
CREATE INDEX idx_tokentraffic_result ON log_TokenTraffic(result_type);
```
```sql theme={null}
CREATE INDEX idx_tokentraffic_correlation ON log_TokenTraffic(correlation_id);
CREATE INDEX idx_tokentraffic_timestamp ON log_TokenTraffic(log_timestamp);
CREATE INDEX idx_tokentraffic_proxy ON log_TokenTraffic(api_proxy_id);
CREATE INDEX idx_tokentraffic_user ON log_TokenTraffic(username_or_key);
CREATE INDEX idx_tokentraffic_result ON log_TokenTraffic(result_type);
```
```sql theme={null}
CREATE INDEX idx_tokentraffic_correlation ON log_TokenTraffic(correlation_id);
CREATE INDEX idx_tokentraffic_timestamp ON log_TokenTraffic(log_timestamp);
CREATE INDEX idx_tokentraffic_proxy ON log_TokenTraffic(api_proxy_id);
CREATE INDEX idx_tokentraffic_user ON log_TokenTraffic(username_or_key);
CREATE INDEX idx_tokentraffic_result ON log_TokenTraffic(result_type);
```
```sql theme={null}
CREATE INDEX idx_tokentraffic_correlation ON log_TokenTraffic(correlation_id);
CREATE INDEX idx_tokentraffic_timestamp ON log_TokenTraffic(log_timestamp);
CREATE INDEX idx_tokentraffic_proxy ON log_TokenTraffic(api_proxy_id);
CREATE INDEX idx_tokentraffic_user ON log_TokenTraffic(username_or_key);
CREATE INDEX idx_tokentraffic_result ON log_TokenTraffic(result_type);
```
### Partitioning
Token volumes usually scale with API traffic volumes. **Daily partitioning on `log_timestamp`** is recommended. See [Partitioning Syntax](#partitioning-syntax).
## Policy Capture Log
The table that stores request details recorded in policy capture mode.
```sql theme={null}
CREATE TABLE log_PolicyCapture (
id VARCHAR2(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
correlation_id VARCHAR2(255),
environment_id VARCHAR2(255),
api_proxy_id VARCHAR2(255),
api_proxy_name VARCHAR2(255),
proxy_method_id VARCHAR2(255),
proxy_method_name VARCHAR2(255),
username_or_key VARCHAR2(255),
status_code NUMBER(10),
result_type VARCHAR2(255),
error_type VARCHAR2(255),
from_client_ro_header CLOB,
from_client_ro_param CLOB,
from_client_ro_body CLOB,
capture_region VARCHAR2(50),
capture_location VARCHAR2(50)
);
```
```sql theme={null}
CREATE TABLE log_PolicyCapture (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP NULL,
correlation_id VARCHAR(255),
environment_id VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
proxy_method_id VARCHAR(255),
proxy_method_name VARCHAR(255),
username_or_key VARCHAR(255),
status_code INT,
result_type VARCHAR(255),
error_type VARCHAR(255),
from_client_ro_header TEXT,
from_client_ro_param TEXT,
from_client_ro_body LONGTEXT,
capture_region VARCHAR(50),
capture_location VARCHAR(50)
);
```
```sql theme={null}
CREATE TABLE log_PolicyCapture (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
correlation_id VARCHAR(255),
environment_id VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
proxy_method_id VARCHAR(255),
proxy_method_name VARCHAR(255),
username_or_key VARCHAR(255),
status_code INTEGER,
result_type VARCHAR(255),
error_type VARCHAR(255),
from_client_ro_header TEXT,
from_client_ro_param TEXT,
from_client_ro_body TEXT,
capture_region VARCHAR(50),
capture_location VARCHAR(50)
);
```
```sql theme={null}
CREATE TABLE log_PolicyCapture (
id NVARCHAR(255) PRIMARY KEY,
log_timestamp DATETIME2,
correlation_id NVARCHAR(255),
environment_id NVARCHAR(255),
api_proxy_id NVARCHAR(255),
api_proxy_name NVARCHAR(255),
proxy_method_id NVARCHAR(255),
proxy_method_name NVARCHAR(255),
username_or_key NVARCHAR(255),
status_code INT,
result_type NVARCHAR(255),
error_type NVARCHAR(255),
from_client_ro_header NVARCHAR(MAX),
from_client_ro_param NVARCHAR(MAX),
from_client_ro_body NVARCHAR(MAX),
capture_region NVARCHAR(50),
capture_location NVARCHAR(50)
);
```
| Column | Description |
| ------------------------ | ------------------------------------------------------------------------------------------------------------------ |
| id | Record identifier |
| log\_timestamp | Time the log record was created |
| correlation\_id | Correlation identifier |
| environment\_id | Environment identifier |
| api\_proxy\_id | API Proxy identifier |
| api\_proxy\_name | API Proxy name |
| proxy\_method\_id | Method identifier |
| proxy\_method\_name | Method name |
| username\_or\_key | Username or API key |
| status\_code | HTTP response status code |
| result\_type | Processing result |
| error\_type | Error type |
| from\_client\_ro\_header | Headers from the client (JSON) |
| from\_client\_ro\_param | Query parameters from the client (JSON) |
| from\_client\_ro\_body | Request body from the client |
| capture\_region | Pipeline stage where the policy ran: `FROM_CLIENT`, `TO_BACKEND`, `FROM_BACKEND`, `TO_CLIENT` |
| capture\_location | Level at which the policy was defined: `API_PROXY_GROUP` (Group), `API_PROXY` (Proxy), `API_PROXY_METHOD` (Method) |
### Recommended Indexes
When the log policy is placed at multiple pipeline stages (for example, `FROM_CLIENT` and `TO_CLIENT`), request and response rows share the same `correlation_id`. The `correlation_id` index is therefore essential for joining request and response records end to end.
```sql theme={null}
CREATE INDEX idx_policycapture_correlation ON log_PolicyCapture(correlation_id);
CREATE INDEX idx_policycapture_timestamp ON log_PolicyCapture(log_timestamp);
CREATE INDEX idx_policycapture_proxy ON log_PolicyCapture(api_proxy_id);
CREATE INDEX idx_policycapture_region ON log_PolicyCapture(capture_region);
```
```sql theme={null}
CREATE INDEX idx_policycapture_correlation ON log_PolicyCapture(correlation_id);
CREATE INDEX idx_policycapture_timestamp ON log_PolicyCapture(log_timestamp);
CREATE INDEX idx_policycapture_proxy ON log_PolicyCapture(api_proxy_id);
CREATE INDEX idx_policycapture_region ON log_PolicyCapture(capture_region);
```
```sql theme={null}
CREATE INDEX idx_policycapture_correlation ON log_PolicyCapture(correlation_id);
CREATE INDEX idx_policycapture_timestamp ON log_PolicyCapture(log_timestamp);
CREATE INDEX idx_policycapture_proxy ON log_PolicyCapture(api_proxy_id);
CREATE INDEX idx_policycapture_region ON log_PolicyCapture(capture_region);
```
```sql theme={null}
CREATE INDEX idx_policycapture_correlation ON log_PolicyCapture(correlation_id);
CREATE INDEX idx_policycapture_timestamp ON log_PolicyCapture(log_timestamp);
CREATE INDEX idx_policycapture_proxy ON log_PolicyCapture(api_proxy_id);
CREATE INDEX idx_policycapture_region ON log_PolicyCapture(capture_region);
```
### Partitioning
Volume depends on how many log policies are attached to flows and how often they fire. For policies attached at proxy/method scope on high-traffic APIs, **daily partitioning on `log_timestamp`** is recommended. See [Partitioning Syntax](#partitioning-syntax).
## Application Log
The table that stores platform-level application logs.
```sql theme={null}
CREATE TABLE log_Application (
id VARCHAR2(255) PRIMARY KEY,
env_name VARCHAR2(255),
api_proxy_id VARCHAR2(255),
api_proxy_name VARCHAR2(255),
correlation_id VARCHAR2(255),
log_timestamp TIMESTAMP,
log_level VARCHAR2(255),
message CLOB,
stack_trace CLOB,
host_name VARCHAR2(255),
enum_application_log VARCHAR2(255),
total_duration NUMBER(10)
);
```
```sql theme={null}
CREATE TABLE log_Application (
id VARCHAR(255) PRIMARY KEY,
env_name VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
correlation_id VARCHAR(255),
log_timestamp TIMESTAMP NULL,
log_level VARCHAR(255),
message TEXT,
stack_trace TEXT,
host_name VARCHAR(255),
enum_application_log VARCHAR(255),
total_duration INT
);
```
```sql theme={null}
CREATE TABLE log_Application (
id VARCHAR(255) PRIMARY KEY,
env_name VARCHAR(255),
api_proxy_id VARCHAR(255),
api_proxy_name VARCHAR(255),
correlation_id VARCHAR(255),
log_timestamp TIMESTAMP,
log_level VARCHAR(255),
message TEXT,
stack_trace TEXT,
host_name VARCHAR(255),
enum_application_log VARCHAR(255),
total_duration INTEGER
);
```
```sql theme={null}
CREATE TABLE log_Application (
id NVARCHAR(255) PRIMARY KEY,
env_name NVARCHAR(255),
api_proxy_id NVARCHAR(255),
api_proxy_name NVARCHAR(255),
correlation_id NVARCHAR(255),
log_timestamp DATETIME2,
log_level NVARCHAR(255),
message NVARCHAR(MAX),
stack_trace NVARCHAR(MAX),
host_name NVARCHAR(255),
enum_application_log NVARCHAR(255),
total_duration INT
);
```
| Column | Description |
| ---------------------- | -------------------------------------------- |
| id | Record identifier |
| env\_name | Environment name |
| api\_proxy\_id | API Proxy identifier |
| api\_proxy\_name | API Proxy name |
| correlation\_id | Correlation identifier |
| log\_timestamp | Time the log record was created |
| log\_level | Log level (INFO, WARN, ERROR, etc.) |
| message | Log message |
| stack\_trace | Error stack trace |
| host\_name | Name of the server where the log was created |
| enum\_application\_log | Application log source type |
| total\_duration | Total processing duration (ms) |
### Recommended Indexes
Application logs are commonly filtered by time range and log level (for troubleshooting), and joined with traffic logs via `correlation_id` to reconstruct a single request's platform-level events.
```sql theme={null}
CREATE INDEX idx_application_correlation ON log_Application(correlation_id);
CREATE INDEX idx_application_timestamp ON log_Application(log_timestamp);
CREATE INDEX idx_application_level ON log_Application(log_level);
CREATE INDEX idx_application_proxy ON log_Application(api_proxy_id);
```
```sql theme={null}
CREATE INDEX idx_application_correlation ON log_Application(correlation_id);
CREATE INDEX idx_application_timestamp ON log_Application(log_timestamp);
CREATE INDEX idx_application_level ON log_Application(log_level);
CREATE INDEX idx_application_proxy ON log_Application(api_proxy_id);
```
```sql theme={null}
CREATE INDEX idx_application_correlation ON log_Application(correlation_id);
CREATE INDEX idx_application_timestamp ON log_Application(log_timestamp);
CREATE INDEX idx_application_level ON log_Application(log_level);
CREATE INDEX idx_application_proxy ON log_Application(api_proxy_id);
```
```sql theme={null}
CREATE INDEX idx_application_correlation ON log_Application(correlation_id);
CREATE INDEX idx_application_timestamp ON log_Application(log_timestamp);
CREATE INDEX idx_application_level ON log_Application(log_level);
CREATE INDEX idx_application_proxy ON log_Application(api_proxy_id);
```
### Partitioning
Application logs accumulate steadily. **Daily partitioning on `log_timestamp`** is recommended on production systems. See [Partitioning Syntax](#partitioning-syntax).
## Audit Event
The table that stores audit records of user operations.
```sql theme={null}
CREATE TABLE log_AuditEvent (
id VARCHAR2(255) PRIMARY KEY,
audit_event_date TIMESTAMP,
principal VARCHAR2(255),
state VARCHAR2(255),
object_id VARCHAR2(255),
object_name VARCHAR2(255),
ref_object_json CLOB,
project_id VARCHAR2(255),
class_name VARCHAR2(255)
);
```
```sql theme={null}
CREATE TABLE log_AuditEvent (
id VARCHAR(255) PRIMARY KEY,
audit_event_date TIMESTAMP NULL,
principal VARCHAR(255),
state VARCHAR(255),
object_id VARCHAR(255),
object_name VARCHAR(255),
ref_object_json LONGTEXT,
project_id VARCHAR(255),
class_name VARCHAR(255)
);
```
```sql theme={null}
CREATE TABLE log_AuditEvent (
id VARCHAR(255) PRIMARY KEY,
audit_event_date TIMESTAMP,
principal VARCHAR(255),
state VARCHAR(255),
object_id VARCHAR(255),
object_name VARCHAR(255),
ref_object_json TEXT,
project_id VARCHAR(255),
class_name VARCHAR(255)
);
```
```sql theme={null}
CREATE TABLE log_AuditEvent (
id NVARCHAR(255) PRIMARY KEY,
audit_event_date DATETIME2,
principal NVARCHAR(255),
state NVARCHAR(255),
object_id NVARCHAR(255),
object_name NVARCHAR(255),
ref_object_json NVARCHAR(MAX),
project_id NVARCHAR(255),
class_name NVARCHAR(255)
);
```
| Column | Description |
| ------------------ | -------------------------------------------------- |
| id | Record identifier |
| audit\_event\_date | Time the audit event occurred |
| principal | User who performed the action |
| state | State of the action (CREATE, UPDATE, DELETE, etc.) |
| object\_id | Identifier of the object acted upon |
| object\_name | Name of the object acted upon |
| ref\_object\_json | JSON representation of the object acted upon |
| project\_id | Project identifier |
| class\_name | Type of the object |
### Recommended Indexes
Audit queries most often filter by date range, user (principal), affected object type, or project. The partition key here is `audit_event_date`, not `log_timestamp`.
```sql theme={null}
CREATE INDEX idx_auditevent_date ON log_AuditEvent(audit_event_date);
CREATE INDEX idx_auditevent_principal ON log_AuditEvent(principal);
CREATE INDEX idx_auditevent_project ON log_AuditEvent(project_id);
CREATE INDEX idx_auditevent_class ON log_AuditEvent(class_name);
CREATE INDEX idx_auditevent_state ON log_AuditEvent(state);
```
```sql theme={null}
CREATE INDEX idx_auditevent_date ON log_AuditEvent(audit_event_date);
CREATE INDEX idx_auditevent_principal ON log_AuditEvent(principal);
CREATE INDEX idx_auditevent_project ON log_AuditEvent(project_id);
CREATE INDEX idx_auditevent_class ON log_AuditEvent(class_name);
CREATE INDEX idx_auditevent_state ON log_AuditEvent(state);
```
```sql theme={null}
CREATE INDEX idx_auditevent_date ON log_AuditEvent(audit_event_date);
CREATE INDEX idx_auditevent_principal ON log_AuditEvent(principal);
CREATE INDEX idx_auditevent_project ON log_AuditEvent(project_id);
CREATE INDEX idx_auditevent_class ON log_AuditEvent(class_name);
CREATE INDEX idx_auditevent_state ON log_AuditEvent(state);
```
```sql theme={null}
CREATE INDEX idx_auditevent_date ON log_AuditEvent(audit_event_date);
CREATE INDEX idx_auditevent_principal ON log_AuditEvent(principal);
CREATE INDEX idx_auditevent_project ON log_AuditEvent(project_id);
CREATE INDEX idx_auditevent_class ON log_AuditEvent(class_name);
CREATE INDEX idx_auditevent_state ON log_AuditEvent(state);
```
### Partitioning
Audit events are generated by user actions (far fewer than traffic rows) and are usually retained for longer periods for compliance. **Monthly partitioning on `audit_event_date`** is typically sufficient. See [Partitioning Syntax](#partitioning-syntax) and substitute `MONTH` for `DAY` in the interval.
## Login Log
The table that stores login attempts to the API Manager and API Portal (success and failure events).
```sql theme={null}
CREATE TABLE log_LoginLog (
id VARCHAR2(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
source VARCHAR2(50),
principal VARCHAR2(255),
email VARCHAR2(255),
display_name VARCHAR2(255),
event_type VARCHAR2(50),
remote_address VARCHAR2(255),
message CLOB,
error_type VARCHAR2(255),
portal_id VARCHAR2(255),
account_id VARCHAR2(255)
);
```
```sql theme={null}
CREATE TABLE log_LoginLog (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP NULL,
source VARCHAR(50),
principal VARCHAR(255),
email VARCHAR(255),
display_name VARCHAR(255),
event_type VARCHAR(50),
remote_address VARCHAR(255),
message TEXT,
error_type VARCHAR(255),
portal_id VARCHAR(255),
account_id VARCHAR(255)
);
```
```sql theme={null}
CREATE TABLE log_LoginLog (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
source VARCHAR(50),
principal VARCHAR(255),
email VARCHAR(255),
display_name VARCHAR(255),
event_type VARCHAR(50),
remote_address VARCHAR(255),
message TEXT,
error_type VARCHAR(255),
portal_id VARCHAR(255),
account_id VARCHAR(255)
);
```
```sql theme={null}
CREATE TABLE log_LoginLog (
id NVARCHAR(255) PRIMARY KEY,
log_timestamp DATETIME2,
source NVARCHAR(50),
principal NVARCHAR(255),
email NVARCHAR(255),
display_name NVARCHAR(255),
event_type NVARCHAR(50),
remote_address NVARCHAR(255),
message NVARCHAR(MAX),
error_type NVARCHAR(255),
portal_id NVARCHAR(255),
account_id NVARCHAR(255)
);
```
| Column | Description |
| --------------- | --------------------------------------------- |
| id | Record identifier |
| log\_timestamp | Time the login event occurred |
| source | Login source (`API_MANAGER`, `API_PORTAL`) |
| principal | User principal (API Manager login) |
| email | User email (API Portal login) |
| display\_name | User display name |
| event\_type | Event type (`LOGIN_SUCCESS`, `LOGIN_FAILURE`) |
| remote\_address | Remote client IP address |
| message | Result message or failure reason |
| error\_type | Error class (for failures) |
| portal\_id | Portal identifier (for portal logins) |
| account\_id | Account identifier (for portal logins) |
### Recommended Indexes
Login logs are queried by time range, user (email/principal), login source, and event type (for security auditing and locked-account investigation).
```sql theme={null}
CREATE INDEX idx_loginlog_timestamp ON log_LoginLog(log_timestamp);
CREATE INDEX idx_loginlog_email ON log_LoginLog(email);
CREATE INDEX idx_loginlog_principal ON log_LoginLog(principal);
CREATE INDEX idx_loginlog_source ON log_LoginLog(source);
CREATE INDEX idx_loginlog_event ON log_LoginLog(event_type);
```
```sql theme={null}
CREATE INDEX idx_loginlog_timestamp ON log_LoginLog(log_timestamp);
CREATE INDEX idx_loginlog_email ON log_LoginLog(email);
CREATE INDEX idx_loginlog_principal ON log_LoginLog(principal);
CREATE INDEX idx_loginlog_source ON log_LoginLog(source);
CREATE INDEX idx_loginlog_event ON log_LoginLog(event_type);
```
```sql theme={null}
CREATE INDEX idx_loginlog_timestamp ON log_LoginLog(log_timestamp);
CREATE INDEX idx_loginlog_email ON log_LoginLog(email);
CREATE INDEX idx_loginlog_principal ON log_LoginLog(principal);
CREATE INDEX idx_loginlog_source ON log_LoginLog(source);
CREATE INDEX idx_loginlog_event ON log_LoginLog(event_type);
```
```sql theme={null}
CREATE INDEX idx_loginlog_timestamp ON log_LoginLog(log_timestamp);
CREATE INDEX idx_loginlog_email ON log_LoginLog(email);
CREATE INDEX idx_loginlog_principal ON log_LoginLog(principal);
CREATE INDEX idx_loginlog_source ON log_LoginLog(source);
CREATE INDEX idx_loginlog_event ON log_LoginLog(event_type);
```
### Partitioning
Login log volume is typically low compared to traffic logs. **Monthly partitioning on `log_timestamp`** is sufficient for long retention periods commonly required for security audit. See [Partitioning Syntax](#partitioning-syntax) and substitute `MONTH` for `DAY` in the interval.
## Unsent Message
The table that temporarily stores log records that could not be delivered due to connection errors.
```sql theme={null}
CREATE TABLE log_UnsentMessage (
id VARCHAR2(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
connector_id VARCHAR2(50),
content CLOB
);
```
```sql theme={null}
CREATE TABLE log_UnsentMessage (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP NULL,
connector_id VARCHAR(50),
content LONGTEXT
);
```
```sql theme={null}
CREATE TABLE log_UnsentMessage (
id VARCHAR(255) PRIMARY KEY,
log_timestamp TIMESTAMP,
connector_id VARCHAR(50),
content TEXT
);
```
```sql theme={null}
CREATE TABLE log_UnsentMessage (
id NVARCHAR(255) PRIMARY KEY,
log_timestamp DATETIME2,
connector_id NVARCHAR(50),
content NVARCHAR(MAX)
);
```
| Column | Description |
| -------------- | --------------------------------------------------------- |
| id | Record identifier |
| log\_timestamp | Time the record was created |
| connector\_id | Identifier of the connector the data failed to be sent to |
| content | Content of the unsent log record (JSON) |
### Recommended Indexes
Rows in this table are transient: they are inserted when a connector is unreachable and removed when delivery succeeds. A small number of indexes is sufficient for resend lookups and cleanup.
```sql theme={null}
CREATE INDEX idx_unsent_timestamp ON log_UnsentMessage(log_timestamp);
CREATE INDEX idx_unsent_connector ON log_UnsentMessage(connector_id);
```
```sql theme={null}
CREATE INDEX idx_unsent_timestamp ON log_UnsentMessage(log_timestamp);
CREATE INDEX idx_unsent_connector ON log_UnsentMessage(connector_id);
```
```sql theme={null}
CREATE INDEX idx_unsent_timestamp ON log_UnsentMessage(log_timestamp);
CREATE INDEX idx_unsent_connector ON log_UnsentMessage(connector_id);
```
```sql theme={null}
CREATE INDEX idx_unsent_timestamp ON log_UnsentMessage(log_timestamp);
CREATE INDEX idx_unsent_connector ON log_UnsentMessage(connector_id);
```
### Partitioning
This table holds transient records that are normally consumed within a short time window. **Partitioning is not required.** If the table grows unexpectedly (prolonged outages), operational cleanup is a better remedy than partitioning.
## Partitioning Syntax
The examples below show daily partitioning on `log_timestamp`. Substitute the actual table name and adjust the initial boundary date to suit your deployment. For the `log_AuditEvent` table, replace `log_timestamp` with `audit_event_date` and use a monthly interval instead.
```sql theme={null}
-- Apply at CREATE TABLE time (add after the column list):
)
PARTITION BY RANGE (log_timestamp)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
PARTITION p_initial VALUES LESS THAN (TIMESTAMP '2026-01-01 00:00:00')
);
-- Monthly variant (e.g., for log_AuditEvent):
-- INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
```
```sql theme={null}
-- The partition key must be part of the PRIMARY KEY.
-- Adjust the primary key definition when creating the table:
-- PRIMARY KEY (id, log_timestamp)
ALTER TABLE log_ApiProxyTraffic
PARTITION BY RANGE (TO_DAYS(log_timestamp)) (
PARTITION p_initial VALUES LESS THAN (TO_DAYS('2026-01-01')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- Add a new daily partition ahead of time (scheduled job):
ALTER TABLE log_ApiProxyTraffic REORGANIZE PARTITION p_future INTO (
PARTITION p20260421 VALUES LESS THAN (TO_DAYS('2026-04-22')),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
```
```sql theme={null}
-- Apply at CREATE TABLE time (replace the table definition):
CREATE TABLE log_ApiProxyTraffic (
/* ... columns ... */
) PARTITION BY RANGE (log_timestamp);
-- Create a daily child partition (scheduled job):
CREATE TABLE log_ApiProxyTraffic_p20260421
PARTITION OF log_ApiProxyTraffic
FOR VALUES FROM ('2026-04-21') TO ('2026-04-22');
-- Drop an old partition quickly:
DROP TABLE log_ApiProxyTraffic_p20260101;
```
```sql theme={null}
-- 1) Partition function (daily boundaries):
CREATE PARTITION FUNCTION pf_logs_daily (DATETIME2)
AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-01-02', '2026-01-03');
-- 2) Partition scheme:
CREATE PARTITION SCHEME ps_logs_daily
AS PARTITION pf_logs_daily ALL TO ([PRIMARY]);
-- 3) Create the table on the scheme:
CREATE TABLE log_ApiProxyTraffic (
/* ... columns ... */
) ON ps_logs_daily(log_timestamp);
-- 4) Extend with new boundaries via a scheduled job:
ALTER PARTITION SCHEME ps_logs_daily NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_logs_daily() SPLIT RANGE ('2026-04-22');
```
New daily partitions must be created in advance (for example, via a scheduled job). If the next partition does not exist when a record arrives, inserts will fail on SQL Server and MySQL, and Oracle will auto-create only when `INTERVAL` partitioning is used.
## Data Type Mapping Table
The following table shows the data type mappings between different databases.
| Usage | Oracle | MySQL/MariaDB | PostgreSQL | SQL Server |
| ----------- | ------------- | --------------- | ---------------- | ------------- |
| Short text | VARCHAR2(255) | VARCHAR(255) | VARCHAR(255) | NVARCHAR(255) |
| Long text | CLOB | TEXT / LONGTEXT | TEXT | NVARCHAR(MAX) |
| Integer | NUMBER(10) | INT | INTEGER | INT |
| Big integer | NUMBER(19) | BIGINT | BIGINT | BIGINT |
| Decimal | NUMBER | DOUBLE | DOUBLE PRECISION | FLOAT |
| Boolean | NUMBER(1) | TINYINT(1) | BOOLEAN | BIT |
| Timestamp | TIMESTAMP | TIMESTAMP NULL | TIMESTAMP | DATETIME2 |