ai-pms-mvp1

Tables (18)

access_tokens

  • token_id — text — Primary key — NOT NULL
  • user_id — text — Foreign key → users.user_id — NOT NULL
  • token_hash — text — NOT NULL
  • issued_at — timestamptz — NOT NULL
  • expires_at — timestamptz — NOT NULL
  • revoked_at — timestamptz
  • last_used_at — timestamptz

audit_logs

  • log_id — bigint — Primary key — NOT NULL
  • actor_user_id — text
  • action_type — text — NOT NULL
  • target_table — text — NOT NULL
  • target_id — text — NOT NULL
  • before_value — jsonb
  • after_value — jsonb
  • created_at — timestamptz — NOT NULL

collection_analysis_jobs

  • job_id — text — Primary key — NOT NULL
  • session_id — text — Foreign key → collection_upload_sessions.session_id
  • asset_id — text — Foreign key → collection_audio_assets.asset_id
  • project_id — text — NOT NULL
  • meeting_id — text — NOT NULL
  • transcript_text — text
  • language — text — NOT NULL
  • status — text — NOT NULL
  • priority — integer — NOT NULL
  • attempt_count — integer — NOT NULL
  • max_attempts — integer — NOT NULL
  • claimed_by — text
  • lease_expires_at — timestamptz
  • model_name — text
  • result_json — jsonb
  • last_error — text
  • platform_callback_status — text — NOT NULL
  • platform_callback_attempt_count — integer — NOT NULL
  • platform_callback_max_attempts — integer — NOT NULL
  • platform_callback_next_attempt_at — timestamptz
  • platform_callback_last_attempt_at — timestamptz
  • platform_callback_completed_at — timestamptz
  • platform_callback_last_error — text
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL
  • completed_at — timestamptz

collection_audio_assets

  • asset_id — text — Primary key — NOT NULL
  • session_id — text — Foreign key → collection_upload_sessions.session_id — NOT NULL
  • project_id — text — NOT NULL
  • meeting_id — text — NOT NULL
  • storage_uri — text
  • file_name — text
  • content_type — text
  • size_bytes — bigint
  • checksum_sha256 — text
  • duration_seconds — numeric
  • status — text — NOT NULL
  • validation_error — text
  • created_at — timestamptz — NOT NULL

collection_job_event_logs

  • event_id — bigint — Primary key — NOT NULL
  • job_id — text — NOT NULL
  • worker_id — text
  • event_type — text — NOT NULL
  • before_status — text
  • after_status — text
  • payload — jsonb — NOT NULL
  • created_at — timestamptz — NOT NULL

collection_upload_sessions

  • session_id — text — Primary key — NOT NULL
  • project_id — text — NOT NULL
  • meeting_id — text — NOT NULL
  • requested_by — text
  • file_name — text
  • content_type — text
  • expected_size_bytes — bigint
  • checksum_sha256 — text
  • upload_token_hash — text — NOT NULL
  • status — text — NOT NULL
  • expires_at — timestamptz
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

collection_workers

  • worker_id — text — Primary key — NOT NULL
  • worker_name — text
  • status — text — NOT NULL
  • current_job_id — text
  • last_heartbeat_at — timestamptz — NOT NULL
  • model_name — text
  • host_info — jsonb — NOT NULL
  • created_at — timestamptz — NOT NULL

company_profiles

  • company_id — text — Primary key — NOT NULL
  • company_name — text — NOT NULL
  • english_name — text
  • industry — text
  • founded_on — date
  • headquarters — text
  • ceo — text
  • fiscal_year — text
  • annual_revenue_krw — bigint
  • headcount — integer
  • project_count — integer
  • organization_summary — text
  • headcount_summary — text
  • note — text
  • source_file — text
  • metadata — jsonb — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

email_delivery_attempts

  • attempt_id — text — Primary key — NOT NULL
  • distribution_id — text — Foreign key → email_distributions.distribution_id — NOT NULL
  • recipient_email — text — NOT NULL
  • recipient_name — text
  • status — text — NOT NULL
  • provider_message_id — text
  • error_message — text
  • attempted_at — timestamptz — NOT NULL
  • attempt_no — integer — NOT NULL

email_distributions

  • distribution_id — text — Primary key — NOT NULL
  • meeting_id — text — Foreign key → meetings.meeting_id — NOT NULL
  • analysis_id — text — Foreign key → meeting_analyses.analysis_id — NOT NULL
  • subject — text — NOT NULL
  • body — text — NOT NULL
  • recipients — jsonb — NOT NULL
  • status — text — NOT NULL
  • delivery_mode — text — NOT NULL
  • requested_by — text — Foreign key → users.user_id
  • created_at — timestamptz — NOT NULL
  • sent_at — timestamptz
  • attempt_count — integer — NOT NULL
  • last_error — text
  • next_retry_at — timestamptz

meeting_analyses

  • analysis_id — text — Primary key — NOT NULL
  • meeting_id — text — Foreign key → meetings.meeting_id — NOT NULL
  • source_collection_job_id — text
  • source_asset_id — text
  • status — text — NOT NULL
  • model_name — text — NOT NULL
  • summary — text — NOT NULL
  • result_json — jsonb — NOT NULL
  • created_at — timestamptz — NOT NULL
  • approved_at — timestamptz

meeting_attendees

  • meeting_id — text — Foreign key → meetings.meeting_id — NOT NULL
  • user_id — text — Foreign key → users.user_id — NOT NULL
  • created_at — timestamptz — NOT NULL

meetings

  • meeting_id — text — Primary key — NOT NULL
  • project_id — text — Foreign key → projects.project_id — NOT NULL
  • title — text — NOT NULL
  • status — text — NOT NULL
  • audio_path — text
  • transcript — text
  • created_by — text
  • created_at — timestamptz — NOT NULL

password_reset_tokens

  • reset_token_id — text — Primary key — NOT NULL
  • user_id — text — Foreign key → users.user_id — NOT NULL
  • token_hash — text — NOT NULL
  • requested_email — text — NOT NULL
  • status — text — NOT NULL
  • expires_at — timestamptz — NOT NULL
  • used_at — timestamptz
  • last_verified_at — timestamptz
  • created_at — timestamptz — NOT NULL

project_members

  • user_id — text — Foreign key → users.user_id — NOT NULL
  • project_id — text — Foreign key → projects.project_id — NOT NULL
  • project_role — text — NOT NULL
  • created_at — timestamptz — NOT NULL
  • allocation_percent — numeric — NOT NULL
  • planned_mm — numeric — NOT NULL
  • staffing_note — text
  • annual_salary_krw — numeric
  • allocated_cost_krw — numeric

projects

  • project_id — text — Primary key — NOT NULL
  • name — text — NOT NULL
  • status — text — NOT NULL
  • pm_user_id — text
  • created_at — timestamptz — NOT NULL
  • description — text

schema_migrations

  • service — text — Primary key — NOT NULL
  • version — text — Primary key — NOT NULL
  • name — text — NOT NULL
  • checksum_sha256 — text — NOT NULL
  • applied_at — timestamptz — NOT NULL

users

  • user_id — text — Primary key — NOT NULL
  • employee_no — text — NOT NULL
  • name — text — NOT NULL
  • email — text
  • role — text — NOT NULL
  • password_hash — text — NOT NULL
  • status — text — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL