🧮 Supabase PostgreSQL Overview

We use Supabase as our backend database solution. It provides robust, scalable, and real-time PostgreSQL support with automatic APIs and authentication.

Our system is organized into two main databases:

📘 Bookings Database: Manages all user reservations, time slots, payments, and meeting room data.

🔌 Automation Database: Controls and logs SmartPod hardware components, sensor data, device permissions, and configurations.

1️⃣ Bookings Database Schema Documentation (Supabase PostgreSQL)

This document describes the structure of the Bookings database used in our application. It includes all tables, their fields, and brief descriptions.

Table: `amenities`

Column Name	Data Type	Description
id	integer	Primary key for the amenity.
name	text	Name of the amenity (e.g., WiFi, AC).
created_at	timestamp	Timestamp of when the amenity was created.
updated_at	timestamp	Last updated timestamp.

Table: `app_users`

Column Name	Data Type	Description
id	uuid	Primary key for the user.
email	varchar	Unique email address.
name	varchar	Full name of the user.
password_hash	varchar	Hashed password (if used).
role	text	Role (admin, user, etc.).
created_at	timestamp	When user was created.
updated_at	timestamp	When user was last updated.
user_id	uuid	Linked Supabase Auth user ID.
phone_number	text	Phone number of the user.
customer_id	uuid	Link to customer entity.

Table: `bookings`

Column Name	Data Type	Description
id	uuid	Primary key.
meeting_space_id	uuid	Foreign key to `meeting_spaces`.
user_id	uuid	Foreign key to `app_users`.
type	text	Type: 'Pay as you go' or 'Pre-booking'.
status	text	'Ongoing', 'Completed', 'Cancelled'.
start_time	timestamp	Start of booking.
end_time	timestamp	End of booking.
total_amount	numeric	Total cost of booking.
voucher_code	text	Applied voucher code.
payment_status	text	'Paid', 'Pending', etc.
room_indicators	jsonb	Extra indicators (e.g., light, fan).
cancellation_reason	text	Reason for cancellation.
created_at / updated_at	timestamp	Timestamps.
user_info	json	Cached user details.
booking_override	boolean	Allow override.
invoice_number	bigint	Unique invoice number.
meeting_space_event_id	text	External calendar event ID.
group_event_id	text	Group calendar ID.
payment_id	uuid	FK to `payments`.
unlock_key	text	For IoT unlock.
device_manage_url	text	URL to manage pod.
customer_id	uuid	FK to `customers`.
discount_amount	numeric	Total discount.
total_amount_to_paid	numeric	Final payable amount.
voucher_data	jsonb	Full voucher details.
voucher_redeem_id	text	Voucher redemption ref.
refunded_amount	numeric	Amount refunded.
refund_percentage	numeric	% refunded.

Table: `customers`

Column Name	Data Type	Description
id	uuid	Primary key.
name	varchar	Customer’s name.
address fields	varchar	Full address (street, city, etc.).
logo/footer_logo	text	Image URLs.
api_key	varchar	For internal API access.
customer_identifier	text	Unique 6-char ID.
email	text	Unique email.
slug	text	URL-safe identifier.
phone_number	text	Optional phone.
company_name	text	Org name.
is_active	boolean	Enabled/disabled.
created_at / updated_at	timestamp	Timestamps.

Table: `groups`

Column Name	Data Type	Description
id	uuid	Primary key.
name	varchar	Group name.
created_by / updated_by	uuid	User who created/updated.
calendar_id	text	Google Calendar ID.
time_zone	text	Time zone.
date fields	date	Validity range.
indoor_building_image / indoor_floor_plan	text	Image URLs.
customer_id	uuid	FK to `customers`.

Table: `group_meeting_space`

Column Name	Data Type	Description
id	uuid	Primary key.
group_id	uuid	FK to `groups`.
meeting_space_id	uuid	FK to `meeting_spaces`.

Table: `log_entries`

Column Name	Data Type	Description
id	uuid	Primary key.
entity_type	text	E.g., 'booking'.
entity_id	uuid	Referenced object.
action	text	E.g., 'update'.
user_id	uuid	FK to `app_users`.
user_role	text	Role of actor.
description	text	Action log.
created_at	timestamp	Timestamp.

Table: `meeting_space_slots`

Column Name	Data Type	Description
id	uuid	Primary key.
meeting_space_id	uuid	FK to `meeting_spaces`.
start_time / end_time	timestamp	Time slot.
is_available	boolean	True if available.

Table: `meeting_spaces`

Column Name	Data Type	Description
id	uuid	Primary key.
name / address	text	Name and location.
type	text	Pod, Table, or Room.
pay_per_hour	numeric	Price/hr.
is_paid	boolean	Free or paid.
payment_form	text	Booking method.
seats	int	Capacity.
working_hours	jsonb	Availability hours.
photos / amenities	array	Asset lists.
login_type	text	Guest, Account, Both.
auth_token	uuid	For secure access.
created_by / updated_by	uuid	FK to users.
main_image	text	Image URL.
status	text	Available, Unavailable.
calendar_id	text	Calendar reference.
time_zone / slug / state	text	Location-related data.
min_hrs_cancellation	int	Policy settings.
zeniot_meeting_space_id	text	External system ID.

Table: `payments`

Column Name	Data Type	Description
id	uuid	Primary key.
amount	numeric	Amount paid.
currency	text	Currency (USD).
stripe_payment_intent_id	text	Stripe reference.
status	text	'Pending', 'Paid', etc.
created_at	timestamp	Time of payment.

Table: `room_indicators`

Column Name	Data Type	Description
id	uuid	Primary key.
name	text	Indicator name.
key	text	Optional key.
created_at	timestamp	Timestamp.

2️⃣ Automation Database

This database is focused on smart device control, sensor states, and real-time environmental automation.

Table: `app_users`

Column	Type	Description
id	uuid	Primary key; unique user identifier
email	varchar	Unique email address
name	varchar	Full name of the user
password_hash	varchar	Hashed password
role	USER-DEFINED	User role
customer_id	uuid	FK to `customers.id`
created_at	timestamp	Record creation time
updated_at	timestamp	Last update time
user_id	uuid	FK to `auth.users.id`; external user ref
phone_number	text	Contact number

Table: `available_slots`

Column	Type	Description
id	bigint	Primary key
created_at	timestamptz	Creation time
start_datetime	timestamptz	Start time of the slot
end_datetime	timestamptz	End time of the slot
date	date	Calendar date
meeting_space_id	uuid	FK to `meeting_spaces.id`
is_active	boolean	Whether slot is available

Table: `booking`

Column	Type	Description
id	uuid	Primary key
created_at	timestamptz	Creation time
start_datetime	timestamptz	Booking start time
end_datetime	timestamptz	Booking end time
meeting_space_id	uuid	FK to `meeting_spaces.id`
created_by_name	text	Name of person who created the booking
created_by_email	text	Their email address
created_by_phone_no	text	Their contact number
unlock_key	text	Access key
external_id	text	External system ID
is_active	boolean	Active status
google_event_id	text	Google Calendar event ID

Table: `booking_attendees`

Column	Type	Description
booking_id	uuid	FK to `booking.id`
name	text	Attendee name
email	text	Attendee email
phone	text	Attendee phone number
id	uuid	Primary key

Table: `cron_logs`

Column	Type	Description
id	bigint	Primary key
created_at	timestamptz	Log creation time
last_run	timestamptz	Last run time of cron job
when	text	Time expression or schedule

Table: `customers`

Column	Type	Description
id	uuid	Primary key
name	varchar	Customer name
street	varchar	Address - street
city	varchar	Address - city
state	varchar	Address - state
zipcode	varchar	Address - zip code
country	varchar	Address - country
logo	text	Company logo URL
api_key	varchar	API integration key
is_active	boolean	Active flag
created_at	timestamp	Creation time
updated_at	timestamp	Last updated time
pod	bigint	Number of pods available
meetingroom	bigint	Number of meeting rooms
table	bigint	Number of tables
phone_number	text	Contact phone number
customer_identifier	text	Unique customer short ID
email	text	Contact email

Table: `device_log`

Column	Type	Description
device_id	varchar	Device ID
event_name	varchar	Name of the event
event_value	varchar	Value of the event
event_description	varchar	Description of the event
timest	timestamptz	Event timestamp

Table: `device_sensor_master`

Column	Type	Description
id	uuid	Primary key
name	text	Internal name
sensor_name	text	Display/logical sensor name
module	text	Module the sensor belongs to
type	text	`read` or `write`
created_at	timestamptz	Creation time

Table: `device_sensors`

Column	Type	Description
id	integer	Primary key
name	varchar	Sensor name
type	varchar	Type
manufacture	varchar	Manufacturer
device_id	varchar	FK to `devices.device_id`
sensor_id	varchar	Sensor ID
config	jsonb	Configuration object
is_enabled	boolean	Whether the sensor is active
sensor_type	varchar	Optional sensor classification

Table: `device_status`

Column	Type	Description
id	uuid	Primary key
created_at	timestamptz	Timestamp
status	varchar	Device status
data	jsonb	JSON data about the status
device_id	varchar	FK to `devices.device_id`

Table: `device_types`

Column	Type	Description
type_id	uuid	Primary key
type_name	varchar	Name of device type
type_description	text	Description of type

🗄️ Database Design Documentation

🧮 Supabase PostgreSQL Overview#

1️⃣ Bookings Database Schema Documentation (Supabase PostgreSQL)#

Table: amenities#

Table: app_users#

Table: bookings#

Table: customers#

Table: groups#

Table: group_meeting_space#

Table: log_entries#

Table: meeting_space_slots#

Table: meeting_spaces#

Table: payments#

Table: room_indicators#

2️⃣ Automation Database#

Table: app_users#

Table: available_slots#

Table: booking#

Table: booking_attendees#

Table: cron_logs#

Table: customers#

Table: device_log#

Table: device_sensor_master#

Table: device_sensors#

Table: device_status#

Table: device_types#

🧮 Supabase PostgreSQL Overview

1️⃣ Bookings Database Schema Documentation (Supabase PostgreSQL)

Table: `amenities`

Table: `app_users`

Table: `bookings`

Table: `customers`

Table: `groups`

Table: `group_meeting_space`

Table: `log_entries`

Table: `meeting_space_slots`

Table: `meeting_spaces`

Table: `payments`

Table: `room_indicators`

2️⃣ Automation Database

Table: `app_users`

Table: `available_slots`

Table: `booking`

Table: `booking_attendees`

Table: `cron_logs`

Table: `customers`

Table: `device_log`

Table: `device_sensor_master`

Table: `device_sensors`

Table: `device_status`

Table: `device_types`