Schema Reference - Unkey Docs

Unkey Analytics stores verification events across multiple time-series tables for efficient querying. This reference documents all available tables and their columns.

Tip

Use aggregated tables (per_hour, per_day, per_month) for queries spanning long time periods to improve performance.

Raw Events Table#

The key_verifications_v1 table contains individual verification events as they occur.

Columns#

Column	Type	Description
`request_id`	String	Unique identifier for each verification request
`time`	Int64	Unix timestamp in milliseconds when verification occurred
`workspace_id`	String	Workspace identifier (automatically filtered - you don't need to filter by this)
`key_space_id`	String	Your KeySpace identifier (e.g., `ks_1234`). Automatically filtered if your root key is scoped to a single keyspace, otherwise filter this yourself.
`external_id`	String	Your user's identifier (e.g., `user_abc`) - use this to filter by user
`key_id`	String	Individual API key identifier
`outcome`	String	Verification result (see Outcome Values)
`region`	String	Unkey region that handled the verification
`source`	String	Verification origin. `api` means the Verify API, and `gateway` means a gateway API key policy
`app_id`	String	App whose gateway policy verified the key. Empty when `source` is not `gateway`
`tags`	Array(String)	Custom tags added during verification
`spent_credits`	Int64	Number of credits spent on this verification (0 if no credits were spent)

Outcome Values#

The outcome column contains one of these values:

Outcome	Description
`VALID`	Key is valid and verification succeeded
`RATE_LIMITED`	Verification exceeded rate limit
`INVALID`	Key not found or malformed
`EXPIRED`	Key has expired
`DISABLED`	Key is disabled
`INSUFFICIENT_PERMISSIONS`	Key lacks required permissions
`FORBIDDEN`	Operation not allowed for this key
`USAGE_EXCEEDED`	Key has exceeded usage limit

Aggregated Tables#

Pre-aggregated tables provide better query performance for long time ranges. Each aggregated table includes outcome counts.

Per Minute Table#

key_verifications_per_minute_v1 - Aggregated by minute

Column	Type	Description
`time`	DateTime/Date	DateTime for minute/hour tables, Date for day/month tables
`workspace_id`	String	Workspace identifier (automatically filtered - you don't need to filter by this)
`key_space_id`	String	KeySpace identifier. Automatically filtered if your root key is scoped to a single keyspace, otherwise filter this yourself.
`external_id`	String	Your user identifier
`key_id`	String	API key identifier
`outcome`	String	Verification outcome (VALID, RATE_LIMITED, INVALID, etc.)
`source`	String	Verification origin (`api` or `gateway`)
`app_id`	String	App whose gateway policy verified the key. Empty for non-gateway verifications
`tags`	Array	Tags associated with verifications
`count`	UInt64	Total verification count for this aggregation
`spent_credits`	UInt64	Total credits spent

Per Hour Table#

key_verifications_per_hour_v1 - Aggregated by hour. Same columns as per-minute table.

Per Day Table#

key_verifications_per_day_v1 - Aggregated by day. Same columns as per-minute table.

Per Month Table#

key_verifications_per_month_v1 - Aggregated by month. Same columns as per-minute table.

Filtering by keyspace and user#

You can use your familiar identifiers directly in queries:

key_space_id - Your KeySpace ID (e.g., ks_1234). Find this in your Keyspace Settings.
external_id - Your user identifiers (e.g., user_abc123) from your application

All standard comparison operators are supported: =, !=, <, >, <=, >=, IN, NOT IN

Filter by keyspace#

SELECT COUNT(*) FROM key_verifications_v1WHERE key_space_id = 'ks_1234'

Filter by User#

SELECT COUNT(*) FROM key_verifications_v1WHERE external_id = 'user_abc123'

Multiple Values#

SELECT COUNT(*) FROM key_verifications_v1WHERE key_space_id IN ('ks_1234', 'ks_5678')  AND external_id IN ('user_abc', 'user_xyz')

Filter gateway verifications by app#

Verifications from the gateway use source = 'gateway' and include the app ID that ran the API key policy. Verifications through the Verify API use source = 'api' and an empty app_id.

SELECT  app_id,  SUM(count) AS verificationsFROM key_verifications_per_day_v1WHERE source = 'gateway'  AND app_id != ''  AND time >= now() - INTERVAL 30 DAYGROUP BY app_idORDER BY verifications DESC

Working with Tags#

Tags are stored as Array(String) and require array functions to query.

Check if tag exists#

SELECT COUNT(*) FROM key_verifications_v1WHERE has(tags, 'path=/api/users')

Check if any tag exists#

SELECT COUNT(*) FROM key_verifications_v1WHERE hasAny(tags, ['environment=prod', 'environment=staging'])

Check if all tags exist#

SELECT COUNT(*) FROM key_verifications_v1WHERE hasAll(tags, ['environment=production', 'team=backend'])

Extract and group by tags#

SELECT  arrayJoin(tags) as tag,  COUNT(*) as countFROM key_verifications_v1WHERE time >= now() - INTERVAL 24 HOURGROUP BY tagORDER BY count DESC

Filter tags with pattern#

-- Get all tags starting with "path="SELECT  arrayJoin(arrayFilter(x -> startsWith(x, 'path='), tags)) as path,  COUNT(*) as requestsFROM key_verifications_v1WHERE time >= now() - INTERVAL 24 HOURGROUP BY path

Time Functions#

Timestamps are stored differently depending on the table:

Raw table (key_verifications_v1): time is Int64 (Unix milliseconds)
Aggregated tables: time is DateTime

Current Time#

SELECT now() as current_datetimeSELECT toUnixTimestamp(now()) * 1000 as current_millis

Time Ranges (Raw Table)#

For the raw key_verifications_v1 table, compare time with millisecond timestamps:

-- Last hourWHERE time >= toUnixTimestamp(now() - INTERVAL 1 HOUR) * 1000-- Last 24 hoursWHERE time >= toUnixTimestamp(now() - INTERVAL 24 HOUR) * 1000-- Last 7 daysWHERE time >= toUnixTimestamp(now() - INTERVAL 7 DAY) * 1000-- Last 30 daysWHERE time >= toUnixTimestamp(now() - INTERVAL 30 DAY) * 1000-- This monthWHERE time >= toUnixTimestamp(toStartOfMonth(now())) * 1000-- TodayWHERE time >= toUnixTimestamp(toStartOfDay(now())) * 1000

Time Ranges (Aggregated Tables)#

For aggregated tables, use DateTime comparisons directly:

-- Last 7 daysWHERE time >= now() - INTERVAL 7 DAY-- This monthWHERE time >= toStartOfMonth(now())-- TodayWHERE time >= toStartOfDay(now())

Time Rounding (Raw Table)#

-- Round to start of hourSELECT toStartOfHour(toDateTime(time / 1000)) as hour-- Round to start of daySELECT toStartOfDay(toDateTime(time / 1000)) as day-- Round to start of monthSELECT toStartOfMonth(toDateTime(time / 1000)) as month-- Convert to dateSELECT toDate(toDateTime(time / 1000)) as date

Specific Date Ranges#

-- Between specific dates (Unix milliseconds)WHERE time >= 1704067200000  -- Jan 1, 2024 00:00:00 UTC  AND time < 1735689600000   -- Jan 1, 2025 00:00:00 UTC

Common ClickHouse Functions#

Aggregate Functions#

Function	Description	Example
`COUNT()`	Count rows	`SELECT COUNT(*) FROM key_verifications_v1`
`SUM()`	Sum values	`SELECT SUM(valid_count) FROM key_verifications_per_day_v1`
`AVG()`	Average	`SELECT AVG(spent_credits) FROM key_verifications_v1`
`MIN()`	Minimum value	`SELECT MIN(time) FROM key_verifications_v1`
`MAX()`	Maximum value	`SELECT MAX(time) FROM key_verifications_v1`
`countIf()`	Conditional count	`SELECT countIf(outcome = 'VALID')`
`uniq()`	Count distinct	`SELECT uniq(key_id) FROM key_verifications_v1`

String Functions#

Function	Description	Example
`lower()`	Convert to lowercase	`WHERE lower(outcome) = 'valid'`
`upper()`	Convert to uppercase	`WHERE upper(region) = 'US-EAST-1'`
`concat()`	Concatenate strings	`SELECT concat(region, '-', outcome)`
`substring()`	Extract substring	`SELECT substring(key_id, 1, 8)`
`startsWith()`	Check prefix	`WHERE startsWith(key_id, 'key_')`

Array Functions#

Function	Description	Example
`has()`	Check element	`WHERE has(tags, 'environment=production')`
`hasAny()`	Check any element	`WHERE hasAny(tags, ['team=backend', 'team=api'])`
`hasAll()`	Check all elements	`WHERE hasAll(tags, ['environment=prod', 'tier=1'])`
`arrayJoin()`	Expand array	`SELECT arrayJoin(tags) as tag`
`arrayFilter()`	Filter array	`arrayFilter(x -> startsWith(x, 'path='), tags)`
`length()`	Array length	`WHERE length(tags) > 0`

Math Functions#

Function	Description	Example
`round()`	Round number	`SELECT round(AVG(spent_credits), 2)`
`floor()`	Round down	`SELECT floor(spent_credits / 100) * 100 as credit_bucket`
`ceil()`	Round up	`SELECT ceil(spent_credits)`
`abs()`	Absolute value	`SELECT abs(difference)`

Conditional Functions#

Function	Description	Example
`if()`	If-then-else	`SELECT if(outcome = 'VALID', 1, 0)`
`CASE`	Multi-condition	`CASE WHEN outcome = 'VALID' THEN 'success' ELSE 'failure' END`

Performance Tips#

Always filter by time - Use time-based WHERE clauses to leverage indexes
Use aggregated tables - Query hourly/daily/month tables for long ranges
Limit result sets - Add LIMIT clauses to prevent large results
Filter before grouping - Use WHERE instead of HAVING when possible
Avoid SELECT * - Only select columns you need

Query Limits#

Resource	Limit	Error Code
Execution time	30 seconds	`query_execution_timeout`
Memory usage	1 GB	`query_memory_limit_exceeded`
Rows to read	10 million	`query_rows_limit_exceeded`
Queries per hour	1000	`query_quota_exceeded`

See Query Restrictions for more details on query limits and restrictions.