Core user and media schema

This reference describes the PostgreSQL baseline installed by the ordered users and media-library Supabase migrations. The authenticated media-upload capability uses these tables as its system of record; this page describes their persistence rules rather than its HTTP interface.

Identity and profiles

users contains the application-owned identity.

Column	Type	Rules
id	UUID	Primary key; generated by PostgreSQL.
identity_provider_id	text	Required and unique opaque identifier from the active authentication provider.
created_at, updated_at	timestamptz	Required creation and update timestamps.

identity_provider_id intentionally has no provider namespace. The system supports one authentication provider at a time. During a provider migration, update this value while retaining users.id; profile and media ownership use the stable internal UUID.

profiles is a zero-or-one extension of a user:

Column	Type	Rules
user_id	UUID	Primary key and foreign key to users.id.
display_name	text	Optional.
bio	text	Optional.
created_at, updated_at	timestamptz	Required creation and update timestamps.

Deleting a user cascades to its profile. A second profile for the same user is rejected because user_id is the profile primary key.

Upload attempts

media_uploads records an upload workflow before a file becomes durable media.

Column	Type	Rules
id	UUID	Primary key; generated by PostgreSQL.
owner_user_id	UUID	Required reference to users.id.
purpose	text	Required, trimmed non-empty value such as avatar or library.
status	media_upload_status	Required; defaults to pending.
original_filename, requested_content_type	text	Required intended file attributes.
expected_size_bytes	bigint	Required and non-negative.
checksum	text	Optional.
upload_key	text	Required, trimmed non-empty, and unique object-upload key.
expires_at	timestamptz	Required expiration time.
uploaded_at, completed_at	timestamptz	Optional lifecycle times.
created_at, updated_at	timestamptz	Required creation and update timestamps.

The closed media_upload_status enum permits only pending, uploaded, completed, failed, and expired. Uploads are indexed by (owner_user_id, status) for ownership and lifecycle lookups.

Durable media items

media_library_items records a finalized file.

Column	Type	Rules
id	UUID	Primary key; generated by PostgreSQL.
owner_user_id	UUID	Required reference to users.id.
media_upload_id	UUID	Required, unique source upload reference.
purpose	text	Required, trimmed non-empty use-case label.
final_key	text	Required, trimmed non-empty, unique durable S3 object key.
content_type, original_filename	text	Required file attributes.
size_bytes	bigint	Required and non-negative.
checksum	text	Optional.
metadata	JSONB	Required JSON object; defaults to {} for extensible attributes such as image dimensions.
created_at, updated_at	timestamptz	Required creation and update timestamps.

final_key is deliberately not a CloudFront URL. Delivery code combines it with the configured CloudFront hostname, allowing a distribution or domain change without rewriting database rows.

Relationship constraints

A media item can reference only one upload, and an upload can produce at most one media item. A composite foreign key requires a library item's owner_user_id to match the owner of its source upload. These rules make retries unable to create duplicate final media and reject cross-user finalization.

Media rows restrict deletion of their owner and source upload. Remove database rows and their external objects through a future explicit cleanup workflow; deleting a user does not cascade to media.

The database does not verify that a referenced upload is already completed. The media finalization command verifies the private object, sets that status, and creates its library item in one transaction. The command also owns authorization: these tables have no authentication-provider foreign keys, RLS policies, or direct browser access model.

Timestamp writers

PostgreSQL defaults created_at and updated_at when it inserts a row. The migrations do not install an update trigger. Repositories write updated_at explicitly when they update a row.

Related documentation

• Database connection explains the runtime and administrative connection boundary.
• Manage database migrations explains how to create and deploy later migrations.
• Authenticated media uploads explains the ownership, verification, and private delivery behavior that uses these records.