Skip to content

Schema

The starter ships a single table: api_log. As of v0.3.0, the default api.log.schema.management=builtin creates this table for you on first application startup — no migration tool required. See api.log.schema.management and Installation / Schema management for the three provisioning strategies.

The SQL below is the exact contents of V1.0__create_api_log.sql shipped at classpath:db/api-log/V1.0__create_api_log.sql inside the JAR — useful if you want to apply it manually (schema.management=none) or copy it into your own migrations.

Table definition

CREATE TABLE api_log (
    id            BIGSERIAL    PRIMARY KEY,
    event_type    VARCHAR(50)  NOT NULL,
    request_id    VARCHAR(36)  NOT NULL,
    endpoint      VARCHAR(255) NOT NULL,
    payload       JSONB,
    response      JSONB,
    status_code   INT,
    error_message JSONB,
    timestamp     TIMESTAMP    NOT NULL,
    retry_count   INT          DEFAULT 0,
    is_retry      BOOLEAN      DEFAULT FALSE
);

CREATE INDEX idx_request_id ON api_log (request_id);
CREATE INDEX idx_timestamp  ON api_log (timestamp);

Columns

Column Type Nullable Description
id BIGSERIAL NO Primary key. Monotonic across inserts — use it for ordering within a request_id.
event_type VARCHAR(50) NO One of INITIATED, SUCCESS, ERROR, RETRY_ERROR. See Events.
request_id VARCHAR(36) NO UUID generated by RestApiClientUtil (or whoever publishes the event). Correlates rows for one logical call.
endpoint VARCHAR(255) NO The URL path or arbitrary call target. Examples: /api/users/1, https://vendor.com/charge, grpc:UserService/Get.
payload JSONB YES Request body. Stored as JSONB if the source is JSON; otherwise wrapped as a string. Null when there's no body.
response JSONB YES Response body. Null on INITIATED, ERROR, RETRY_ERROR.
status_code INT YES HTTP status. Null on INITIATED and pre-response errors (connection refused, timeout). Set when the upstream returned anything.
error_message JSONB YES Structured error detail on ERROR / RETRY_ERROR. Shape: {"type": "java.net.SocketTimeoutException", "message": "Read timed out"}.
timestamp TIMESTAMP NO When the listener wrote the row. Captured server-side, not from the event's creation time — for accurate request latency, log it in the payload.
retry_count INT YES, default 0 Attempt number. 0 for the initial call, 1+ for each retry.
is_retry BOOLEAN YES, default false true on rows for retry attempts. Convenient predicate for retry-aware queries.

Shipped indexes

Index Columns Use
idx_request_id (request_id) Looking up the timeline for a single call
idx_timestamp (timestamp) Time-range filtering (last 1h, last 24h queries)

Indexes worth adding in production

The migration intentionally ships a minimal set. Add these as you outgrow them:

-- Used by every "group by endpoint" dashboard query.
CREATE INDEX idx_api_log_endpoint ON api_log (endpoint);

-- Used by error-rate alerts and event-type counts.
CREATE INDEX idx_api_log_event_type ON api_log (event_type);

-- Needed for JSONB containment / existence operators (@>, ?, ?&, ?|).
CREATE INDEX idx_api_log_payload_gin  ON api_log USING GIN (payload);
CREATE INDEX idx_api_log_response_gin ON api_log USING GIN (response);
CREATE INDEX idx_api_log_error_gin    ON api_log USING GIN (error_message);

-- For "find me errors in the last hour" — covers the common dashboard pattern.
CREATE INDEX idx_api_log_event_ts ON api_log (event_type, timestamp DESC);

JSONB shapes

The columns are JSONB so the exact shape is flexible, but RestApiClientUtil writes consistent structures:

payload

Whatever your request body was, serialized via Jackson. If you POSTed a DTO {"name": "Ada", "email": "ada@example.com"}, that's what payload contains.

For plain string payloads, it's wrapped: {"data": "..."} — ApiLogService does this conversion.

response

{
  "data": "{\"id\":1,\"name\":\"Ada\"}",
  "statusCode": 200
}

data is the response body as a string (you can re-parse it with (response -> 'data')::jsonb). statusCode mirrors the top-level status_code column for convenience inside JSONB queries.

error_message

{
  "type": "org.springframework.web.client.HttpClientErrorException",
  "message": "404 Not Found: [{\"error\":\"user not found\"}]"
}

Query with error_message ->> 'type' or error_message ->> 'message'.

See also