Skip to content

Database Schema

Overview

Table Model Purpose
user User All registered users
user_encryption_key UserEncryptionKey Per-user AES-256 keys
file_access FileAccess Shared folder/file grants
guest_download_link GuestDownloadLink Guest upload download tokens
lab_portal LabPortal Lab portal access grants
print_order PrintOrder Lab print orders
print_orders_public_link PrintOrdersPublicLink Public order status links
association Association Photography associations
admin_control_flags AdminControlFlags Global feature toggles
user_subscription UserSubscription Subscription plan records
collaboration_request CollaborationRequest Collaboration requests
collaboration_match CollaborationMatch Matched collaboration pairs
collaboration_schedule CollaborationSchedule Scheduled collaboration dates
notification Notification In-app notifications
collaboration_invite CollaborationInvite Email-based collaboration invites

user Table

Column Type Notes
id Integer PK Auto-increment
name String(100) Display name
email String(120), unique Login identifier
mobile String(15), nullable Optional
otp String(6) Current OTP for login
is_verified Boolean Email verified flag
is_admin Boolean Admin access flag
plan_opted String(100) FREE, DAILY, WEEKLY, MONTHLY, YEARLY
storage_limit Integer GB limit (default 25)
storage_used Float GB used (cached)
role String(50) Photographer, Model, StudioOwner, etc.
state, district, city String Location for collaboration
travel_distance_km Integer Max travel distance (default 25)
web_studioname String(150) Studio name for website
web_lnzcode String(20) Unique code for studio URL
web_access_token String(64), unique Token securing website data
websites_created_count Integer Number of websites created
enable_guest_upload Boolean Guest upload feature toggle
enable_lab_portal Boolean Lab portal access toggle
encrypt_data_b Boolean Encryption enabled for uploads
decrypt_data_b Boolean Decryption enabled for downloads
guest_upload_guidelines_accepted_hash String(64) SHA-256 of accepted guidelines version
guest_upload_guidelines_accepted_at DateTime When guidelines were accepted
created_at DateTime Account creation time
last_login DateTime Last login timestamp

user_encryption_key Table

Column Type Notes
id Integer PK Auto-increment
user_id Integer FK → user.id Owner
key_value Text AES-256 key encrypted with master key
key_version String(64) Version identifier (SHA-256 hash)
is_active Boolean Only one active key per user
created_at DateTime Key creation time

Multiple rows per user allowed for key rotation history. Only is_active=True row is used for new uploads.

Column Type Notes
id Integer PK —
owner_email String(120) Registered user who owns the storage
token String(80), unique secrets.token_urlsafe(32)
otp String(6) One-time access code
folder_name String(255) Folder in S3
created_at DateTime Link creation time
file_count Integer Number of uploaded files
total_size_mb Float Total upload size

lab_portal Table

Column Type Notes
id Integer PK —
user_email String FK → user.email, unique Lab owner
lab_id String(30), unique e.g., LNZ-LAB-XXXX
lab_pin String(4) 4-digit PIN
access_granted_at DateTime When admin granted access
granted_by_admin String(120) Admin email

Key columns (see lenzeye_BiodataStructure.py for full schema):

Column Notes
print_id Unique order ID
lab_id Target lab
status pending → files_downloaded → print_ready → out_for_delivery → delivered
status_link Public token for client tracking
download_link S3 link for lab to download files
Timestamp columns files_downloaded_at, print_ready_at, delivered_at, etc.

Migration Strategy

  • Migrations managed via Flask-Migrate (Alembic)
  • flask db migrate generates migration scripts
  • flask db upgrade applies them to production DB
  • Local dev: SQLite (DSS_local.db) for fast iteration
  • Production: PostgreSQL on Render
  • Schema divergence between local and prod is possible — always run flask db upgrade on deploy

TL;DR

Core tables: user, user_encryption_key, guest_download_link, print_order, lab_portal. Key relationships: user_encryption_key → user (one-to-many, for key rotation). lab_portal → user (one-to-one). Migrations: Flask-Migrate (Alembic). SQLite locally, PostgreSQL in production.