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