373 lines
11 KiB
Plaintext
373 lines
11 KiB
Plaintext
// HKSingleParty
|
|
|
|
Table fake_auth.users {
|
|
id uuid [pk]
|
|
username varchar
|
|
remarks text
|
|
}
|
|
|
|
// table above this line is from supabase, for graphing / generating sql
|
|
|
|
// filename: 002_message_status.sql
|
|
Table message_status {
|
|
id serial [pk]
|
|
title text [not null, default: ''] // read, unread
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'message read/unread status'
|
|
}
|
|
|
|
// REQ0002/vip-tag
|
|
// store user service rank,
|
|
// `not_vip`, not vip
|
|
// `vip`, vip user
|
|
Table user_rank {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user vip status field'
|
|
}
|
|
|
|
// REQ0112/verified-tag
|
|
// store user verified status,
|
|
// `not_verified`, not verified
|
|
// `verified`, verified
|
|
Table user_verified {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user verified field'
|
|
}
|
|
|
|
// REQ0092/user-other-tags
|
|
Table user_other_tags {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user tags field'
|
|
}
|
|
|
|
// REQ0043/profile_detail
|
|
Table user_spoken_language {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user spoken language field'
|
|
}
|
|
|
|
// REQ0094/user-career-filter
|
|
Table user_career {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user career field'
|
|
}
|
|
|
|
// REQ0095/user-education-filter
|
|
Table user_education {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user education field'
|
|
}
|
|
|
|
// REQ0051/notification
|
|
// to extend -> Database design for notification system
|
|
// https://tannguyenit95.medium.com/designing-a-notification-system-1da83ca971bc
|
|
Table notifications {
|
|
id serial [pk]
|
|
content text [not null, default: '']
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'handle admin send notification to user'
|
|
}
|
|
|
|
// REQ0047/order-page
|
|
// REQ0042/event-detail
|
|
// store party event cancelled, ongoing
|
|
Table party_event_status {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store party event status'
|
|
}
|
|
|
|
// REQ0041/home_discover_event_tab
|
|
Table party_events {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
about_event text [not null, default: ''] // store about the event field
|
|
//
|
|
event_date TIMESTAMPTZ [not null, default: `now()`]
|
|
event_duration_min integer [not null, default: 90]
|
|
//
|
|
price integer [not null, default: 999]
|
|
currency text [not null, default: 'HKD']
|
|
last_payment_time TIMESTAMPTZ [not null, default: '2999-01-01']
|
|
//
|
|
address text [not null, default: '']
|
|
image_urls text[] [default: '{}']
|
|
//
|
|
status integer [not null, ref: > party_event_status.id, default: 0]
|
|
//
|
|
// replaced by party_event_orders table
|
|
// participants uuid [not null, ref: <> fake_auth.users.id]
|
|
join_boy integer [not null, default: 999]
|
|
join_girl integer [not null, default: 999]
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
remarks text [not null, default: '']
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store party events'
|
|
}
|
|
|
|
Table user_genders {
|
|
id serial [pk]
|
|
title text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`] // order time ?
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user party event orders status'
|
|
}
|
|
|
|
// REQ0053/Profile-page
|
|
Table profiles {
|
|
// provided by supabase auth
|
|
id uuid [pk, ref: - fake_auth.users.id]
|
|
//
|
|
user_id serial [pk, unique]
|
|
website text [not null, default: '']
|
|
gender integer [not null, ref: - user_genders.id, default: 1]
|
|
|
|
// store user bookmarked event
|
|
bookmark_events integer [unique, ref: <> party_events.id]
|
|
|
|
// REQ0044/near_by_page
|
|
// TODO: fix coordinates later
|
|
// coordinates point [default: 'POINT(114.1759 22.3271)']
|
|
|
|
// REQ0099/profile-edit-form start
|
|
avatar_urls text[] [default: '{}']
|
|
about_user text [not null, default: '']
|
|
|
|
spoken_language integer [unique, ref: <> user_spoken_language.id]
|
|
career integer [unique, ref: <> user_career.id]
|
|
user_rank integer [unique, ref: <> user_rank.id] // store user vip rank
|
|
verified integer [unique, ref: - user_verified.id]
|
|
weight_kg integer [not null, default: 100]
|
|
height_cm integer [not null, default: 100]
|
|
education integer [unique, ref: - user_education.id]
|
|
|
|
other_tags integer [unique, ref: <> user_other_tags.id]
|
|
// REQ0099/profile-edit-form end
|
|
|
|
// store last onlnie information for My-nearby
|
|
last_seen TIMESTAMPTZ [not null, default: `now()`]
|
|
|
|
// get age
|
|
year_of_birth integer [default: 1970] // default to Jan-01 to get age
|
|
|
|
// REQ0051/notification
|
|
notification_read integer [unique, ref: <> notifications.id]
|
|
// REQ0051/notification
|
|
|
|
|
|
block_user_id text[] [not null, default: '{}']
|
|
|
|
// handle user delete account
|
|
// is_user_suspend -> user delete account
|
|
// is_admin_suspend -> admin delete user account
|
|
suspend_at TIMESTAMPTZ [default: '2999-01-01']
|
|
is_user_suspend integer [not null, default: 0] // 0 => not suspended
|
|
is_admin_suspend integer [not null, default: 0] // 0 => not suspended
|
|
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user meta'
|
|
}
|
|
|
|
// REQ0050/chat-room-list (messages)
|
|
Table messages {
|
|
id serial [pk]
|
|
content text [not null, default: '']
|
|
media_url varchar(255)
|
|
|
|
sender_user_id integer [not null, ref: > profiles.user_id]
|
|
receiver_user_id integer [not null, ref: > profiles.user_id]
|
|
|
|
// NOTE: ?? not sure correct or not for these
|
|
// message_arrived integer [not null, unique, ref: <> profiles.user_id]
|
|
// message_read integer [not null, unique, ref: <> profiles.user_id]
|
|
// message_archived integer [not null, unique, ref: <> profiles.user_id]
|
|
// message_deleted integer [not null, unique, ref: <> profiles.user_id]
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store in-app chat'
|
|
}
|
|
|
|
Table visit_user_profile_read_status {
|
|
id serial [pk]
|
|
title text [not null, default: ''] // unread -> read
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store visit status read by host'
|
|
}
|
|
|
|
// REQ0050/chat-room-list
|
|
// visitor => some people visit host profile
|
|
// host => profile that visitor visit
|
|
// on host perspective, store who visit host profile
|
|
Table visit_user_profile {
|
|
id serial [pk]
|
|
//
|
|
visitor_user_id integer [not null, ref: > profiles.user_id]
|
|
visit_at TIMESTAMPTZ [not null, default: `now()`]
|
|
|
|
// question, why i think `<` is correct ?
|
|
host_user_id integer [not null, ref: > profiles.user_id]
|
|
host_user_read integer [not null, ref: > visit_user_profile_read_status.id, default: 1]
|
|
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
remarks text [not null, default: '']
|
|
//
|
|
note: 'history list, store user profile visited by others, used in near-by page'
|
|
}
|
|
|
|
// not-paid, user not paid for event
|
|
// paid, user paid for event
|
|
Table party_event_order_status {
|
|
id serial [pk]
|
|
title text [not null, default: ''] // pending, paid
|
|
remarks text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`] // order time ?
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user party event orders status'
|
|
}
|
|
|
|
// REQ0047/order-page
|
|
Table party_event_orders {
|
|
id serial [pk]
|
|
user_id integer [not null, ref: - profiles.user_id]
|
|
party_event_id integer [not null, ref: - party_events.id]
|
|
//
|
|
status integer [not null, ref: - party_event_order_status.id]
|
|
payment_time TIMESTAMPTZ [not null, default: '2999-01-01']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`] // order time
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
remarks text [default: '']
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user party event orders'
|
|
}
|
|
|
|
// REQ0057/contact-support
|
|
Table contact_us_message {
|
|
id serial [pk]
|
|
content text [not null, default: '']
|
|
attachments text[] [default: '{}']
|
|
read_status integer [not null, ref: - message_status.id, default: 1]
|
|
//
|
|
from_user_id integer [not null, ref: - profiles.user_id]
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
remarks text [not null, default: '']
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user to admin messages'
|
|
}
|
|
|
|
// REQ0084/select-report-category
|
|
Table report_user_reason {
|
|
id serial [pk]
|
|
content text [not null, default: '']
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
remarks text [not null, default: '']
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store user to admin messages'
|
|
}
|
|
|
|
// REQ0046/report-form
|
|
Table report_user {
|
|
id serial [pk]
|
|
content text [not null, default: '']
|
|
attachments text[] [default: '{}']
|
|
reason integer [not null, ref: - report_user_reason.id]
|
|
//
|
|
report_user_id integer [not null, ref: - profiles.user_id] // store user id to report
|
|
from_user_id integer [not null, ref: - profiles.user_id] // store report sender
|
|
//
|
|
create_at TIMESTAMPTZ [not null, default: `CURRENT_TIMESTAMP`]
|
|
update_at TIMESTAMPTZ [not null, default: `now()`]
|
|
remarks text [not null, default: '']
|
|
meta json [not null, default: `'{}'::json`]
|
|
//
|
|
note: 'store report user messages'
|
|
}
|