Bunnect_1

Tables (20)

academies

  • id — UUID — Primary key — NOT NULL
  • code — VARCHAR(20) — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • phone — VARCHAR(20)
  • email — VARCHAR(255)
  • address — TEXT
  • logo_url — VARCHAR(500)
  • timezone — VARCHAR(50)
  • operating_hours — JSONB
  • metadata — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ

academy_managers

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → users.id — NOT NULL
  • user_id — UUID — NOT NULL
  • academy_id — UUID — NOT NULL
  • id (id3) — UUID — Foreign key → academies.id — NOT NULL

board_records

  • id — UUID — Primary key — NOT NULL
  • schedule_id — UUID — NOT NULL
  • student_id — UUID — NOT NULL
  • bus_stop_id — UUID — NOT NULL
  • event_type — VARCHAR(20) — NOT NULL
  • scheduled_time — TIMESTAMPTZ
  • actual_time — TIMESTAMPTZ
  • driver_id — UUID
  • notes — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • expected_students_count — INTEGER
  • actual_students_count — INTEGER
  • id (id2) — UUID — Foreign key → schedules.id — NOT NULL

bus_stops

  • id — UUID — Primary key — NOT NULL
  • academy_id — UUID — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • address — TEXT
  • latitude — DECIMAL(10, 8) — NOT NULL
  • longitude — DECIMAL(11, 8) — NOT NULL
  • photo_url — VARCHAR(500)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ

complaints

  • id — UUID — Primary key — NOT NULL
  • academy_id — UUID — NOT NULL
  • submitted_user_id — UUID — NOT NULL
  • subject — VARCHAR(255) — NOT NULL
  • description — TEXT — NOT NULL
  • priority — VARCHAR(20)
  • status — VARCHAR(20)
  • assigned_manager_id — UUID
  • resolution — TEXT
  • resolved_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • id (id2) — UUID — Foreign key → academies.id — NOT NULL

drivers

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → users.id — NOT NULL
  • user_id — UUID — NOT NULL
  • license_number — VARCHAR(20) — NOT NULL
  • license_verification — VARCHAR(20)

notifications

  • id — UUID — Primary key — NOT NULL
  • academy_id — UUID — NOT NULL
  • manager_id — UUID — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — TEXT — NOT NULL
  • priority — VARCHAR(20)
  • target_audience — JSONB
  • attachments — JSONB
  • expires_at — TIMESTAMPTZ
  • created_by_id — UUID
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • id (id2) — UUID — Foreign key → academies.id — NOT NULL

parents

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → users.id — NOT NULL
  • user_id — UUID — NOT NULL

parents_students

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → students.id — NOT NULL
  • id (id22) — UUID — Foreign key → students.id — NOT NULL
  • id (id3) — UUID — Foreign key → parents.id — NOT NULL
  • id (id23) — UUID — Foreign key → parents.id — NOT NULL
  • parent_id — UUID — NOT NULL
  • student_id — UUID — NOT NULL
  • relationship — VARCHAR(20) — NOT NULL
  • verification — VARCHAR(20)
  • created_at — TIMESTAMPTZ — NOT NULL
  • UNIQUE(parent_id, — student_id

ride_tracking

  • id — UUID — Primary key — NOT NULL
  • schedule_id — UUID — NOT NULL
  • gps_location — POINT — NOT NULL
  • previous_bus_stop — UUID — NOT NULL
  • next_bus_stop — UUID — NOT NULL
  • actual_time — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • id (id2) — UUID — Foreign key → schedules.id — NOT NULL

route_bus_stops

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → routes.id — NOT NULL
  • id (id3) — UUID — Foreign key → bus_stops.id — NOT NULL
  • route_id — UUID — NOT NULL
  • bus_stop_id — UUID — NOT NULL
  • sequence_order — INTEGER — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

routes

  • id — UUID — Primary key — NOT NULL
  • academy_id — UUID — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • manager_id — UUID
  • estimated_duration — INTEGER
  • status — VARCHAR(20)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • id (id2) — UUID — Foreign key → academies.id — NOT NULL

schedule_bus_stops

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → schedules.id — NOT NULL
  • schedule_id — UUID — NOT NULL
  • route_id — UUID — NOT NULL
  • bus_stop_id — UUID — NOT NULL
  • estimated_arrival_offset — INTEGER — NOT NULL
  • estimated_arrival_time — TIMETZ — NOT NULL
  • delay_time — INTERVAL — NOT NULL
  • UNIQUE(schedule_id, — bus_stop_id)

schedules

  • id — UUID — Primary key — NOT NULL
  • academy_id — UUID — NOT NULL
  • route_id — UUID — NOT NULL
  • repeat_days — TEXT — NOT NULL
  • driver_id — UUID
  • manager_id — UUID
  • vehicle_id — UUID
  • schedule_type — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20)
  • start_time — TIMESTAMPTZ — NOT NULL
  • end_time — TIMESTAMPTZ — NOT NULL
  • notes — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • UNIQUE(route_id, — repeat_days,
  • id (id2) — UUID — Foreign key → routes.id — NOT NULL
  • id (id3) — UUID — Foreign key → drivers.id — NOT NULL
  • id (id22) — UUID — Foreign key → drivers.id — NOT NULL

student_academies

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → students.id — NOT NULL
  • id (id22) — UUID — Foreign key → students.id — NOT NULL
  • id (id3) — UUID — Foreign key → academies.id — NOT NULL
  • academy_id — UUID — NOT NULL
  • student_id — UUID — NOT NULL
  • enrolled_at — TIMESTAMPTZ — NOT NULL
  • status — VARCHAR(20)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ

students

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → users.id — NOT NULL
  • user_id — UUID — NOT NULL
  • school_type — VARCHAR(255) — NOT NULL
  • school_name — VARCHAR(255) — NOT NULL
  • grade — VARCHAR(255) — NOT NULL
  • class — VARCHAR(255) — NOT NULL
  • secondary_contact — VARCHAR(255) — NOT NULL

students_schedules

  • id — UUID — Primary key — NOT NULL
  • id (id2) — UUID — Foreign key → schedules.id — NOT NULL
  • id (id3) — UUID — Foreign key → students.id — NOT NULL
  • id (id22) — UUID — Foreign key → students.id — NOT NULL
  • student_id — UUID — NOT NULL
  • schedule_id — UUID — NOT NULL
  • pickup_bus_stop_id — UUID
  • dropoff_bus_stop_id — UUID
  • effective_from — DATE — NOT NULL
  • effective_to — DATE
  • created_at — TIMESTAMPTZ — NOT NULL
  • UNIQUE(student_id, — schedule_id,

user_notifications

  • id — UUID — Primary key — NOT NULL
  • 공지ID (notifications_id) — UUID — Foreign key → notifications.id — NOT NULL
  • 사용자ID (user_id) — UUID — Foreign key → users.id — NOT NULL
  • delivery_method — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20)
  • sent_at — TIMESTAMPTZ
  • delivered_at — TIMESTAMPTZ
  • read_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • UNIQUE(notification_id, — user_id,

users

  • id — UUID — Primary key — NOT NULL
  • phone_number — VARCHAR(20) — NOT NULL
  • username — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(255)
  • birth_date — DATE — NOT NULL
  • profile_image_url — VARCHAR(500)
  • status — VARCHAR(20)
  • user_type — VARCHAR(20) — NOT NULL
  • last_login_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ

vehicles

  • id — UUID — Primary key — NOT NULL
  • academy_id — UUID — NOT NULL
  • license_plate — VARCHAR(20) — NOT NULL
  • model — VARCHAR(100)
  • capacity — INTEGER — NOT NULL
  • photo_url — VARCHAR(500)
  • insurance_info — JSONB
  • status — VARCHAR(20)
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL
  • deleted_at — TIMESTAMPTZ
  • id (id2) — UUID — Foreign key → academies.id — NOT NULL