Database Schema

This appendix documents the database schema for the Conference Expo Portal v2.0. The schema is designed to support all core features while maintaining data integrity and performance.

Common Fields

All entities include these base fields:

Field	Type	Description
id	uuid	Primary key (UUID v7)
created_at	timestamp	Record creation timestamp
updated_at	timestamp	Last update timestamp
deleted_at	timestamp	Soft delete timestamp (nullable)

Core Entities

Event

Represents a conference or exhibition event.

Field	Type	Description	Constraints
name	string	Event name	Required, Unique per organiser
description	text	Event description	Required
organiser_id	uuid	Foreign key to Organiser	Required, Foreign Key
venue_id	uuid	Foreign key to Venue	Required, Foreign Key
status	enum	Event status	draft, published, archived
timezone	string	Event timezone	Required, IANA timezone
started_at	timestamp	Event start date/time	Required
ended_at	timestamp	Event end date/time	Required, After started_at
cut_off_at	timestamp	Stand configuration deadline	Required, Before started_at
artwork_required_at	timestamp	Artwork submission deadline	Required, Before started_at

Relationships:

• belongs_to: Organiser
• belongs_to: Venue
• has_many: Stands

User

Represents system users (admins, organisers, sponsors).

Field	Type	Description	Constraints
first_name	string	User's first name	Required
last_name	string	User's last name	Required
email	string	Email address	Required, Unique, Valid email
phone_number	string	Contact phone number	Optional, Valid phone format
password	string	Hashed password	Required, Secure hash
is_admin	boolean	Admin status flag	Default: false
status	enum	User status	active, inactive, suspended
last_login_at	timestamp	Last login timestamp	Nullable

Relationships:

• has_many: UserPermissions
• has_many: Notifications

Stand

Represents a physical stand at an event.

Field	Type	Description	Constraints
number	string	Stand reference number	Required, Unique per event
event_id	uuid	Foreign key to Event	Required, Foreign Key
sponsor_id	uuid	Foreign key to Sponsor	Required, Foreign Key
status	enum	Stand status	assigned, configured, approved, completed
notes	text	Additional information	Optional

Relationships:

• belongs_to: Event
• belongs_to: Sponsor
• has_many: StandConfigurations
• has_many: Artworks

Package

Represents a stand package configuration provided by external system.

Field	Type	Description	Constraints
name	string	Package name	Required
description	text	Package description	Required
kit_id	string	External system kit ID	Required
kit_code	string	External system kit code	Required
base_price	decimal	Package base price	Required, >= 0
unit_cost	decimal	Package cost (internal)	Required, >= 0
status	enum	Package status	active, inactive, archived
version	integer	Package version	Required, Auto-increment
effective_date	timestamp	Version effective date	Required

Relationships:

• has_many: StandConfigurations
• has_many: ConfigurationCategories
• has_many: PackageVersions

Package Version

Tracks historical versions of package data.

Field	Type	Description	Constraints
package_id	uuid	Foreign key to Package	Required, Foreign Key
version_number	integer	Version number	Required
effective_date	timestamp	When version became active	Required
data_snapshot	jsonb	Complete package data	Required

Relationships:

• belongs_to: Package

Stand Configuration

Represents a sponsor's configuration for a specific stand.

Field	Type	Description	Constraints
stand_id	uuid	Foreign key to Stand	Required, Foreign Key
package_id	uuid	Foreign key to Package	Required, Foreign Key
package_version	integer	Package version used	Required
package_selection_permission	boolean	Can sponsor select package	Default: true
allocated_by	uuid	Foreign key to User (if pre-allocated)	Nullable, Foreign Key
allocation_timestamp	timestamp	When package was allocated	Nullable
status	enum	Configuration status	draft, submitted, approved, changes_requested
submission_timestamp	timestamp	When submitted for review	Nullable
approval_timestamp	timestamp	When approved	Nullable
approved_by	uuid	Foreign key to User (approver)	Nullable, Foreign Key
total_price	decimal	Total configuration price	Required, >= 0

Relationships:

• belongs_to: Stand
• belongs_to: Package
• belongs_to: User (allocator)
• belongs_to: User (approver)
• has_many: StandConfigurationSelections
• has_many: Artworks

Configuration Category

Categories for grouping configuration options, provided by external system.

Field	Type	Description	Constraints
package_id	uuid	Foreign key to Package	Required, Foreign Key
name	string	Category name	Required
description	text	Category description	Optional
external_group_id	string	External system group ID	Optional
position	integer	Display order	Required
selection_mode	enum	Selection mode	single, multiple

Relationships:

• belongs_to: Package
• has_many: ConfigurationOptions

Configuration Option

Individual items/options that can be configured for a stand, provided by external system.

Field	Type	Description	Constraints
package_id	uuid	Foreign key to Package	Required, Foreign Key
category_id	uuid	Foreign key to Category	Nullable, Foreign Key
name	string	Option name	Required
description	text	Option description	Optional
bolt_on_id	string	External system bolt-on ID	Optional
item_code	string	External system item code	Optional
artwork_code	string	Artwork specification code	Optional
min_quantity	integer	Minimum quantity	Required, >= 0
included_quantity	integer	Quantity included in package	Required, >= min_quantity
max_quantity	integer	Maximum quantity	Required, >= included_quantity
unit_price	decimal	Price per unit (customer-facing)	Required, >= 0
unit_cost	decimal	Cost per unit (internal)	Required, >= 0
size_modifiers	jsonb	External system metadata	Optional
position	integer	Display order	Required
is_default_selected	boolean	Default selection indicator	Default: false

Relationships:

• belongs_to: Package
• belongs_to: ConfigurationCategory
• has_many: ConfigurationSelections

Configuration Selection

Records sponsor's selected options and quantities for a configuration.

Field	Type	Description	Constraints
stand_configuration_id	uuid	Foreign key to Configuration	Required, Foreign Key
option_id	uuid	Foreign key to Option	Required, Foreign Key
quantity	integer	Selected quantity	Required, >= 0
unit_price_at_selection	decimal	Unit price when selected	Required, >= 0
additional_charge	decimal	Charge above included qty	Required, >= 0
selected_at	timestamp	Selection timestamp	Required

Relationships:

• belongs_to: StandConfiguration
• belongs_to: ConfigurationOption

Artwork

Artwork required for stand configurations.

Field	Type	Description	Constraints
stand_configuration_id	uuid	Foreign key to Configuration	Required, Foreign Key
specification_file_id	uuid	Artwork specification file	Required, Foreign Key
artwork_file_id	uuid	Uploaded artwork file	Required, Foreign Key
status	enum	Artwork status	pending, approved, rejected
approved_at	timestamp	Approval timestamp	Nullable
approved_by	uuid	Foreign key to User	Nullable, Foreign Key
rejection_reason	text	Rejection reason	Nullable

Relationships:

• belongs_to: StandConfiguration
• belongs_to: User (approver)

User Permission

Manages user access to different entities.

Field	Type	Description	Constraints
user_id	uuid	Foreign key to User	Required, Foreign Key
entity_type	enum	Entity type	organiser, sponsor, stand
entity_id	uuid	Entity ID	Required
scope	enum	Permission scope	read, write, admin
role	enum	User role	primary_contact, contact, viewer
granted_at	timestamp	Grant timestamp	Required
granted_by	uuid	Foreign key to User	Required, Foreign Key

Relationships:

• belongs_to: User (permission holder)
• belongs_to: User (granter)

Indexes

Performance Indexes

• events(organiser_id, status)
• stands(event_id, status)
• packages(kit_id, status)
• packages(status, effective_date)
• stand_configurations(stand_id, status)
• stand_configurations(package_id, package_version)
• configuration_options(package_id, category_id)
• configuration_selections(stand_configuration_id, option_id)
• artworks(stand_configuration_id, status)
• user_permissions(user_id, entity_type, entity_id)

Unique Constraints

• events(organiser_id, name)
• stands(event_id, number)
• users(email)
• package_versions(package_id, version_number)

Data Integrity

Foreign Key Constraints

• All foreign keys are indexed
• Cascading deletes are disabled
• Soft deletes are used where appropriate

Validation Rules

• Email addresses must be valid
• Phone numbers must be in E.164 format
• Timestamps must be in UTC
• Prices must be non-negative
• Quantities must be within min/max bounds

Data Retention

Archiving Rules

• Events are archived after completion
• Stand configurations are retained for 7 years
• Artwork files are retained for 7 years
• User data is retained while active
• Audit logs are retained for 7 years

For detailed technical specifications, see the Architecture. For database schema details, refer to the Database Schema.