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