catch_me
Tables (13)
bush_clues
- id — BIGSERIAL — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id — NOT NULL
- crop_x — DECIMAL(5, 4) — NOT NULL
- crop_y — DECIMAL(5, 4) — NOT NULL
- crop_size — DECIMAL(5, 4) — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
campus_zones
- id — BIGSERIAL — Primary key — NOT NULL
- name — VARCHAR(100) — NOT NULL
- type — VARCHAR(30) — NOT NULL
- latitude — DECIMAL(10, 7) — NOT NULL
- longitude — DECIMAL(10, 7) — NOT NULL
- radius_meters — INT — NOT NULL
- model_type — VARCHAR(50)
- description — TEXT
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
cat_identification_candidates
- id — BIGSERIAL — Primary key — NOT NULL
- photo_id — BIGINT — Foreign key → cat_photos.id — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id — NOT NULL
- image_similarity_score — DECIMAL(5, 4) — NOT NULL
- location_score — DECIMAL(5, 4)
- final_score — DECIMAL(5, 4) — NOT NULL
- rank_order — INT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
cat_photo_embeddings
- id — BIGSERIAL — Primary key — NOT NULL
- photo_id — BIGINT — Foreign key → cat_photos.id — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id
- model_name — VARCHAR(100) — NOT NULL
- embedding — DOUBLE PRECISION — NOT NULL
- crop_image_url — TEXT
- quality_score — DECIMAL(5, 4)
- created_at — TIMESTAMPTZ — NOT NULL
cat_photos
- id — BIGSERIAL — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id
- image_url — TEXT — NOT NULL
- latitude — DECIMAL(10, 7) — NOT NULL
- longitude — DECIMAL(10, 7) — NOT NULL
- zone_id — BIGINT — Foreign key → campus_zones.id
- taken_at — TIMESTAMPTZ — NOT NULL
- uploaded_at — TIMESTAMPTZ — NOT NULL
- is_cat — BOOLEAN — NOT NULL
- cat_detection_confidence — DECIMAL(5, 4)
- cat_identification_confidence — DECIMAL(5, 4)
- is_gallery_visible — BOOLEAN — NOT NULL
- is_representative — BOOLEAN — NOT NULL
- identification_status — VARCHAR(30) — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
cat_placements
- id — BIGSERIAL — Primary key — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id — NOT NULL
- source_sighting_id — BIGINT — Foreign key → cat_sightings.id
- latitude — DECIMAL(10, 7) — NOT NULL
- longitude — DECIMAL(10, 7) — NOT NULL
- zone_id — BIGINT — Foreign key → campus_zones.id
- surface — VARCHAR(30) — NOT NULL
- anchor_key — VARCHAR(50)
- height_offset_meters — DECIMAL(7, 2) — NOT NULL
- movement_radius_meters — DECIMAL(7, 2) — NOT NULL
- animation_key — VARCHAR(50) — NOT NULL
- animation_started_at — TIMESTAMPTZ — NOT NULL
- animation_expires_at — TIMESTAMPTZ
- selected_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
cat_sightings
- id — BIGSERIAL — Primary key — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- photo_id — BIGINT — Foreign key → cat_photos.id — NOT NULL
- latitude — DECIMAL(10, 7) — NOT NULL
- longitude — DECIMAL(10, 7) — NOT NULL
- zone_id — BIGINT — Foreign key → campus_zones.id
- seen_at — TIMESTAMPTZ — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
cats
- id — BIGSERIAL — Primary key — NOT NULL
- name — VARCHAR(50)
- description — TEXT
- representative_photo_id — BIGINT
- representative_photo_url — TEXT
- first_seen_at — TIMESTAMPTZ
- last_seen_at — TIMESTAMPTZ
- pattern — VARCHAR(30)
- personality — TEXT
- default_latitude — DECIMAL(10, 7)
- default_longitude — DECIMAL(10, 7)
- default_zone_id — BIGINT — Foreign key → campus_zones.id
- status — VARCHAR(20) — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL
email_verifications
- id — BIGSERIAL — Primary key — NOT NULL
- email — VARCHAR(255) — NOT NULL
- code_hash — VARCHAR(255) — NOT NULL
- expires_at — TIMESTAMPTZ — NOT NULL
- consumed_at — TIMESTAMPTZ
- attempts — INT — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
refresh_tokens
- id — BIGSERIAL — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- token_hash — VARCHAR(255) — NOT NULL
- expires_at — TIMESTAMPTZ — NOT NULL
- revoked_at — TIMESTAMPTZ
- created_at — TIMESTAMPTZ — NOT NULL
user_cat_collections
- id — BIGSERIAL — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id — NOT NULL
- first_discovered_at — TIMESTAMPTZ — NOT NULL
- last_seen_at — TIMESTAMPTZ
- discovery_photo_id — BIGINT — Foreign key → cat_photos.id
- representative_photo_id — BIGINT — Foreign key → cat_photos.id
- is_favorite — BOOLEAN — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
user_exp_events
- id — BIGSERIAL — Primary key — NOT NULL
- user_id — BIGINT — Foreign key → users.id — NOT NULL
- event_type — VARCHAR(30) — NOT NULL
- exp_amount — INT — NOT NULL
- cat_id — BIGINT — Foreign key → cats.id
- photo_id — BIGINT — Foreign key → cat_photos.id
- zone_id — BIGINT — Foreign key → campus_zones.id
- metadata — JSONB
- created_at — TIMESTAMPTZ — NOT NULL
users
- id — BIGSERIAL — Primary key — NOT NULL
- username — VARCHAR(50) — NOT NULL
- password_hash — VARCHAR(255) — NOT NULL
- email — VARCHAR(255)
- auth_provider — VARCHAR(20) — NOT NULL
- provider_user_id — VARCHAR(255)
- nickname — VARCHAR(50) — NOT NULL
- nickname_onboarded — BOOLEAN — NOT NULL
- profile_image_url — TEXT
- role — VARCHAR(20) — NOT NULL
- created_at — TIMESTAMPTZ — NOT NULL
- updated_at — TIMESTAMPTZ — NOT NULL