pin

Tables (27)

analysis_jobs

  • id — BIGSERIAL — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • status — analysis_job_status — NOT NULL
  • s3_report_key — VARCHAR(512)
  • error_message — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • completed_at — TIMESTAMPTZ

badges

  • badge_id — UUID — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • condition_type — VARCHAR(50) — NOT NULL
  • threshold — INTEGER — NOT NULL
  • image_url — TEXT — NOT NULL

card_characters

  • character_id — UUID — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • asset_url — TEXT — NOT NULL
  • active — BOOLEAN — NOT NULL

card_themes

  • theme_id — UUID — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • asset_url — TEXT — NOT NULL
  • active — BOOLEAN — NOT NULL

citizen_cards

  • card_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • character_id — UUID — Foreign key → card_characters.character_id — NOT NULL
  • theme_id — UUID — Foreign key → card_themes.theme_id — NOT NULL
  • display_name — VARCHAR(8) — NOT NULL
  • barcode_token — VARCHAR(255) — NOT NULL
  • issued_at — TIMESTAMPTZ — NOT NULL

data_sources

  • source_id — UUID — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • source_url — TEXT
  • updated_at — TIMESTAMPTZ — NOT NULL

mission_attempts

  • attempt_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • mission_id — UUID — Foreign key → missions.mission_id — NOT NULL
  • selected_option_id — UUID — Foreign key → mission_options.option_id
  • photo_url — TEXT
  • status — VARCHAR(20) — NOT NULL
  • attempted_at — TIMESTAMPTZ — NOT NULL
  • completed_at — TIMESTAMPTZ

mission_options

  • option_id — UUID — Primary key — NOT NULL
  • mission_id — UUID — Foreign key → missions.mission_id — NOT NULL
  • label — TEXT — NOT NULL
  • correct — BOOLEAN — NOT NULL
  • sort_order — INTEGER — NOT NULL

missions

  • mission_id — UUID — Primary key — NOT NULL
  • place_id — UUID — Foreign key → places.place_id — NOT NULL
  • mission_type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • reward_points — INTEGER — NOT NULL
  • geo_radius_m — INTEGER — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

notifications

  • notification_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • place_id — UUID — Foreign key → places.place_id
  • notification_type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • body — TEXT — NOT NULL
  • read_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

place_categories

  • category_id — UUID — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • sort_order — INTEGER — NOT NULL

places

  • place_id — UUID — Primary key — NOT NULL
  • category_id — UUID — Foreign key → place_categories.category_id — NOT NULL
  • source_id — UUID — Foreign key → data_sources.source_id
  • name — VARCHAR(150) — NOT NULL
  • description — TEXT
  • address — TEXT
  • latitude — NUMERIC(10, 7) — NOT NULL
  • longitude — NUMERIC(10, 7) — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

point_transactions

  • transaction_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • attempt_id — UUID — Foreign key → mission_attempts.attempt_id
  • place_id — UUID — Foreign key → places.place_id
  • transaction_type — VARCHAR(20) — NOT NULL
  • amount — INTEGER — NOT NULL
  • balance_after — INTEGER — NOT NULL
  • expires_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL

profile_reports

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_job_id — BIGINT — Foreign key → analysis_jobs.id — NOT NULL
  • total_commits — INT
  • total_prs — INT
  • avg_pr_merge_rate — DECIMAL(5,2)
  • top_languages — TEXT — NOT NULL
  • strengths — TEXT — NOT NULL
  • weaknesses — TEXT — NOT NULL
  • one_liner — TEXT — NOT NULL
  • confidence — DECIMAL(3,2)
  • created_at — TIMESTAMPTZ — NOT NULL

repositories

  • id — BIGSERIAL — Primary key — NOT NULL
  • analysis_job_id — BIGINT — Foreign key → analysis_jobs.id — NOT NULL
  • github_repo_id — BIGINT — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • full_name — VARCHAR(255) — NOT NULL
  • description — TEXT
  • primary_language — VARCHAR(50)
  • stars — INT — NOT NULL
  • forks — INT — NOT NULL
  • is_fork — BOOLEAN — NOT NULL
  • is_archived — BOOLEAN — NOT NULL
  • fetched_at — TIMESTAMPTZ — NOT NULL

repository_analyses

  • id — BIGSERIAL — Primary key — NOT NULL
  • repository_id — BIGINT — Foreign key → repositories.id — NOT NULL
  • code_additions — INT
  • code_deletions — INT
  • pr_open_count — INT — NOT NULL
  • pr_merged_count — INT — NOT NULL
  • pr_merge_rate — DECIMAL(5,2)
  • commit_frequency — DECIMAL(7,2)
  • review_approvals — INT — NOT NULL
  • review_changes_requested — INT — NOT NULL
  • bug_issue_count — INT — NOT NULL
  • feature_issue_count — INT — NOT NULL
  • strengths — TEXT — NOT NULL
  • weaknesses — TEXT — NOT NULL
  • one_liner — TEXT — NOT NULL
  • confidence — DECIMAL(3,2)
  • created_at — TIMESTAMPTZ — NOT NULL

saved_places

  • user_id — UUID — Foreign key — NOT NULL
  • place_id — UUID — Foreign key → places.place_id — NOT NULL
  • saved_at — TIMESTAMPTZ — NOT NULL

social_accounts

  • social_account_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • provider — VARCHAR(20) — NOT NULL
  • provider_user_id — VARCHAR(255) — NOT NULL
  • connected_at — TIMESTAMPTZ — NOT NULL

term_consents

  • consent_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • term_id — UUID — Foreign key → terms.term_id — NOT NULL
  • agreed — BOOLEAN — NOT NULL
  • agreed_at — TIMESTAMPTZ — NOT NULL

terms

  • term_id — UUID — Primary key — NOT NULL
  • term_type — VARCHAR(50) — NOT NULL
  • version — VARCHAR(20) — NOT NULL
  • required — BOOLEAN — NOT NULL
  • effective_at — TIMESTAMPTZ — NOT NULL

trip_sessions

  • trip_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • started_at — TIMESTAMPTZ — NOT NULL
  • ended_at — TIMESTAMPTZ
  • distance_km — NUMERIC(8, 2) — NOT NULL
  • visited_count — INTEGER — NOT NULL
  • calories_kcal — INTEGER — NOT NULL
  • earned_points — INTEGER — NOT NULL
  • point_action — VARCHAR(20)

user_badges

  • user_badge_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • badge_id — UUID — Foreign key → badges.badge_id — NOT NULL
  • progress — INTEGER — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • unlocked_at — TIMESTAMPTZ

user_github_tokens

  • id — BIGSERIAL — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • encrypted_token — TEXT — NOT NULL
  • token_scope — VARCHAR(512)
  • expires_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

user_settings

  • user_id — UUID — Foreign key — NOT NULL
  • nearby_notification — BOOLEAN — NOT NULL
  • haptic_enabled — BOOLEAN — NOT NULL
  • dark_mode — BOOLEAN — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

users

  • id — BIGSERIAL — Primary key — NOT NULL
  • github_id — BIGINT — NOT NULL
  • github_login — VARCHAR(39) — NOT NULL
  • github_name — VARCHAR(255)
  • github_email — VARCHAR(255)
  • avatar_url — VARCHAR(2048)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

visits

  • visit_id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key — NOT NULL
  • place_id — UUID — Foreign key → places.place_id — NOT NULL
  • location_verified — BOOLEAN — NOT NULL
  • visited_at — TIMESTAMPTZ — NOT NULL

widgets

  • id — BIGSERIAL — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • analysis_job_id — BIGINT — Foreign key → analysis_jobs.id — NOT NULL
  • username — VARCHAR(39) — NOT NULL
  • theme — VARCHAR(5) — NOT NULL
  • items — TEXT — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • svg_light_url — VARCHAR(2048)
  • svg_dark_url — VARCHAR(2048)
  • error_message — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • completed_at — TIMESTAMPTZ