Database Schema

The Postcard database (postcard-db) is a Cloudflare D1 instance. Below are the primary tables and their relationships.

Tables

users

Core user profiles.

• user_id: UUID primary key.
• public_id: 8-character opaque ID for URLs.
• phone_e164: Primary identity.
• phone_hash: For contact matching.

posts

Content created by users.

• owner_user_id: FK to users.
• deleted_at: Soft-delete timestamp.

post_media

Individual images/videos in a post carousel.

• post_id: FK to posts.
• image_id: Cloudflare Images reference.
• stream_uid: Cloudflare Stream reference.

friends

Mutual relationship mapping.

• user_id_a, user_id_b: Ordered pair of user IDs.

post_reports

User-submitted reports for moderation.

• status: 'pending' or 'actioned'.

user_logins

Audit log for security and admin visibility.

• Includes IP address and country.