Auction Excellence - Parking Lot Management & Quality Inspection

Overview

Auction Excellence uses PostgreSQL with 35 tables organized into logical domains. All data access is controlled by Row Level Security (RLS) policies that enforce multi-tenant isolation.

35 Tables

Core data, chat, reporting, and audit tables

8 Views

Pre-built queries for common operations

35+ Functions

RPC functions for complex operations

Multi-Tenant Architecture

All core tables include an auction_id column that enables tenant isolation. RLS policies automatically filter data based on the authenticated user’s auction memberships.

-- Example: Locations are scoped to auctions
CREATE TABLE locations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  auction_id UUID NOT NULL REFERENCES auctions(id),
  name TEXT NOT NULL,
  -- ... other columns
);

-- RLS policy ensures users only see their auction's locations
CREATE POLICY "Users can view their auction's locations"
  ON locations FOR SELECT
  USING (is_auction_member(auction_id));

Authorization Hierarchy

The system uses a hierarchical role structure for access control:

Role	Scope	Capabilities
Super Admin	System-wide	Access all auctions, create new auctions, manage all users
Owner	Per-auction	Full auction control, transfer ownership, delete auction
Admin	Per-auction	Manage members, locations, and most settings
Team Member	Per-auction	Submit data for assigned locations, participate in chat

Table Groups

User & Authentication Tables

users

Extends Supabase Auth with application-specific profile data.

Column	Type	Description
`id`	UUID	Primary key (matches auth.users.id)
`email`	TEXT	User’s email address
`full_name`	TEXT	Display name
`avatar_url`	TEXT	URL to profile photo in `avatars` storage bucket (nullable)
`is_active`	BOOLEAN	Account status
`last_auction_id`	UUID	Last accessed auction
`created_at`	TIMESTAMPTZ	Account creation time
`updated_at`	TIMESTAMPTZ	Last profile update

Indexes:

idx_users_is_active on (is_active)
idx_users_last_auction_id on (last_auction_id)

RLS Policies:

Operation	Rule
SELECT	User can view self, admins can view all
INSERT	Admins only
UPDATE	User can update self, admins can update all
DELETE	Admins only

super_admins

System-wide administrators with elevated access.

Column	Type	Description
`id`	UUID	Primary key
`user_id`	UUID	Reference to users
`created_at`	TIMESTAMPTZ	When granted
`created_by`	UUID	Who granted access

Indexes:

idx_super_admins_user_id on (user_id)

RLS Policies: Managed via service role only (not directly accessible via RLS)

push_tokens

Device tokens for push notifications.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`user_id`	UUID	Device owner
`token`	TEXT	Push notification token
`platform`	platform_type	ios or android
`created_at`	TIMESTAMPTZ	Registration timestamp
`updated_at`	TIMESTAMPTZ	Last update

Indexes:

idx_push_tokens_token on (token)
idx_push_tokens_auction_id on (auction_id)

RLS Policies:

Operation	Rule
SELECT	User can view own tokens, admins can view all
INSERT	User can insert own tokens
UPDATE	User can update own tokens
DELETE	User can delete own, admins can delete all

Auction (Organization) Tables

auctions

Multi-tenant workspace/organization entity.

Column	Type	Description
`id`	UUID	Primary key
`name`	TEXT	Organization name
`slug`	TEXT	URL-friendly identifier
`is_active`	BOOLEAN	Account status
`payment_status`	TEXT	Subscription payment status
`subscription_tier`	TEXT	Plan level
`settings`	JSONB	Custom auction settings
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_auctions_slug on (slug) - for URL lookups
idx_auctions_is_active on (is_active)

RLS Policies:

Operation	Rule
SELECT	Super admins or members of the auction
INSERT	Super admins only
UPDATE	Auction admins or above
DELETE	Auction owners only

auction_members

User membership within auctions with role assignment.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`user_id`	UUID	Member user
`role`	auction_role	owner, admin, or team_member
`invited_by`	UUID	Who invited this member
`joined_at`	TIMESTAMPTZ	When they joined
`created_at`	TIMESTAMPTZ	Record creation

Indexes:

idx_auction_members_auction_id on (auction_id)
idx_auction_members_user_id on (user_id)
idx_auction_members_role on (role)

RLS Policies:

Operation	Rule
SELECT	Super admins or auction members
INSERT	Auction admins or above
UPDATE	Super admins can update any; owners can update non-owners; admins can update team members
DELETE	Super admins, owners (non-owner members), or admins (team members only)

auction_invites

Invitation tokens for onboarding new members.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Target auction
`email`	TEXT	Invitee’s email
`token`	TEXT	Unique invitation token
`role`	auction_role	Assigned role
`invited_by`	UUID	Inviter user
`expires_at`	TIMESTAMPTZ	Token expiration
`accepted_at`	TIMESTAMPTZ	When accepted (null if pending)
`created_at`	TIMESTAMPTZ	Invite creation

Indexes:

idx_auction_invites_auction_id on (auction_id)
idx_auction_invites_email on (email)
idx_auction_invites_token on (token) - for token lookups
idx_auction_invites_expires_at on (expires_at)

RLS Policies:

Operation	Rule
SELECT	Auction admins or above
INSERT	Auction admins or above
DELETE	Auction admins or above

auction_branding

Custom branding configuration per auction.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`logo_url`	TEXT	Custom logo URL
`primary_color`	TEXT	Hex color code
`secondary_color`	TEXT	Hex color code
`accent_color`	TEXT	Hex color code
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_auction_branding_auction_id on (auction_id)

audit_logs

Comprehensive audit trail for compliance.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Auction context
`user_id`	UUID	Acting user
`entity_type`	TEXT	Affected entity type
`entity_id`	UUID	Affected entity ID
`action`	audit_action	Action performed
`metadata`	JSONB	Additional context
`ip_address`	INET	Client IP
`user_agent`	TEXT	Client user agent
`created_at`	TIMESTAMPTZ	When action occurred

Indexes:

idx_audit_logs_auction_id on (auction_id)
idx_audit_logs_user_id on (user_id)
idx_audit_logs_action on (action)
idx_audit_logs_created_at on (created_at DESC) - for recent logs
idx_audit_logs_entity on (entity_type, entity_id) - composite for entity lookups

RLS Policies:

Operation	Rule
SELECT	Super admins or auction owners only
INSERT/UPDATE/DELETE	System only (via service role)

Location Management Tables

locations

Parking lot/facility definitions.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`name`	TEXT	Location display name
`address`	TEXT	Physical address
`operating_hours_start`	TIME	Daily start time
`operating_hours_end`	TIME	Daily end time
`submission_frequency_minutes`	INTEGER	Required submission interval
`is_active`	BOOLEAN	Location status
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_locations_auction_id on (auction_id)

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction admins or above
UPDATE	Auction admins or above
DELETE	Auction admins or above

user_location_assignments

Maps team members to their assigned locations.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`user_id`	UUID	Assigned user
`location_id`	UUID	Assigned location
`created_at`	TIMESTAMPTZ	Assignment timestamp

Indexes:

idx_user_location_assignments_user on (user_id)
idx_user_location_assignments_location on (location_id)
idx_user_location_assignments_auction_id on (auction_id)

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction admins or above
UPDATE	Auction admins or above
DELETE	Auction admins or above

Overwatch Feature Tables

lot_submissions

Parking lot occupancy submissions.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`location_id`	UUID	Submission location
`user_id`	UUID	Submitting user
`image_url`	TEXT	Submission photo URL
`submitted_at`	TIMESTAMPTZ	When submitted
`created_at`	TIMESTAMPTZ	Record creation

Indexes:

idx_lot_submissions_location on (location_id)
idx_lot_submissions_user on (user_id)
idx_lot_submissions_submitted_at on (submitted_at)
idx_lot_submissions_auction_id on (auction_id)
idx_lot_submissions_type on (submission_type_id)
idx_lot_submissions_location_type on (location_id, submission_type_id) - composite

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Admins or users assigned to the location
UPDATE	Auction admins or above
DELETE	Auction admins or above

lot_submission_entries

Individual entries within a submission (multi-type support).

Column	Type	Description
`id`	UUID	Primary key
`lot_submission_id`	UUID	Parent submission
`submission_type_id`	UUID	Type category
`car_count`	INTEGER	Number of vehicles
`image_url`	TEXT	Entry-specific photo
`created_at`	TIMESTAMPTZ	Record creation

Indexes:

idx_lot_submission_entries_submission on (lot_submission_id)
idx_lot_submission_entries_type on (submission_type_id)

RLS Policies: Inherits from parent lot_submissions table

submission_types

Catalog of submission categories.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Null for system types
`name`	TEXT	Type display name
`display_order`	INTEGER	Sort order
`is_system`	BOOLEAN	System-defined vs custom
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_submission_types_auction on (auction_id)
idx_submission_types_system on (is_system)

RLS Policies:

Operation	Rule
SELECT	System types visible to all; custom types to auction members
INSERT	Auction admins can create custom types only
UPDATE	Auction admins can update custom types only
DELETE	Auction admins can delete custom types only

location_submission_types

Per-location type configuration with ordering.

Column	Type	Description
`id`	UUID	Primary key
`location_id`	UUID	Target location
`submission_type_id`	UUID	Submission type
`display_order`	INTEGER	Location-specific order
`is_enabled`	BOOLEAN	Whether type is active
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_location_submission_types_location on (location_id)
idx_location_submission_types_type on (submission_type_id)
idx_location_submission_types_enabled on (location_id, is_enabled) - composite for active types

RLS Policies:

Operation	Rule
SELECT	User can access location’s auction
INSERT	Location’s auction admin or above
UPDATE	Location’s auction admin or above
DELETE	Location’s auction admin or above

Quality Inspection Tables

quality_inspections

Vehicle inspection records.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`location_id`	UUID	Inspection location
`user_id`	UUID	Inspector
`vin_number`	TEXT	Vehicle VIN
`barcode`	TEXT	Barcode/Stock number
`submitted_at`	TIMESTAMPTZ	Inspection time
`created_at`	TIMESTAMPTZ	Record creation

Indexes:

idx_quality_inspections_location on (location_id)
idx_quality_inspections_user on (user_id)
idx_quality_inspections_submitted_at on (submitted_at)
idx_quality_inspections_vin on (vin_number) - for VIN lookups
idx_quality_inspections_auction_id on (auction_id)

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Admins or users assigned to the location
UPDATE	Auction admins or above
DELETE	Auction admins or above

defect_categories

Predefined defect types (20 standard categories).

Column	Type	Description
`id`	UUID	Primary key
`name`	TEXT	Category name
`display_order`	INTEGER	Sort order
`created_at`	TIMESTAMPTZ	Creation timestamp

RLS Policies:

Operation	Rule
SELECT	All authenticated users
INSERT/UPDATE/DELETE	Admins only

quality_defects

Defects found during inspections.

Column	Type	Description
`id`	UUID	Primary key
`inspection_id`	UUID	Parent inspection
`category_id`	UUID	Defect category
`notes`	TEXT	Additional details
`created_at`	TIMESTAMPTZ	Record creation

RLS Policies:

Operation	Rule
SELECT	User can view parent inspection
INSERT	User owns parent inspection
UPDATE	User owns parent inspection or is admin
DELETE	Admins only

Problem Reports (A3)

problem_reports

A3-style problem reports.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`prepared_by`	UUID	Report creator
`assigned_to`	UUID	Responsible person
`issue_name`	TEXT	Problem title
`area`	TEXT	Problem area
`location_id`	UUID	Related location
`status`	problem_status	open, in_progress, closed
`submitted_at`	TIMESTAMPTZ	Submission time
`created_at`	TIMESTAMPTZ	Record creation
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_problem_reports_status on (status)
idx_problem_reports_location on (location_id)
idx_problem_reports_auction_id on (auction_id)

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction members can create
UPDATE	Report creator or auction admins
DELETE	Auction admins or above

problem_facts

5W2H analysis for problems.

Column	Type	Description
`id`	UUID	Primary key
`problem_id`	UUID	Parent problem
`who`	TEXT	Who is affected
`what`	TEXT	What is the issue
`where_field`	TEXT	Where it occurs
`when_field`	TEXT	When it happens
`how_many`	TEXT	Scale of impact
`how_much`	TEXT	Cost impact
`created_at`	TIMESTAMPTZ	Record creation

RLS Policies: Access controlled by parent problem_reports

potential_causes

Root cause analysis (2-5 per problem).

Column	Type	Description
`id`	UUID	Primary key
`problem_id`	UUID	Parent problem
`cause_text`	TEXT	Cause description
`order_number`	INTEGER	Cause priority
`created_at`	TIMESTAMPTZ	Record creation

RLS Policies: Access controlled by parent problem_reports

problem_actions

Action items with tracking.

Column	Type	Description
`id`	UUID	Primary key
`problem_id`	UUID	Parent problem
`issue`	TEXT	Issue description
`countermeasure`	TEXT	Proposed solution
`responsible_person`	TEXT	Who is responsible
`completion_date`	DATE	Target date
`status`	action_status	pending or completed
`created_at`	TIMESTAMPTZ	Record creation
`updated_at`	TIMESTAMPTZ	Last modification

RLS Policies:

Operation	Rule
SELECT/INSERT	Access controlled by parent problem
UPDATE	Responsible person, report creator, or admins
DELETE	Auction admins or above

problem_kpi_impacts

KPI impact tracking.

Column	Type	Description
`id`	UUID	Primary key
`problem_id`	UUID	Parent problem
`kpi_type`	kpi_type	safety, quality, delivery, cost, engagement
`created_at`	TIMESTAMPTZ	Record creation

RLS Policies: Access controlled by parent problem_reports

Chat System Tables

channels

Chat channels within auctions.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`name`	TEXT	Channel name
`description`	TEXT	Channel description
`created_by`	UUID	Channel creator
`location_id`	UUID	Optional location scope
`is_default`	BOOLEAN	Auto-join for new members
`is_archived`	BOOLEAN	Archive status
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_channels_auction_id on (auction_id)
idx_channels_location_id on (location_id)
idx_channels_created_by on (created_by)

RLS Policies:

Operation	Rule
SELECT	Auction members or super admins
INSERT	Auction admins or super admins
UPDATE	Channel creator, auction admins, or super admins
DELETE	Auction admins or super admins

channel_members

User membership in channels.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`channel_id`	UUID	Parent channel
`user_id`	UUID	Member user
`role`	channel_member_role	owner or member
`notification_preference`	TEXT	all, mentions, or none
`last_read_at`	TIMESTAMPTZ	Last read timestamp
`joined_at`	TIMESTAMPTZ	When joined

Indexes:

idx_channel_members_channel_id on (channel_id)
idx_channel_members_user_id on (user_id)
idx_channel_members_auction_id on (auction_id)

RLS Policies:

Operation	Rule
SELECT	Self, channel members, auction admins, or super admins
INSERT	Auction admins or super admins
UPDATE	Self, auction admins, or super admins
DELETE	Self (leave), auction admins, or super admins

messages

Chat messages with threading support.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`channel_id`	UUID	Parent channel
`user_id`	UUID	Message author
`content`	TEXT	Message content
`message_type`	message_type	text, image, or system
`image_url`	TEXT	Optional image URL
`parent_message_id`	UUID	Thread parent (null for top-level)
`is_edited`	BOOLEAN	Edit status
`is_deleted`	BOOLEAN	Soft delete flag
`metadata`	JSONB	Additional data
`created_at`	TIMESTAMPTZ	Send timestamp
`updated_at`	TIMESTAMPTZ	Last edit timestamp

Indexes:

idx_messages_channel_id on (channel_id)
idx_messages_auction_id on (auction_id)
idx_messages_user_id on (user_id)
idx_messages_parent_message_id on (parent_message_id)
idx_messages_created_at on (created_at DESC)
idx_messages_channel_created on (channel_id, created_at DESC) - composite for pagination
idx_messages_content_search - GIN index on to_tsvector('english', content) for full-text search

RLS Policies:

Operation	Rule
SELECT	Channel members, auction admins, or super admins
INSERT	Author is channel member
UPDATE	Author only (for edits)
DELETE	Author, auction admins, or super admins

message_reactions

Emoji reactions on messages.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`message_id`	UUID	Parent message
`user_id`	UUID	Reacting user
`emoji`	TEXT	Emoji character
`created_at`	TIMESTAMPTZ	Reaction timestamp

Indexes:

idx_message_reactions_message_id on (message_id)
idx_message_reactions_user_id on (user_id)
idx_message_reactions_auction_id on (auction_id)

RLS Policies:

Operation	Rule
SELECT	Channel members or super admins
INSERT	Channel members only
DELETE	Author only (remove own reaction)

Reporting & Analytics Tables

report_datasets

Imported CSV file metadata.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`name`	TEXT	Dataset name
`description`	TEXT	Dataset description
`source_filename`	TEXT	Original filename
`row_count`	INTEGER	Number of rows
`column_schema`	JSONB	Column definitions
`imported_by`	UUID	Importing user
`imported_at`	TIMESTAMPTZ	Import timestamp
`last_updated_at`	TIMESTAMPTZ	Last data update
`status`	TEXT	active, archived, deleted
`created_at`	TIMESTAMPTZ	Record creation

Indexes:

idx_report_datasets_auction on (auction_id)
idx_report_datasets_status on (status) WHERE status = ‘active’ - partial index
idx_report_datasets_imported_by on (imported_by)

RLS Policies: Auction members can access their auction’s datasets

report_data_rows

Imported CSV data rows.

Column	Type	Description
`id`	UUID	Primary key
`dataset_id`	UUID	Parent dataset
`row_data`	JSONB	Row data as JSON
`created_at`	TIMESTAMPTZ	Import timestamp

Indexes:

idx_report_data_rows_dataset on (dataset_id)
idx_report_data_rows_data - GIN index on (row_data) for JSONB queries

RLS Policies: Access controlled by parent dataset

report_definitions

Saved report configurations.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`dataset_id`	UUID	Source dataset
`name`	TEXT	Report name
`description`	TEXT	Report description
`natural_language_query`	TEXT	User’s query
`generated_sql`	TEXT	AI-generated SQL
`visualization_config`	JSONB	Chart configuration
`created_by`	UUID	Report creator
`updated_by`	UUID	Last modifier
`is_template`	BOOLEAN	Template flag
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_report_definitions_auction on (auction_id)
idx_report_definitions_dataset on (dataset_id)
idx_report_definitions_created_by on (created_by)
idx_report_definitions_is_template on (is_template) WHERE is_template = TRUE - partial index

RLS Policies: Creator, shared users, or auction members for shared reports

report_shares

Report sharing permissions.

Column	Type	Description
`id`	UUID	Primary key
`report_id`	UUID	Parent report
`share_type`	TEXT	user, auction, public
`shared_with_user_id`	UUID	Target user (if user share)
`public_token`	TEXT	Public access token
`expires_at`	TIMESTAMPTZ	Share expiration
`created_by`	UUID	Sharing user
`created_at`	TIMESTAMPTZ	Share timestamp

Indexes:

idx_report_shares_report on (report_id)
idx_report_shares_token on (public_token) WHERE public_token IS NOT NULL
idx_report_shares_user on (shared_with_user_id) WHERE shared_with_user_id IS NOT NULL
idx_report_shares_type on (share_type)

RLS Policies: Report owner or shared users

report_query_logs

Audit log for AI queries.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Parent auction
`report_id`	UUID	Related report
`user_id`	UUID	Querying user
`natural_language_query`	TEXT	User’s question
`generated_sql`	TEXT	Generated SQL
`execution_time_ms`	INTEGER	Query duration
`row_count`	INTEGER	Results count
`llm_model`	TEXT	AI model used
`llm_tokens_used`	INTEGER	Token consumption
`error_message`	TEXT	Error if failed
`created_at`	TIMESTAMPTZ	Query timestamp

Indexes:

idx_report_query_logs_user on (user_id)
idx_report_query_logs_auction on (auction_id)
idx_report_query_logs_report on (report_id) WHERE report_id IS NOT NULL
idx_report_query_logs_created_at on (created_at DESC)
Rate limiting indexes for query throttling

RLS Policies: Users can view their own query logs; admins can view all

Staffing Data Tables

The staffing data system supports importing, storing, and analyzing CR writer productivity data from external auction management systems.

Duplicate Handling: When importing staffing data, duplicates are identified using composite keys and can be skipped, updated, or cause the import to fail based on user preference.

staffing_dataset_types

Catalog of supported import schema types (system data, read-only).

Column	Type	Description
`id`	UUID	Primary key
`name`	TEXT	Type identifier (“checkin_model” or “cr_data”)
`display_name`	TEXT	Human-readable name
`description`	TEXT	What data this type contains
`schema`	JSONB	Column definitions with types and aliases
`column_count`	INTEGER	Number of columns in schema
`is_system`	BOOLEAN	Always true (seeded data)
`created_at`	TIMESTAMPTZ	Creation timestamp

RLS Policies: Read-only for all authenticated users

staffing_datasets

Metadata for imported staffing datasets.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Multi-tenant isolation
`dataset_type_id`	UUID	References staffing_dataset_types
`name`	TEXT	Dataset name (e.g., “January 2024 Check-Ins”)
`description`	TEXT	Optional notes
`row_count`	INTEGER	Number of imported rows
`date_range_start`	DATE	Earliest date in dataset
`date_range_end`	DATE	Latest date in dataset
`created_by`	UUID	User who imported
`created_at`	TIMESTAMPTZ	Import timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Indexes:

idx_staffing_datasets_auction_id on (auction_id)
idx_staffing_datasets_type on (dataset_type_id)

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction admins only
UPDATE	Auction admins only
DELETE	Auction admins only

staffing_checkin_data

Vehicle check-in records imported from CheckinModel exports.

Column	Type	Description
`id`	UUID	Primary key
`dataset_id`	UUID	Parent dataset reference
`auction_id`	UUID	Multi-tenant isolation
`vin`	TEXT	Vehicle Identification Number
`check_in_date`	TIMESTAMPTZ	Date/time vehicle arrived (Required)
`check_in_user`	TEXT	User who checked in vehicle
`facilitation_location`	TEXT	Auction location name
`sale_type`	TEXT	”Lease” or “Consignment”
`stock_number`	TEXT	Internal stock identifier
`vehicle_year`	INTEGER	Vehicle year
`make`	TEXT	Vehicle make
`model`	TEXT	Vehicle model
`grade`	DECIMAL	Vehicle condition grade
`grade2`	DECIMAL	Secondary grade
`photo_count`	INTEGER	Number of photos taken
`primary_inspector`	TEXT	Assigned inspector name
`inspection_id`	TEXT	External system inspection ID
`day_of_week`	INTEGER	Calculated (0=Sunday)
`hour_of_day`	INTEGER	Calculated (0-23)
`week_number`	INTEGER	Calculated ISO week

Unique Constraint: (dataset_id, vin, check_in_date) - Prevents duplicate imports

Duplicate Detection: A record is considered a duplicate if the same VIN checks in on the same date within the same dataset. This prevents re-importing the same data from multiple exports.

Indexes:

idx_staffing_checkin_data_dataset on (dataset_id)
idx_staffing_checkin_data_auction on (auction_id)
idx_staffing_checkin_data_vin on (vin)
idx_staffing_checkin_data_date on (check_in_date)

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction admins only
DELETE	Auction admins only

staffing_cr_data

Condition Report (CR) records imported from CR_Data exports.

Column	Type	Description
`id`	UUID	Primary key
`dataset_id`	UUID	Parent dataset reference
`auction_id`	UUID	Multi-tenant isolation
`vin`	TEXT	Vehicle Identification Number
`inspection_id`	TEXT	External system inspection ID
`created_date`	TIMESTAMPTZ	When CR was created
`start_date`	TIMESTAMPTZ	When CR writing started
`stop_date`	TIMESTAMPTZ	When CR was completed
`edit_time`	TEXT	Human-readable duration (e.g., “7m 14s”)
`edit_time_seconds`	INTEGER	Parsed duration in seconds
`edit_type`	TEXT	”Completed CR” or “Edited”
`inspector_name`	TEXT	CR writer name
`sale_type`	TEXT	”Lease” or “Consignment”
`status`	TEXT	”Closed”, “Open”, etc.
`completed_cr`	BOOLEAN	TRUE if edit_type is “Completed CR”
`greater_than_5_days`	BOOLEAN	TRUE if CR took >5 days
`not_on_same_day`	BOOLEAN	TRUE if completed different day
`cr_days`	DECIMAL	Days from check-in to CR completion
`day_of_week`	INTEGER	Calculated (0=Sunday)
`hour_of_day`	INTEGER	Calculated (0-23)
`week_number`	INTEGER	Calculated ISO week
`month`	INTEGER	Calculated month (1-12)
`year`	INTEGER	Calculated year

Unique Constraint: (dataset_id, inspection_id, start_date) - Prevents duplicate imports

Duplicate Detection: A record is considered a duplicate if the same inspection ID starts at the same time within the same dataset. This handles scenarios where CR edits create multiple records.

Indexes:

idx_staffing_cr_data_dataset on (dataset_id)
idx_staffing_cr_data_auction on (auction_id)
idx_staffing_cr_data_vin on (vin)
idx_staffing_cr_data_inspection on (inspection_id)
idx_staffing_cr_data_completed on (completed_cr) WHERE completed_cr = TRUE - partial index

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction admins only
DELETE	Auction admins only

staffing_dataset_links

Cross-dataset linking for vehicle journey analysis.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Multi-tenant isolation
`checkin_dataset_id`	UUID	References check-in dataset
`cr_dataset_id`	UUID	References CR data dataset
`name`	TEXT	Optional link name
`description`	TEXT	Optional description
`created_by`	UUID	User who created link
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

Unique Constraint: (checkin_dataset_id, cr_dataset_id) - Prevents duplicate linksRLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction admins only
DELETE	Auction admins only

staffing_activity_settings

Global activity rate settings per auction.

Column	Type	Description
`id`	UUID	Primary key
`auction_id`	UUID	Multi-tenant isolation (unique)
`lease_cr_time_minutes`	INTEGER	Expected lease CR time
`consignment_cr_time_minutes`	INTEGER	Expected consignment CR time
`lease_inop_percent`	DECIMAL	Inoperable vehicle percentage
`lease_certification_percent`	DECIMAL	Certification percentage
`inop_activity_rate`	DECIMAL	Activity rate for inop vehicles
`certification_activity_rate`	DECIMAL	Activity rate for certifications
`standard_lease_activity_rate`	DECIMAL	Standard lease activity rate
`dealer_cr_completion_percent`	DECIMAL	Dealer CR completion target
`created_at`	TIMESTAMPTZ	Creation timestamp
`updated_at`	TIMESTAMPTZ	Last modification

RLS Policies:

Operation	Rule
SELECT	Auction members can view
INSERT	Auction admins only
UPDATE	Auction admins only

Duplicate Handling Strategy

When importing staffing data, duplicates are identified using composite keys:

Dataset Type	Duplicate Key	Rationale
Check-In Data	`VIN + check_in_date`	Same vehicle can’t check in twice on same date
CR Data	`inspection_id + start_date`	Same inspection can’t start twice on same date

Import Behavior Options:

Skip (Default) - Duplicate rows are skipped, originals retained
Update - Existing records updated with new data
Fail - Import aborts if any duplicates found

-- Example: Upsert for staffing_checkin_data with skip behavior
INSERT INTO staffing_checkin_data (dataset_id, vin, check_in_date, ...)
VALUES ($1, $2, $3, ...)
ON CONFLICT (dataset_id, vin, check_in_date)
DO NOTHING;  -- Skip duplicates

-- Example: Upsert with update behavior
INSERT INTO staffing_checkin_data (dataset_id, vin, check_in_date, ...)
VALUES ($1, $2, $3, ...)
ON CONFLICT (dataset_id, vin, check_in_date)
DO UPDATE SET
  check_in_user = EXCLUDED.check_in_user,
  facilitation_location = EXCLUDED.facilitation_location;

Staffing Views

staffing_vehicle_journey

Joins check-in and CR data for end-to-end vehicle tracking.

SELECT * FROM staffing_vehicle_journey
WHERE auction_id = 'your-auction-id'
  AND has_cr = FALSE  -- Find vehicles without CRs
ORDER BY days_waiting DESC;

Columns: checkin_id, check_in_date, vin, stock_number, vehicle details, cr_id, created_date, start_date, stop_date, inspector_name, has_cr, days_to_cr_start, days_to_cr_complete

Staffing Helper Functions

Function	Parameters	Returns	Description
`get_cr_backlog(p_auction_id, p_link_id)`	UUID, UUID	SETOF	Vehicles waiting for CR
`get_cr_completion_rate(p_auction_id, p_link_id)`	UUID, UUID	JSON	CR completion stats by sale type
`get_avg_cr_time_by_sale_type(p_auction_id, p_link_id)`	UUID, UUID	SETOF	Average CR write times
`get_days_to_cr_stats(p_auction_id, p_link_id)`	UUID, UUID	SETOF	Days to CR percentiles
`get_vehicle_journey(p_vin, p_stock_number)`	TEXT, TEXT	JSON	Complete journey for specific vehicle

Custom Enums

PostgreSQL enum types used throughout the schema:

Enum	Values	Used By
`auction_role`	owner, admin, team_member	auction_members
`action_status`	pending, completed	problem_actions
`audit_action`	user_invited, user_joined, role_changed, user_removed, submission_created, inspection_created, problem_created, login	audit_logs
`channel_member_role`	owner, member	channel_members
`kpi_type`	safety, quality, delivery, cost, engagement	problem_kpi_impacts
`message_type`	text, image, system	messages
`platform_type`	ios, android	push_tokens
`problem_status`	open, in_progress, closed	problem_reports

Indexes Summary

The database uses 73 indexes optimized for common query patterns:

Index Types

Type	Count	Purpose
B-tree	65	Standard lookups, sorting, range queries
GIN	2	Full-text search (messages), JSONB queries (report_data_rows)
Composite	6	Multi-column lookups for complex queries
Partial	7	Filtered indexes for common conditions (e.g., `WHERE is_active = true`)

Key Performance Indexes

-- Full-text search on chat messages
CREATE INDEX idx_messages_content_search
ON messages USING GIN (to_tsvector('english', content));

-- Efficient pagination for chat
CREATE INDEX idx_messages_channel_created
ON messages (channel_id, created_at DESC);

-- JSONB queries on report data
CREATE INDEX idx_report_data_rows_data
ON report_data_rows USING GIN (row_data);

-- Active datasets only
CREATE INDEX idx_report_datasets_status
ON report_datasets (status) WHERE status = 'active';

Views

Pre-built queries for common operations:

recent_submissions

Latest lot submissions with aggregated entries.

SELECT * FROM recent_submissions
WHERE auction_id = 'your-auction-id'
ORDER BY submitted_at DESC
LIMIT 50;

Columns: id, location_id, location_name, user_id, submitted_by, submitted_at, entries (JSON), entry_count, total_car_count, image_url, auction_id

recent_quality_inspections

Latest inspections with defect counts.

SELECT * FROM recent_quality_inspections
WHERE auction_id = 'your-auction-id'
  AND defect_count > 0;

Columns: id, location_id, location_name, user_id, inspector_name, vin_number, barcode, submitted_at, defect_count, auction_id

active_problem_reports

Open problems with action progress.

SELECT * FROM active_problem_reports
WHERE auction_id = 'your-auction-id'
  AND status != 'closed';

Columns: id, issue_name, area, location_id, location_name, status, prepared_by_id, prepared_by_name, assigned_to_id, assigned_to_name, action_count, completed_action_count, submitted_at, updated_at, auction_id

user_auctions

All auctions a user belongs to with branding.

SELECT * FROM user_auctions
WHERE user_id = auth.uid();

Columns: user_id, auction_id, auction_name, auction_slug, logo_url, primary_color, role, joined_at

auction_members_with_details

Members with full user information.

SELECT * FROM auction_members_with_details
WHERE auction_id = 'your-auction-id';

Columns: id, auction_id, user_id, email, full_name, role, joined_at, invited_by, invited_by_name

channel_unread_counts

Unread message counts per channel.

SELECT * FROM channel_unread_counts
WHERE user_id = auth.uid();

Columns: auction_id, channel_id, user_id, unread_count, last_message_at

messages_with_reply_count

Messages with thread statistics.

SELECT * FROM messages_with_reply_count
WHERE channel_id = 'channel-id'
  AND parent_message_id IS NULL
ORDER BY created_at DESC;

Columns: id, auction_id, channel_id, user_id, content, message_type, image_url, created_at, updated_at, parent_message_id, reply_count, last_reply_at, is_edited, is_deleted

RPC Functions

Authorization Functions

Function	Parameters	Returns	Description
`is_super_admin()`	-	BOOLEAN	Check if current user is super admin
`is_auction_member(p_auction_id)`	UUID	BOOLEAN	Check auction membership
`is_auction_admin(p_auction_id)`	UUID	BOOLEAN	Check admin role
`is_auction_admin_or_above(p_auction_id)`	UUID	BOOLEAN	Check admin or owner
`is_auction_owner(p_auction_id)`	UUID	BOOLEAN	Check owner role
`get_user_auction_role(p_auction_id)`	UUID	auction_role	Get user’s role
`is_channel_member(p_channel_id)`	UUID	BOOLEAN	Check channel membership
`is_channel_owner(p_channel_id)`	UUID	BOOLEAN	Check channel ownership
`is_assigned_to_location(location_uuid)`	UUID	BOOLEAN	Check location assignment

Auction Management

Function	Parameters	Returns	Description
`invite_user_to_auction(...)`	auction_id, email, role	UUID	Create invitation
`accept_auction_invite(p_token)`	TEXT	BOOLEAN	Accept and join
`change_member_role(...)`	auction_id, user_id, new_role	BOOLEAN	Update member role
`remove_auction_member(...)`	auction_id, user_id	BOOLEAN	Remove member
`transfer_auction_ownership(...)`	auction_id, new_owner_id	BOOLEAN	Transfer ownership
`get_invite_by_token(p_token)`	TEXT	JSON	Get invitation details

Submissions & Analytics

Function	Parameters	Returns	Description
`create_lot_submission_with_entries(...)`	location_id, entries[], image_url	UUID	Create multi-entry submission
`get_submission_with_entries(p_submission_id)`	UUID	JSON	Get submission with all entries
`get_location_submission_types_ordered(p_location_id)`	UUID	SETOF	Get ordered types for location
`get_pending_submissions()`	-	SETOF	Find overdue submissions
`get_location_stats(...)`	location_id, start_date, end_date	JSON	Location occupancy statistics
`get_all_locations_stats(...)`	start_date, end_date	SETOF	Dashboard statistics
`get_defect_trends(...)`	start_date, end_date, location_id	SETOF	Quality analytics

Chat Functions

Function	Parameters	Returns	Description
`get_channel_notification_preference(p_channel_id)`	UUID	TEXT	Get notification setting
`mark_channel_as_read(p_channel_id)`	UUID	VOID	Update last_read_at
`get_total_unread_count(p_auction_id)`	UUID	INTEGER	Count all unread messages

Reporting Functions

Function	Parameters	Returns	Description
`generate_share_token()`	-	TEXT	Generate URL-safe token
`create_report_share(...)`	report_id, share_type, user_id, expires_days	UUID	Create report share
`get_report_by_token(p_token)`	TEXT	JSON	Get public report
`has_report_access(p_report_id)`	UUID	BOOLEAN	Check report access
`execute_report_query(...)`	dataset_id, sql_query	JSON	Safe SQL execution with RLS

Storage Buckets

lot-images (Public)

Storage for submission and entry photos.

// Upload path structure
const path = `${userId}/${Date.now()}_${randomId}.jpg`;

// Upload example
const { data, error } = await supabase.storage
  .from('lot-images')
  .upload(path, file);

Policies:

Upload: Authenticated users can upload to their own folder
Read: Public access to all images
Delete: Users can delete own images; admins can delete any

avatars (Private)

Storage for user profile photos.

// Upload path structure
const path = `${userId}/${timestamp}.jpg`;

// Upload example
const { data, error } = await supabase.storage
  .from('avatars')
  .upload(path, compressedImage, {
    contentType: 'image/jpeg',
    upsert: true
  });

// Get public URL
const { data: { publicUrl } } = supabase.storage
  .from('avatars')
  .getPublicUrl(path);

Configuration:

File size limit: 5 MB
Allowed MIME types: image/jpeg, image/png, image/webp
Recommended dimensions: 256×256 pixels (mobile), 512×512 pixels (admin)

Policies:

Operation	Rule
Upload	Users can upload only to their own folder (`{user_id}/*`)
Read	Users can read own avatar; admins can read any
Update	Users can replace own avatar only
Delete	Users can delete own avatar only

When a user updates their avatar, the old file should be deleted to prevent storage bloat. The avatar URL is stored in the users.avatar_url column.

auction-branding (Public)

Storage for custom logos and branding assets.

Realtime Subscriptions

Tables with realtime enabled:

Table	Events	Use Case
`messages`	INSERT, UPDATE, DELETE	Live chat delivery
`message_reactions`	INSERT, DELETE	Real-time emoji reactions
`channel_members`	INSERT, DELETE	Membership changes

// Subscribe to new messages
const channel = supabase
  .channel('messages')
  .on(
    'postgres_changes',
    {
      event: 'INSERT',
      schema: 'public',
      table: 'messages',
      filter: `channel_id=eq.${channelId}`
    },
    (payload) => handleNewMessage(payload.new)
  )
  .subscribe();

Entity Relationships

Migration Strategy

How Migrations Work

Supabase uses sequential SQL migration files to manage database schema changes. Migrations are version-controlled and applied in order based on timestamp prefixes.

Migration File Structure

supabase/migrations/
├── 20250101000000_initial_schema.sql           # Core tables
├── 20250101000001_seed_defect_categories.sql   # Seed data
├── 20250101000002_rls_policies.sql             # RLS policies
├── 20250101000003_functions_and_views.sql      # Functions/views
├── ...
└── 20250101000073_fix_token_lookup_padding.sql # Latest fix

Creating New Migrations

# Generate a new migration file
supabase migration new add_new_feature

# This creates:
# supabase/migrations/20250101000074_add_new_feature.sql

Migration Naming Conventions

Pattern	Example	Use Case
`create_{table}_table`	`create_channels_table.sql`	New table
`add_{column}_to_{table}`	`add_auction_id_to_locations.sql`	Add column
`create_{table}_rls_policies`	`create_chat_rls_policies.sql`	RLS policies
`create_{name}_function`	`create_invite_user_function.sql`	New function
`fix_{description}`	`fix_chat_rls_infinite_recursion.sql`	Bug fixes
`update_{table}_for_{feature}`	`update_views_for_auction_context.sql`	Modify existing

Migration Best Practices

Atomic Changes: Each migration should be self-contained and reversible
Idempotent Scripts: Use IF NOT EXISTS and IF EXISTS clauses
Test Locally: Run supabase db reset to test full migration chain
RLS Tests: Update RLS tests when modifying policies

-- Example: Safe table creation
CREATE TABLE IF NOT EXISTS new_feature (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Example: Safe column addition
ALTER TABLE existing_table
ADD COLUMN IF NOT EXISTS new_column TEXT;

Running Migrations

# Apply pending migrations to local database
supabase db push

# Reset database and reapply all migrations
supabase db reset

# Check migration status
supabase migration list

Example Queries

Multi-Entry Lot Submission

// Create submission with multiple entry types
const { data, error } = await supabase.rpc('create_lot_submission_with_entries', {
  p_location_id: locationId,
  p_entries: [
    { submission_type_id: 'type-1-uuid', car_count: 25, image_url: 'https://...' },
    { submission_type_id: 'type-2-uuid', car_count: 12, image_url: 'https://...' }
  ],
  p_image_url: 'https://main-photo-url'
});

Full-Text Chat Search

// Search messages with PostgreSQL full-text search
const { data, error } = await supabase
  .from('messages')
  .select('*')
  .textSearch('content', 'search terms')
  .eq('channel_id', channelId)
  .order('created_at', { ascending: false });

Report Data Query

// Execute AI-generated SQL safely
const { data, error } = await supabase.rpc('execute_report_query', {
  p_dataset_id: datasetId,
  p_sql_query: generatedSql
});

Next Steps

API Endpoints

Learn how to query these tables

Edge Functions

Server-side business logic

Authentication

Secure your API calls

Architecture

System design overview

Authentication

Database

Server Functions

Security & Compliance

​Overview

35 Tables

8 Views

35+ Functions

​Multi-Tenant Architecture

​Authorization Hierarchy

​Table Groups

​User & Authentication Tables

​users

​super_admins

​push_tokens

​Auction (Organization) Tables

​auctions

​auction_members

​auction_invites

​auction_branding

​audit_logs

​Location Management Tables

​locations

​user_location_assignments

​Overwatch Feature Tables

​lot_submissions

​lot_submission_entries

​submission_types

​location_submission_types

​Quality Inspection Tables

​quality_inspections

​defect_categories

​quality_defects

​Problem Reports (A3)

​problem_reports

​problem_facts

​potential_causes

​problem_actions

​problem_kpi_impacts

​Chat System Tables

​channels

​channel_members

​messages

​message_reactions

​Reporting & Analytics Tables

​report_datasets

​report_data_rows

​report_definitions

​report_shares

​report_query_logs

​Staffing Data Tables

​staffing_dataset_types

​staffing_datasets

​staffing_checkin_data

​staffing_cr_data

​staffing_dataset_links

​staffing_activity_settings

​Duplicate Handling Strategy

​Staffing Views

​staffing_vehicle_journey

​Staffing Helper Functions

​Custom Enums

​Indexes Summary

​Index Types

​Key Performance Indexes

​Views

​recent_submissions

​recent_quality_inspections

​active_problem_reports

​user_auctions

​auction_members_with_details

​channel_unread_counts

​messages_with_reply_count

​RPC Functions

​Authorization Functions

​Auction Management

​Submissions & Analytics

​Chat Functions

​Reporting Functions

​Storage Buckets

Overview

Multi-Tenant Architecture

Authorization Hierarchy

Table Groups

User & Authentication Tables

users

super_admins

push_tokens

Auction (Organization) Tables

auctions

auction_members

auction_invites

auction_branding

audit_logs

Location Management Tables

locations

user_location_assignments

Overwatch Feature Tables

lot_submissions

lot_submission_entries

submission_types

location_submission_types

Quality Inspection Tables

quality_inspections

defect_categories

quality_defects

Problem Reports (A3)

problem_reports

problem_facts

potential_causes

problem_actions

problem_kpi_impacts

Chat System Tables

channels

channel_members

messages

message_reactions

Reporting & Analytics Tables

report_datasets

report_data_rows

report_definitions

report_shares

report_query_logs

Staffing Data Tables

staffing_dataset_types

staffing_datasets

staffing_checkin_data

staffing_cr_data

staffing_dataset_links

staffing_activity_settings

Duplicate Handling Strategy

Staffing Views

staffing_vehicle_journey

Staffing Helper Functions

Custom Enums

Indexes Summary

Index Types

Key Performance Indexes

Views

recent_submissions

recent_quality_inspections

active_problem_reports

user_auctions

auction_members_with_details

channel_unread_counts

messages_with_reply_count

RPC Functions

Authorization Functions

Auction Management

Submissions & Analytics

Chat Functions

Reporting Functions

Storage Buckets

lot-images (Public)

avatars (Private)

auction-branding (Public)

Realtime Subscriptions

Entity Relationships

Migration Strategy

How Migrations Work