Data Types & Indexing Strategies

Relevant source files

The following files were used as context for generating this wiki page:

• AGENTS.md
• ARCHITECTURE.md
• mobile/schema.graphql
• mobile/src/theme/tokens.ts
• assets/css/app.css

Introduction

The LiveCanvas backend utilizes PostgreSQL as its exclusive persistent datastore, adhering to a modular monolith architecture. The data strategy emphasizes strict normalization for core identities (emails and phone numbers) and a Relay-first GraphQL API design. This approach ensures high data integrity for critical account information while supporting high-concurrency realtime features like live streaming and chat.

The indexing and data type strategies are designed to support global refetchable IDs, cursor-based pagination, and secure ownership rules. By separating durable facts in PostgreSQL from ephemeral state in OTP/Presence, the system maintains a clean boundary between long-term storage and transient runtime coordination.
Sources: ARCHITECTURE.md:41-45, ARCHITECTURE.md:86-90, AGENTS.md:7-13

Primary Keys and Identifiers

LiveCanvas employs a dual-identifier strategy for relational tables to balance performance with global uniqueness and security.

Identification Standards

• Primary Keys: New relational tables default to bigint auto-incrementing primary keys for internal database efficiency and join performance.
• Entropy IDs: Tables include an :entropy_id using the UUIDv7 format. These are generated by Postgres and serve as the external, non-sequential identifiers exposed via the API.
• UUIDv7 Exceptions: Specific tables, such as user_tokens, are permitted to use pure UUIDv7 identifiers as their primary keys.
• Global IDs: The GraphQL API implements Relay-first global IDs, ensuring every Node has a globally unique identifier for refetching.

Sources: AGENTS.md:7-12, mobile/schema.graphql:16-19

Identifier Flow

The following diagram illustrates how internal BigInt keys relate to external UUIDv7 and GraphQL Global IDs.

The diagram shows the transformation of data identifiers from internal storage to client-facing representations.
Sources: AGENTS.md:7-12, mobile/schema.graphql:16-19

Data Type Specifications

The project enforces specific data types for timestamps, secrets, and normalized identity strings to maintain consistency across the Elixir backend and PostgreSQL.

Core Type Mapping

Entity Component	Data Type / Format	Description
Timestamps	:utc_datetime_usec	Microsecond precision UTC datetimes for all temporal fields.
Token Secrets	SHA3-based Hash	Persistent secrets are never stored raw; SHA3 is used for hashing.
Email Addresses	Normalized String	Unique, lower-case normalized values stored in dedicated tables.
Phone Numbers	E.164 String	Normalized international format for unique identification.
Metadata	jsonb	Flexible schema storage for provider-specific metadata.

Sources: AGENTS.md:8-14, ARCHITECTURE.md:144-159

Specialized GraphQL Types

The mobile schema defines several enums and scalars to enforce type safety at the API boundary:

• MediaProcessingState: PENDING_UPLOAD, UPLOADED, PROCESSED, FAILED.
• LiveSessionStatus: STARTING, LIVE, ENDED.
• UserPrivacyMode: PRIVATE, PUBLIC.

Sources: mobile/schema.graphql:439-444, mobile/schema.graphql:394-398, mobile/schema.graphql:522-525

Indexing and Normalization Strategies

The architecture avoids denormalization in core tables to prevent data anomalies, particularly regarding user contact information.

Normalization Hierarchy

Instead of storing emails or phone numbers directly on the users table, the system uses a normalized join structure:

This ER diagram represents the mandatory normalization of identity data.
Sources: ARCHITECTURE.md:139-165

Indexing for Performance and Security

1. Unique Constraints: Normalized identity tables (email_addresses, phone_numbers) use unique indexes on the normalized values to prevent duplicate account identities.
2. Relay Cursor Pagination: Indexing strategy must support cursor-based pagination for connections (e.g., homeFeed, chatMessages).
3. Authorization Checks: Indexes are designed to facilitate "viewer authorization" at the database level, ensuring global IDs do not bypass ownership rules (CWE-639 / IDOR protection).
4. Hashed Lookups: Token lookups are performed against secret_hash columns rather than raw values.

Sources: AGENTS.md:13-16, ARCHITECTURE.md:144-152, mobile/schema.graphql:8-15

Client-Side Tokens and Theming

While the backend handles primary data types, the system defines specific tokens for the mobile UI and web CSS to ensure visual consistency.

UI Design Tokens

Found in mobile/src/theme/tokens.ts:

• Spacing: xs: 4, sm: 8, md: 16, lg: 24, xl: 32 (Logical Pixels).
• Radius: sm: 8, md: 14, lg: 24, pill: 999.
• Typography: Specific fontSize and fontWeight for eyebrow, heading, body, and label.

Sources: mobile/src/theme/tokens.ts:4-40

Web Theme Tokens (DaisyUI)

The web frontend utilizes OKLCH color space for theme definitions in app.css.

/* Dark Theme Example */
--color-primary: oklch(58% 0.233 277.117);
--color-base-100: oklch(30.33% 0.016 252.42);

Sources: assets/css/app.css:26-30

Conclusion

The data type and indexing strategies in LiveCanvas prioritize security and scalability through strict normalization, the use of UUIDv7 entropy identifiers, and microsecond-precision timestamps. By leveraging PostgreSQL's relational strengths for durable facts and Elixir's OTP for ephemeral state, the system provides a robust foundation for high-performance realtime social features.
Sources: ARCHITECTURE.md:41-50, AGENTS.md:7-14