Table Types

KalamDB has four table types at runtime:

• SYSTEM
• SHARED
• USER
• STREAM

In the backend, these are defined by TableType and carried through schema metadata, permission checks, storage routing, and SQL execution behavior.

Quick guidance

• Choose USER for tenant/user-isolated app data.
• Choose SHARED for global app data shared across users.
• Choose STREAM for ephemeral, append-heavy event feeds with TTL.
• Treat SYSTEM as internal metadata tables managed by the server.

SQL creation behavior

CREATE TABLE currently supports user-facing TYPE values: USER, SHARED, STREAM.

• If TYPE is omitted, backend parser defaults to SHARED.
• TTL_SECONDS is required for STREAM and rejected for non-stream tables.
• ACCESS_LEVEL is only valid for SHARED.
• USE_USER_STORAGE is only valid for USER.

Legacy forms like CREATE USER TABLE, CREATE SHARED TABLE, and CREATE STREAM TABLE are normalized by parser compatibility logic.

SYSTEM tables

SYSTEM tables are internal metadata/state tables (for example, entries exposed via system.* views/providers).

Used for: engine metadata, schema/state introspection, internal control-plane data.

Backend characteristics:

• Internal table type in TableType enum (System).
• Created/managed by system initialization and macros, not regular app DDL flows.
• Read/write access is role-gated (system/dba privileged paths).
• FILE upload/download paths reject SYSTEM tables.

SHARED tables

SHARED tables hold data in a single logical scope shared across users.

CREATE TABLE app.config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW()
) WITH (
  TYPE = 'SHARED',
  ACCESS_LEVEL = 'PRIVATE'
);

Used for: app configuration, common catalogs, feature flags, global reference entities.

Backend characteristics:

• Single shared scope (no per-user key partitioning).
• Supports ACCESS_LEVEL (PUBLIC, PRIVATE, RESTRICTED).
• Supports STORAGE_ID and FLUSH_POLICY.
• Supports hot + cold tier patterns (manifest/parquet flows).
• EXECUTE AS USER is rejected on shared tables in SQL API file path handling.

USER tables

USER tables are user-scoped and designed for multi-tenant isolation.

CREATE TABLE chat.messages (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  conversation_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
) WITH (
  TYPE = 'USER',
  STORAGE_ID = 'local',
  USE_USER_STORAGE = false,
  FLUSH_POLICY = 'rows:1000,interval:60'
);

Used for: per-user messages, preferences, personal documents, tenant-isolated records.

Backend characteristics:

• Write/read scope is user-aware (user_id participates in routing/keys).
• Supports table-level STORAGE_ID with optional USE_USER_STORAGE routing behavior.
• Supports FLUSH_POLICY and cold-tier lifecycle.
• FILE APIs support user scoping and optional impersonation checks.
• Compatible with live query and topic notification flows.

STREAM tables

STREAM tables are optimized for ephemeral event streams.

CREATE TABLE chat.typing_events (
  event_id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  user_id TEXT NOT NULL,
  event_type TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
) WITH (
  TYPE = 'STREAM',
  TTL_SECONDS = 30
);

Used for: presence/typing/cursor signals, short-lived event pipelines, low-latency transient feeds.

Backend characteristics:

• TTL_SECONDS is mandatory in SQL for stream tables.
• Hot-only path (stream log/in-memory style provider/store), no Parquet flush path.
• TTL filtering is applied during stream scans.
• Append-heavy event model; optimized for realtime notifications.
• FILE upload/download APIs reject stream tables.

Comparison

Which type should I pick?

• Need strict per-user isolation and optional per-user storage routing → USER
• Need one shared dataset for all users with access policy controls → SHARED
• Need short-lived events and continuous realtime processing → STREAM
• Need engine metadata/introspection → SYSTEM (managed by KalamDB internals)

USER Tables

User tables provide per-user data isolation. Each user gets their own physical storage directory, enabling true multi-tenant separation.

CREATE USER TABLE chat.messages (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  conversation_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
) WITH (
  FLUSH_POLICY = 'rows:1000,interval:60'
);

Best for: Chat messages, user preferences, personal data, per-user AI memory

Properties:

• Physically isolated per user
• Hot (RocksDB) + Cold (Parquet) tiers
• GDPR deletion = remove user directory
• Supports real-time subscriptions

SHARED Tables

Shared tables are accessible by all users and store global/common data.

CREATE SHARED TABLE app.config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW()
) WITH (
  ACCESS_LEVEL = 'PUBLIC'
);

Best for: Application config, shared content, cross-tenant analytics, reference data

Properties:

• Single storage location for all users
• Configurable access levels (PUBLIC, PRIVATE, RESTRICTED)
• Hot + Cold tiers
• Supports real-time subscriptions

STREAM Tables

Stream tables hold ephemeral data with automatic TTL eviction. Data is stored only in the hot tier and expires after the configured TTL.

CREATE STREAM TABLE chat.typing_events (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  user_id TEXT NOT NULL,
  event_type TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
) WITH (
  TTL_SECONDS = 30
);

Best for: Typing indicators, presence, cursor positions, ephemeral signals

Properties:

• Automatic expiry via TTL_SECONDS
• Hot tier only (no Parquet flush)
• Low overhead for transient data
• Ideal for real-time subscriptions

Comparison