PostgreSQL Database Standards

Relevant source files

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

• AGENTS.md
• ARCHITECTURE.md
• mobile/schema.graphql
• README.md
• assets/js/app.js

PostgreSQL serves as the exclusive persistent datastore for the LiveCanvas project. It functions as the system of record for all durable facts, including user accounts, social relationships, content metadata, and session history. This document outlines the structural requirements, naming conventions, and architectural patterns mandated for database interactions within the Phoenix/Elixir backend.

The database is integrated into a modular monolith architecture where strictly defined context boundaries manage data access. While transient state is handled via OTP processes and Phoenix PubSub, any data requiring long-term persistence must adhere to the standards defined herein.

Sources: ARCHITECTURE.md:28-30, ARCHITECTURE.md:89-91

Core Data Modeling Standards

The project follows specific conventions for primary keys, timestamps, and data types to ensure consistency across the schema.

Primary Keys and Identifiers

Default relational tables must use bigint primary keys for internal referencing. In addition to the auto-incrementing primary key, tables must include an :entropy_id using the UUIDv7 format, which is generated directly by PostgreSQL. Certain tables, such as user_tokens, are permitted to use pure UUIDv7 identifiers as their primary key.

Sources: AGENTS.md:5-7

Timestamps and Data Types

All timestamps across the database must utilize the :utc_datetime_usec type to ensure microsecond precision and UTC consistency.

Sources: AGENTS.md:4

Table and Field Conventions

• Naming: Use snake_case for all table and column names.
• Normalization: High levels of normalization are required, particularly for identity data. For example, the users table must not contain primary email or phone columns; these are managed through dedicated join tables.
• Security: Secrets, such as tokens or passwords, must never be stored in raw form. Passwords use strong hashes, and persisted tokens use SHA3-based hashing.

Sources: ARCHITECTURE.md:144-147, AGENTS.md:8, ARCHITECTURE.md:210-211

Architectural Relationship

The following diagram illustrates how PostgreSQL interacts with the application layers and external storage.

Account and Identity Schema

The account system is highly normalized to support multiple linked identities (OAuth, Email, Phone, Passkeys) for a single user.

Essential Account Tables

Table	Purpose	Key Columns
users	Canonical account row	id, entropy_id, privacy_mode, moderation_flags
user_identities	Login-capable credentials	id, user_id, provider, uid, provider_metadata
email_addresses	Unique normalized emails	id, address (unique)
phone_numbers	Unique normalized phone numbers	id, number (E.164 unique)
user_tokens	Session and flow tokens	id (UUIDv7), user_id, secret_hash, context

Sources: ARCHITECTURE.md:150-188, AGENTS.md:5-8

User-Identity Relationship

Domain-Specific Storage

Content and Social

• Social: Relationships are stored in follows (managing requests/approvals) and blocks (overriding visibility).
• Content: The posts table stores durable user content, while media_assets tracks file metadata including processing states and S3 keys.
• Live: live_sessions records the lifecycle of broadcasts, including status transitions (starting, live, ended) and recording references.

Sources: ARCHITECTURE.md:191-207, mobile/schema.graphql:320-340

Token Management

Token persistence is restricted to hashes. Only the token hash is stored in user_tokens.secret_hash. Raw secrets are returned to the client once and are never stored in a recoverable form.

Sources: ARCHITECTURE.md:185-188, ARCHITECTURE.md:211

Data Access and Security Standards

Multi-Tenant and Ownership Rules

Direct access to database records via raw IDs or foreign keys is prohibited in public resolvers. Re-apply viewer authorization in all GraphQL node fetchers and child field resolvers to prevent Insecure Direct Object Reference (IDOR) vulnerabilities (CWE-639).

Sources: AGENTS.md:10-11

Schema Implementation

Ecto schemas must be kept under the LCSchemas boundary. These modules are strictly for schema definition and must not contain:

• Changeset building logic
• Repository operations (Repo calls)
• Workflow or business logic

Sources: ARCHITECTURE.md:54-57

Conclusion

PostgreSQL standards in LiveCanvas prioritize data integrity, microsecond-precision auditability, and a "Relay-first" GraphQL structure. By enforcing strict normalization for identities and using modern UUIDv7 identifiers alongside traditional keys, the system ensures scalability while maintaining a robust security posture through secret hashing and IDOR protection.

Sources: ARCHITECTURE.md:28-32, AGENTS.md:3-11