Table Types

KalamDB uses four runtime table types. The table type decides data scope, write path, storage layout, live-query routing, and which role checks run before the server exposes rows.

USER
SHARED
STREAM
SYSTEM (internal)

Table-Type Routing Model

Quick Selection

Choose	When the data is	Typical examples
`USER`	Private to the authenticated or effective user	chat messages, agent memory, preferences, tenant records
`SHARED`	Global within the namespace and governed by table policy	feature flags, catalogs, plans, public reference data
`STREAM`	Short-lived and user-scoped	typing indicators, presence, cursor positions, transient events
`SYSTEM`	Engine metadata or runtime state	users, schemas, jobs, cluster views, transactions

Application SQL can create USER, SHARED, and STREAM tables. SYSTEM tables are created and evolved by the server.

For SQL syntax, see /docs/server/sql-reference/tables.

SQL Creation Patterns

Recommended unified syntax:

SQL

1CREATE TABLE app.messages (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  content TEXT NOT NULL,4  created_at TIMESTAMP DEFAULT NOW()5) WITH (TYPE = 'USER');

Also supported for compatibility:

CREATE USER TABLE ...
CREATE SHARED TABLE ...
CREATE STREAM TABLE ...

If neither a typed prefix nor WITH (TYPE = ...) is supplied, the parser currently defaults to a SHARED table. Prefer setting TYPE explicitly in new application SQL so the data boundary is obvious in reviews.

USER Tables

Per-user logical isolation with user-aware routing and permissions.

SQL

1CREATE TABLE chat.messages (2  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  conversation_id BIGINT NOT NULL,4  content TEXT NOT NULL,5  created_at TIMESTAMP DEFAULT NOW()6) WITH (7  TYPE = 'USER',8  FLUSH_POLICY = 'rows:1000,interval:60',9  STORAGE_ID = 'local'10);

Use for: conversations, preferences, private records, tenant data.

USER Table Behavior

Rows are scoped by the authenticated or effective user_id from the session.
Reads do not implicitly cross user boundaries, even for system, dba, or service roles.
Cross-user work must use an authorized impersonation flow such as EXECUTE AS USER.
USER tables require a primary key.
STORAGE_ID, USE_USER_STORAGE, and FLUSH_POLICY apply to this table type.
Hot rows can flush into cold Parquet segments. The flush scope is (table, user).
FILE columns and file APIs are supported.
Live queries are evaluated in the user-scoped partition.

USER Role Access

Operation	Anonymous	User	Service	DBA	System
Read own/effective `USER` scope	No	Yes	Yes	Yes	Yes
Insert/update/delete own/effective `USER` scope	No	Yes	Yes	Yes	Yes
Create `USER` table	No	No	Yes	Yes	Yes
Alter `USER` table	No	No	Yes	Yes	Yes
Drop `USER` table	No	No	No	Yes	Yes

SHARED Tables

Global shared scope for all users.

SQL

1CREATE TABLE app.config (2  key TEXT PRIMARY KEY,3  value TEXT NOT NULL,4  updated_at TIMESTAMP DEFAULT NOW()5) WITH (6  TYPE = 'SHARED',7  ACCESS_LEVEL = 'PRIVATE'8);

Use for: reference data, global settings, shared catalogs.

SHARED Table Behavior

Rows are not partitioned by user_id.
Access is controlled by the table’s ACCESS_LEVEL option.
If ACCESS_LEVEL is omitted, the server stores PRIVATE.
SHARED tables require a primary key.
STORAGE_ID, FLUSH_POLICY, and ACCESS_LEVEL apply to this table type.
USE_USER_STORAGE is rejected because shared data has no user-specific storage scope.
Hot rows can flush into cold Parquet segments. The flush scope is the shared table scope.
FILE columns and file APIs are supported.
Live queries use a shared-table subscription path optimized for large fan-out.

ACCESS_LEVEL Semantics

The SQL parser accepts PUBLIC, PRIVATE, RESTRICTED, and DBA for ACCESS_LEVEL on shared tables. Values are case-insensitive. PRIVATE and RESTRICTED currently enforce the same runtime role matrix; keep RESTRICTED for data you want to label as elevated service/admin-only access.

`ACCESS_LEVEL`	Read: Anonymous	Read: User	Read: Service	Read: DBA	Read: System	Write: User	Write: Service	Write: DBA	Write: System
`PUBLIC`	Yes	Yes	Yes	Yes	Yes	No	Yes	Yes	Yes
`PRIVATE`	No	No	Yes	Yes	Yes	No	Yes	Yes	Yes
`RESTRICTED`	No	No	Yes	Yes	Yes	No	Yes	Yes	Yes
`DBA`	No	No	No	Yes	Yes	No	No	Yes	Yes

Notes:

PUBLIC means readable by every session role, including anonymous session context, but regular user role sessions still cannot write.
PRIVATE is the default and is service/admin read-write.
DBA is the strictest shared-table option and excludes service roles.
DDL still has its own gate: service, dba, and system can create or alter application tables; regular user sessions cannot create, alter, or drop tables.

Changing Shared Access

Two ALTER forms are supported:

SQL

1ALTER TABLE app.config SET TBLPROPERTIES (ACCESS_LEVEL = 'PUBLIC');2ALTER TABLE app.config SET ACCESS LEVEL public;

The SET TBLPROPERTIES form accepts PUBLIC, PRIVATE, RESTRICTED, and DBA. The shorthand SET ACCESS LEVEL ... form currently accepts PUBLIC, PRIVATE, and RESTRICTED.

STREAM Tables

Ephemeral rows with TTL-based eviction. Like USER tables, STREAM tables are scoped per user tenant and are not shared across all users.

SQL

1CREATE TABLE chat.typing_events (2  event_id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),3  user_id TEXT NOT NULL,4  event_type TEXT NOT NULL,5  created_at TIMESTAMP DEFAULT NOW()6) WITH (7  TYPE = 'STREAM',8  TTL_SECONDS = 309);

Use for: typing indicators, presence, cursor events, transient signals.

STREAM Table Behavior

Rows are scoped by the authenticated or effective user_id.
TTL_SECONDS is required in SQL creation.
Stream rows are evicted by the stream lifecycle rather than flushed into Parquet cold storage.
STREAM tables use the same read/write permission gate as USER tables.
FILE columns and file APIs are not supported.
Live queries are supported and are commonly used for transient UI state.

Realtime Subscriptions

Today, KalamDB supports realtime subscriptions to all three primary application table types: USER, SHARED, and STREAM. This allows applications to listen for live changes (inserts, updates, deletes) as they happen, enabling reactive UIs and realtime workflows.

Subscriptions can be full-row (SELECT *) or projected (SELECT username, status FROM chat.changes ...) depending on the payload shape your client needs.

For the delivery path, shared-table fan-out, and reconnect semantics, see /docs/server/architecture/live-query.

SYSTEM Tables

Internal control-plane and metadata tables (system.*).

not intended for normal app DDL lifecycle
role-restricted access for sensitive operations
file upload/download flows reject SYSTEM tables
direct DML against system.* tables is blocked for normal application workflows
computed system.* views expose runtime metadata such as sessions, transactions, cluster state, and jobs

SYSTEM table access is restricted to dba and system roles. service, user, and anonymous roles cannot read system tables through the normal table-access gate.

For inspectable system views, see /docs/server/sql-reference/system-views.

Table Options By Type

Option	`USER`	`SHARED`	`STREAM`	`SYSTEM`	Server behavior
`TYPE`	Yes	Yes	Yes	No app DDL	Selects table type when not using `CREATE USER/SHARED/STREAM TABLE`.
`STORAGE_ID`	Yes	Yes	Parsed but not used	Internal	Defaults to `local`; must reference an existing storage backend.
`USE_USER_STORAGE`	Yes	No	No	No	Valid only on `USER`; rejected on `SHARED`/`STREAM`.
`FLUSH_POLICY`	Yes	Yes	Parsed but no flush path	Internal	Triggers hot-to-cold movement for `USER` and `SHARED`.
`DELETED_RETENTION_HOURS`	Parsed	Parsed	Parsed	Internal	Parsed into the DDL statement; runtime deletion retention is handled by server lifecycle code.
`TTL_SECONDS`	No	No	Required	No	Valid only on `STREAM`; omitted TTL is rejected.
`ACCESS_LEVEL`	No	Yes	No	No	Valid only on `SHARED`; omitted value defaults to `PRIVATE`.

For storage templates and STORAGE_ID examples, see /docs/server/sql-reference/storage-id-usage.

DDL Role Matrix

DDL operation	Anonymous	User	Service	DBA	System
Create `USER` / `SHARED` / `STREAM` table	No	No	Yes	Yes	Yes
Create `SYSTEM` table	No	No	No	No	No via app SQL
Alter application table	No	No	Yes	Yes	Yes
Drop application table	No	No	No	Yes	Yes
Create view	No	No	No	Yes	Yes

Capability Matrix

Capability	USER	SHARED	STREAM	SYSTEM
App DDL creation	Yes	Yes	Yes	Internal only
Per-user isolation	Yes	No	Yes	Internal
`ACCESS_LEVEL` option	No	Yes	No	No
`TTL_SECONDS` option	No	No	Required	No
`USE_USER_STORAGE` option	Yes	No	No	No
Cold-tier Parquet lifecycle	Yes	Yes	No	Internal-specific
File APIs supported	Yes	Yes	No	No
Live Query	Yes	Yes	Yes	No
Cluster multi-group Raft support	Yes	Yes	Yes	Internal

Capability Definitions

App DDL creation: Whether application SQL can create/manage this table type directly.
Per-user isolation: Whether data is automatically isolated by authenticated user/tenant.
ACCESS_LEVEL option: Whether table-level access policy can be configured through ACCESS_LEVEL.
TTL_SECONDS option: Whether the table supports TTL-based automatic row eviction.
USE_USER_STORAGE option: Whether table storage can be explicitly pinned to user-scoped storage paths.
Cold-tier Parquet lifecycle: Whether hot data can flush/compact into cold-tier Parquet segments.
File APIs supported: Whether /v1/files/... upload/download flows are supported for rows in this table type.
Live Query: Whether realtime subscriptions can stream change events from this table type.
Cluster multi-group Raft support: Whether metadata/data operations for this table type participate in clustered Raft replication groups.