Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• database/migrations/0001_01_01_000000_create_users_table.php
• database/migrations/2025_11_15_042414_create_whatsapp_accounts_table.php
• database/migrations/2025_11_15_042422_create_whatsapp_templates_table.php
• database/migrations/2025_12_04_000006_create_products_table.php
• database/migrations/2025_12_04_000007_create_orders_table.php
• database/migrations/2025_12_06_000001_create_subscriptions_table.php
• app/Services/EmbeddedSignupService.php
• app/Services/TemplateService.php
• resources/views/dashboard/templates.blade.php

The database schema for QashierWise is designed to support a multi-tenant SaaS application that integrates WhatsApp Business functionality with a Point of Sale (POS) system for restaurants. The schema is built around a central users table, which links to various modules such as WhatsApp account management, message templates, user subscriptions, and POS entities like products and orders.

The design utilizes foreign key constraints to maintain relational integrity and JSON columns for storing semi-structured data, such as WhatsApp message components and order items. This provides both structure and flexibility to handle the diverse data requirements of the application.

Entity-Relationship Diagram

The following diagram illustrates the primary relationships between the core tables in the QashierWise database.

Table Schemas

This section provides a detailed breakdown of each major table, including its columns, types, and purpose.

users

This table stores the primary user account information for individuals who sign up for QashierWise. It follows the standard Laravel user structure.

Column	Type	Constraints	Description
id	bigint	Primary Key, Unsigned, Auto-increment	Unique identifier for the user.
name	string		The user's full name.
email	string	Unique	The user's email address, used for login.
email_verified_at	timestamp	Nullable	Timestamp when the user verified their email.
password	string		The user's hashed password.
remember_token	string(100)	Nullable	Token for "remember me" functionality.
created_at	timestamp	Nullable	Timestamp of user creation.
updated_at	timestamp	Nullable	Timestamp of the last user update.

Sources: database/migrations/0001_01_01_000000_create_users_table.php:15-25

whatsapp_accounts

This table stores the credentials and metadata for a user's connected WhatsApp Business Account (WABA). A user has a one-to-one relationship with a WhatsApp account. The application logic enforces this by updating the existing record for a user rather than creating a new one.

Column	Type	Constraints	Description
id	bigint	Primary Key, Unsigned, Auto-increment	Unique identifier for the WhatsApp account record.
user_id	bigint	Foreign Key (users.id)	Links to the user who owns this account.
phone_number_id	string	Nullable, Index	The unique ID for the phone number provided by Meta.
waba_id	string	Nullable, Index	The WhatsApp Business Account ID.
access_token	text	Nullable	The access token for authenticating with the Meta Graph API.
name	string	Nullable	The verified name of the business.
display_phone_number	string	Nullable	The formatted phone number for display.
quality_rating	string	Nullable	The quality rating of the phone number (e.g., GREEN).
connection_method	string	Default: 'embedded_signup'	The method used to connect the account.
is_active	boolean	Default: false	Flag indicating if the account is active and usable.
coexistence_enabled	boolean	Default: false	Flag for WhatsApp's coexistence mode.
created_at	timestamp	Nullable	Timestamp of record creation.
updated_at	timestamp	Nullable	Timestamp of the last record update.

Sources: database/migrations/2025_11_15_042414_create_whatsapp_accounts_table.php:15-30, tests/Unit/Services/EmbeddedSignupServicePropertyTest.php:101-108

whatsapp_templates

This table holds the WhatsApp message templates created by users. Each template is associated with a specific WhatsApp account and has a unique combination of name and language.

Column	Type	Constraints	Description
id	bigint	Primary Key, Unsigned, Auto-increment	Unique identifier for the template.
whatsapp_account_id	bigint	Foreign Key (whatsapp_accounts.id)	Links to the WhatsApp account this template belongs to.
template_id	string	Nullable	The ID of the template provided by Meta upon approval.
name	string	Index	The unique name of the template (e.g., order_confirmation).
language	string	Default: 'en'	The language code of the template (e.g., en, id).
category	enum	Default: 'UTILITY'	The template category ('AUTHENTICATION', 'MARKETING', 'UTILITY').
status	enum	Default: 'PENDING'	The approval status of the template ('APPROVED', 'PENDING', 'REJECTED', 'DISABLED').
components	json	Nullable	A JSON object containing the template's structural components (header, body, footer, buttons).
body	text	Nullable	A denormalized copy of the body text for easy access.
header	text	Nullable	A denormalized copy of the header text.
footer	text	Nullable	A denormalized copy of the footer text.
buttons	json	Nullable	A denormalized JSON array of the button configurations.
usage_count	integer	Default: 0	A counter for how many times the template has been used.
approved_at	timestamp	Nullable	Timestamp when the template was approved by Meta.
created_at	timestamp	Nullable	Timestamp of record creation.
updated_at	timestamp	Nullable	Timestamp of the last record update.

A unique constraint exists on (whatsapp_account_id, name, language) to prevent duplicate templates.
Sources: database/migrations/2025_11_15_042422_create_whatsapp_templates_table.php:15-31

subscriptions

This table tracks user subscriptions, which are managed via the Polar.sh service. It links a user to their subscription plan and status.

Column	Type	Constraints	Description
id	bigint	Primary Key, Unsigned, Auto-increment	Unique identifier for the subscription record.
user_id	bigint	Foreign Key (users.id), Unique	The user associated with the subscription.
plan_name	string		The name of the subscription plan (e.g., 'standard', 'pro').
polar_subscription_id	string	Unique	The subscription ID from the Polar.sh service.
status	string		The status of the subscription (e.g., 'active', 'trialing').
trial_ends_at	timestamp	Nullable	Timestamp when the trial period ends.
period_ends_at	timestamp	Nullable	Timestamp when the current billing period ends.
cancelled_at	timestamp	Nullable	Timestamp when the subscription was cancelled.
created_at	timestamp	Nullable	Timestamp of record creation.
updated_at	timestamp	Nullable	Timestamp of the last record update.

Sources: database/migrations/2025_12_06_000001_create_subscriptions_table.php:15-26

products

This table stores information about products for the Point of Sale (POS) system.

Column	Type	Constraints	Description
id	bigint	Primary Key, Unsigned, Auto-increment	Unique identifier for the product.
store_id	bigint	Foreign Key (stores.id)	Links to the store this product belongs to.
category_id	bigint	Foreign Key (categories.id), Nullable	Links to the product's category.
name	string		The name of the product.
slug	string	Unique	A URL-friendly version of the product name.
description	text	Nullable	A description of the product.
price	decimal(15, 2)		The price of the product.
stock	integer	Default: 0	The current stock quantity.
is_active	boolean	Default: true	Flag to enable or disable the product.
created_at	timestamp	Nullable	Timestamp of record creation.
updated_at	timestamp	Nullable	Timestamp of the last record update.

Sources: database/migrations/2025_12_04_000006_create_products_table.php:15-27

orders

This table records orders made through the POS system. It uses a JSON column items to store the products included in the order, which is a denormalized approach to avoid a many-to-many pivot table.

Column	Type	Constraints	Description
id	bigint	Primary Key, Unsigned, Auto-increment	Unique identifier for the order.
store_id	bigint	Foreign Key (stores.id)	The store where the order was placed.
table_id	bigint	Foreign Key (tables.id), Nullable	The table associated with the order (for dine-in).
customer_name	string	Nullable	The name of the customer.
status	string	Default: 'pending'	The current status of the order (e.g., 'pending', 'completed').
total	decimal(15, 2)		The total amount of the order.
payment_method	string	Nullable	The method of payment (e.g., 'cash', 'qris').
payment_status	string	Default: 'unpaid'	The payment status of the order.
items	json		A JSON array of objects, each representing a product in the order.
notes	text	Nullable	Any special notes for the order.
created_at	timestamp	Nullable	Timestamp of record creation.
updated_at	timestamp	Nullable	Timestamp of the last record update.

Sources: database/migrations/2025_12_04_000007_create_orders_table.php:15-29