Database
Naming Conventions
- Use snake_case for all identifiers
- Table names are singular (e.g., user not users)
- Prefix junction table names with parent table (e.g., user_device)
- Queries written in lowercase for readability
Table Structure
- created_at timestamptz not null
- updated_at timestamptz not null
- deleted boolean default false
- All timestamps stored in UTC
- auth.uid() for user context
Tables
Primary Keys
- Single tables: id bigint/uuid
- Junction tables: compound keys from both parents
create table user_device (
user_id uuid references user(id),
device_id uuid references device(id),
primary key (user_id, device_id)
)
Relationships
- Foreign keys required for referential integrity
- Junction table names reflect relationship (parent_child)
- Clear cascade rules defined
Columns
Data Types
- prefer jsonb over json for better performance
- timestamptz for all timestamps
- text for variable-length strings
- bigint for numeric ids
- uuid for public ids
Standard Columns
create table example (
id uuid primary key,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
deleted boolean not null default false
)
Enums
Implementation
create type status as enum (
'pending',
'active',
'suspended'
);
Usage
- Used for static lists that won’t change
- Document all possible values
- Consider backwards compatibility when modifying