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