MetatroncubeSoftwareSolutions/The_Vibe
3
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
The_Vibe/packages/database/schema.sql
metatroncubeswdev 89cf37f5b5 Initial commit — The Vibe fair-trade delivery platform 
- NestJS backend: auth, restaurants, orders, drivers, payments, tracking, reviews, zones, admin, email
- Next.js 14 frontend: landing, restaurants, checkout, tracking, dashboards, onboarding
- Expo mobile app: driver orders and earnings screens
- PostgreSQL + PostGIS schema with seed data
- Docker Compose for local dev (Postgres, Redis, OSRM)
- MapLibre GL + OpenStreetMap integration
- Stripe subscription and payment processing

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
2026-03-04 13:26:55 -05:00

559 lines
22 KiB
PL/PgSQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- ============================================================
-- The Vibe - Fair-Trade Delivery Platform
-- PostgreSQL + PostGIS Schema
-- ============================================================
-- Extensions
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- fuzzy search for restaurant names
-- ============================================================
-- ENUMS
-- ============================================================
CREATE TYPE user_role AS ENUM ('customer', 'driver', 'restaurant_owner', 'admin');
CREATE TYPE order_status AS ENUM (
'pending',
'confirmed',
'preparing',
'ready_for_pickup',
'driver_assigned',
'picked_up',
'delivered',
'cancelled'
);
CREATE TYPE subscription_status AS ENUM ('active', 'past_due', 'cancelled', 'trialing');
CREATE TYPE driver_session_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE zone_status AS ENUM ('active', 'inactive', 'coming_soon');
CREATE TYPE payment_status AS ENUM ('pending', 'succeeded', 'failed', 'refunded');
-- ============================================================
-- USERS (all roles share this table)
-- ============================================================
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email VARCHAR(255) UNIQUE NOT NULL,
phone VARCHAR(20),
password_hash TEXT NOT NULL,
role user_role NOT NULL,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
avatar_url TEXT,
is_verified BOOLEAN DEFAULT FALSE,
is_active BOOLEAN DEFAULT TRUE,
stripe_customer_id VARCHAR(255),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_stripe ON users(stripe_customer_id);
-- ============================================================
-- ZONES (GTA Geofencing - PostGIS)
-- ============================================================
CREATE TABLE zones (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(100) NOT NULL, -- e.g. "Downtown Toronto"
slug VARCHAR(100) UNIQUE NOT NULL, -- e.g. "downtown-toronto"
boundary GEOGRAPHY(POLYGON, 4326) NOT NULL,
center GEOGRAPHY(POINT, 4326) NOT NULL,
radius_km DECIMAL(6,2), -- for quick distance checks
status zone_status DEFAULT 'active',
priority INTEGER DEFAULT 0, -- higher = launched first
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_zones_boundary ON zones USING GIST(boundary);
CREATE INDEX idx_zones_center ON zones USING GIST(center);
CREATE INDEX idx_zones_status ON zones(status);
-- ============================================================
-- RESTAURANTS
-- ============================================================
CREATE TABLE restaurants (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
description TEXT,
cuisine_type VARCHAR(100)[], -- array: ["Italian", "Pizza"]
phone VARCHAR(20),
email VARCHAR(255),
address TEXT NOT NULL,
city VARCHAR(100) DEFAULT 'Toronto',
province VARCHAR(10) DEFAULT 'ON',
postal_code VARCHAR(10),
location GEOGRAPHY(POINT, 4326) NOT NULL,
zone_id UUID REFERENCES zones(id),
logo_url TEXT,
banner_url TEXT,
is_active BOOLEAN DEFAULT TRUE,
is_open BOOLEAN DEFAULT FALSE,
accepts_orders BOOLEAN DEFAULT TRUE,
avg_prep_time_minutes INTEGER DEFAULT 20,
min_order_amount DECIMAL(10,2) DEFAULT 0,
rating DECIMAL(3,2), -- 0.00 to 5.00
total_reviews INTEGER DEFAULT 0,
-- Savings tracking vs competitors
total_orders_platform INTEGER DEFAULT 0,
total_savings_vs_uber DECIMAL(12,2) DEFAULT 0, -- cumulative savings
-- Stripe
stripe_account_id VARCHAR(255), -- for future payout features
subscription_id VARCHAR(255), -- Stripe subscription ID
subscription_status subscription_status,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_restaurants_location ON restaurants USING GIST(location);
CREATE INDEX idx_restaurants_zone ON restaurants(zone_id);
CREATE INDEX idx_restaurants_owner ON restaurants(owner_id);
CREATE INDEX idx_restaurants_active ON restaurants(is_active, is_open);
CREATE INDEX idx_restaurants_name ON restaurants USING gin(name gin_trgm_ops);
-- ============================================================
-- MENUS & MENU ITEMS
-- ============================================================
CREATE TABLE menu_categories (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
restaurant_id UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
name VARCHAR(100) NOT NULL,
description TEXT,
sort_order INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_menu_categories_restaurant ON menu_categories(restaurant_id);
CREATE TABLE menu_items (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
restaurant_id UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
category_id UUID REFERENCES menu_categories(id) ON DELETE SET NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL, -- in-store price (parity guaranteed)
image_url TEXT,
is_available BOOLEAN DEFAULT TRUE,
is_featured BOOLEAN DEFAULT FALSE,
dietary_tags VARCHAR(50)[], -- ["vegan", "gluten-free", "halal"]
allergens VARCHAR(50)[], -- ["nuts", "dairy"]
prep_time_min INTEGER DEFAULT 10,
calories INTEGER,
sort_order INTEGER DEFAULT 0,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_menu_items_restaurant ON menu_items(restaurant_id);
CREATE INDEX idx_menu_items_category ON menu_items(category_id);
CREATE INDEX idx_menu_items_available ON menu_items(is_available);
-- ============================================================
-- DRIVERS
-- ============================================================
CREATE TABLE drivers (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE RESTRICT,
zone_id UUID REFERENCES zones(id),
vehicle_type VARCHAR(50), -- "bicycle", "car", "ebike", "scooter"
vehicle_plate VARCHAR(20),
license_number VARCHAR(50),
is_background_checked BOOLEAN DEFAULT FALSE,
is_approved BOOLEAN DEFAULT FALSE,
rating DECIMAL(3,2),
total_deliveries INTEGER DEFAULT 0,
total_earnings DECIMAL(12,2) DEFAULT 0,
total_tips DECIMAL(12,2) DEFAULT 0,
-- Stripe
stripe_payment_method VARCHAR(255), -- saved card for daily fee
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_drivers_user ON drivers(user_id);
CREATE INDEX idx_drivers_zone ON drivers(zone_id);
CREATE INDEX idx_drivers_approved ON drivers(is_approved);
-- ============================================================
-- DRIVER SESSIONS (daily login fee tracking)
-- ============================================================
CREATE TABLE driver_sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
driver_id UUID NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
session_date DATE NOT NULL DEFAULT CURRENT_DATE,
login_at TIMESTAMPTZ DEFAULT NOW(),
logout_at TIMESTAMPTZ,
daily_fee DECIMAL(10,2) DEFAULT 20.00,
fee_paid BOOLEAN DEFAULT FALSE,
fee_payment_intent VARCHAR(255), -- Stripe PaymentIntent ID
fee_paid_at TIMESTAMPTZ,
-- Break-even tracking
deliveries_count INTEGER DEFAULT 0,
delivery_revenue DECIMAL(10,2) DEFAULT 0, -- deliveries × $5
tips_earned DECIMAL(10,2) DEFAULT 0,
net_earnings DECIMAL(10,2) DEFAULT 0, -- revenue + tips - daily_fee
status driver_session_status DEFAULT 'active',
UNIQUE(driver_id, session_date)
);
CREATE INDEX idx_driver_sessions_driver ON driver_sessions(driver_id);
CREATE INDEX idx_driver_sessions_date ON driver_sessions(session_date);
CREATE INDEX idx_driver_sessions_fee ON driver_sessions(fee_paid);
-- ============================================================
-- DRIVER LOCATION (real-time PostGIS)
-- ============================================================
CREATE TABLE driver_locations (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
driver_id UUID NOT NULL UNIQUE REFERENCES drivers(id) ON DELETE CASCADE,
location GEOGRAPHY(POINT, 4326) NOT NULL,
heading DECIMAL(5,2), -- degrees 0-360
speed_kmh DECIMAL(6,2),
is_online BOOLEAN DEFAULT FALSE,
is_available BOOLEAN DEFAULT FALSE, -- online AND not on a delivery
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_driver_locations_location ON driver_locations USING GIST(location);
CREATE INDEX idx_driver_locations_available ON driver_locations(is_online, is_available);
-- ============================================================
-- ORDERS
-- ============================================================
CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
order_number SERIAL, -- human-readable order number
customer_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
restaurant_id UUID NOT NULL REFERENCES restaurants(id) ON DELETE RESTRICT,
driver_id UUID REFERENCES drivers(id) ON DELETE SET NULL,
zone_id UUID REFERENCES zones(id),
-- Addresses (stored as text + geo)
delivery_address TEXT NOT NULL,
delivery_location GEOGRAPHY(POINT, 4326) NOT NULL,
restaurant_location GEOGRAPHY(POINT, 4326), -- snapshot at order time
-- Pricing (all transparent, no hidden fees)
subtotal DECIMAL(10,2) NOT NULL, -- sum of items
delivery_fee DECIMAL(10,2) DEFAULT 5.00,
tip_amount DECIMAL(10,2) DEFAULT 0,
platform_fee DECIMAL(10,2) DEFAULT 0.10, -- restaurant pays
cc_processing_fee DECIMAL(10,2), -- calculated at checkout
total_customer_pays DECIMAL(10,2) NOT NULL, -- subtotal + delivery + tip + cc
restaurant_receives DECIMAL(10,2), -- subtotal - platform_fee - cc
driver_receives DECIMAL(10,2), -- delivery_fee + tip
-- Savings display
uber_equivalent_fee DECIMAL(10,2), -- what UberEats would charge (30%)
restaurant_savings DECIMAL(10,2), -- uber_fee - platform_fee
-- Status
status order_status DEFAULT 'pending',
special_instructions TEXT,
estimated_pickup_at TIMESTAMPTZ,
estimated_delivery_at TIMESTAMPTZ,
picked_up_at TIMESTAMPTZ,
delivered_at TIMESTAMPTZ,
cancelled_at TIMESTAMPTZ,
cancellation_reason TEXT,
-- Stripe
payment_intent_id VARCHAR(255),
payment_status payment_status DEFAULT 'pending',
-- OSRM route snapshot
route_polyline TEXT, -- encoded polyline
distance_km DECIMAL(8,3),
duration_minutes INTEGER,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_restaurant ON orders(restaurant_id);
CREATE INDEX idx_orders_driver ON orders(driver_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_orders_delivery_location ON orders USING GIST(delivery_location);
-- ============================================================
-- ORDER ITEMS
-- ============================================================
CREATE TABLE order_items (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
menu_item_id UUID NOT NULL REFERENCES menu_items(id) ON DELETE RESTRICT,
name VARCHAR(255) NOT NULL, -- snapshot at order time
price DECIMAL(10,2) NOT NULL, -- snapshot at order time
quantity INTEGER NOT NULL DEFAULT 1,
subtotal DECIMAL(10,2) NOT NULL, -- price × quantity
special_request TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_order_items_order ON order_items(order_id);
-- ============================================================
-- DRIVER EARNINGS (per-delivery record)
-- ============================================================
CREATE TABLE driver_earnings (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
driver_id UUID NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
session_id UUID NOT NULL REFERENCES driver_sessions(id) ON DELETE CASCADE,
order_id UUID NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
delivery_fee DECIMAL(10,2) DEFAULT 5.00,
tip_amount DECIMAL(10,2) DEFAULT 0,
total DECIMAL(10,2) NOT NULL, -- delivery_fee + tip
is_profit BOOLEAN DEFAULT FALSE, -- true if past break-even
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_driver_earnings_driver ON driver_earnings(driver_id);
CREATE INDEX idx_driver_earnings_session ON driver_earnings(session_id);
CREATE INDEX idx_driver_earnings_order ON driver_earnings(order_id);
-- ============================================================
-- RESTAURANT SUBSCRIPTIONS
-- ============================================================
CREATE TABLE restaurant_subscriptions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
restaurant_id UUID NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
stripe_subscription_id VARCHAR(255) UNIQUE NOT NULL,
stripe_customer_id VARCHAR(255) NOT NULL,
status subscription_status DEFAULT 'active',
current_period_start TIMESTAMPTZ,
current_period_end TIMESTAMPTZ,
monthly_fee DECIMAL(10,2) DEFAULT 500.00,
trial_ends_at TIMESTAMPTZ,
cancelled_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_subscriptions_restaurant ON restaurant_subscriptions(restaurant_id);
CREATE INDEX idx_subscriptions_stripe ON restaurant_subscriptions(stripe_subscription_id);
CREATE INDEX idx_subscriptions_status ON restaurant_subscriptions(status);
-- ============================================================
-- DELIVERY TRACKING (breadcrumb trail)
-- ============================================================
CREATE TABLE delivery_tracking (
id BIGSERIAL PRIMARY KEY, -- high insert rate, use bigserial
order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
driver_id UUID NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
location GEOGRAPHY(POINT, 4326) NOT NULL,
heading DECIMAL(5,2),
speed_kmh DECIMAL(6,2),
recorded_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_delivery_tracking_order ON delivery_tracking(order_id);
CREATE INDEX idx_delivery_tracking_recorded ON delivery_tracking(recorded_at DESC);
-- Partition by month for performance at scale
-- CREATE TABLE delivery_tracking_2025_01 PARTITION OF delivery_tracking
-- FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
-- ============================================================
-- PLATFORM ANALYTICS (materialized for performance)
-- ============================================================
CREATE TABLE daily_platform_stats (
stat_date DATE PRIMARY KEY,
total_orders INTEGER DEFAULT 0,
total_revenue DECIMAL(12,2) DEFAULT 0, -- all delivery fees collected
total_driver_fees DECIMAL(12,2) DEFAULT 0, -- daily login fees
total_restaurant_fees DECIMAL(12,2) DEFAULT 0, -- per-order fees
active_drivers INTEGER DEFAULT 0,
active_restaurants INTEGER DEFAULT 0,
new_customers INTEGER DEFAULT 0,
avg_delivery_time INTEGER, -- minutes
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- ============================================================
-- REVIEWS
-- ============================================================
CREATE TABLE reviews (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
order_id UUID NOT NULL UNIQUE REFERENCES orders(id) ON DELETE CASCADE,
customer_id UUID NOT NULL REFERENCES users(id),
restaurant_id UUID REFERENCES restaurants(id),
driver_id UUID REFERENCES drivers(id),
restaurant_rating INTEGER CHECK (restaurant_rating BETWEEN 1 AND 5),
driver_rating INTEGER CHECK (driver_rating BETWEEN 1 AND 5),
comment TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_reviews_restaurant ON reviews(restaurant_id);
CREATE INDEX idx_reviews_driver ON reviews(driver_id);
-- ============================================================
-- FUNCTIONS & TRIGGERS
-- ============================================================
-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER trg_restaurants_updated_at
BEFORE UPDATE ON restaurants FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER trg_menu_items_updated_at
BEFORE UPDATE ON menu_items FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER trg_orders_updated_at
BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION update_updated_at();
-- Update driver session when an earning is recorded
CREATE OR REPLACE FUNCTION update_driver_session_on_earning()
RETURNS TRIGGER AS $$
DECLARE
v_daily_fee DECIMAL(10,2);
BEGIN
SELECT daily_fee INTO v_daily_fee
FROM driver_sessions WHERE id = NEW.session_id;
UPDATE driver_sessions SET
deliveries_count = deliveries_count + 1,
delivery_revenue = delivery_revenue + NEW.delivery_fee,
tips_earned = tips_earned + NEW.tip_amount,
net_earnings = (delivery_revenue + NEW.delivery_fee) + (tips_earned + NEW.tip_amount) - daily_fee
WHERE id = NEW.session_id;
-- Mark earning as profit if past break-even
UPDATE driver_earnings SET
is_profit = (
SELECT net_earnings >= 0
FROM driver_sessions WHERE id = NEW.session_id
)
WHERE id = NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_driver_earnings_session
AFTER INSERT ON driver_earnings
FOR EACH ROW EXECUTE FUNCTION update_driver_session_on_earning();
-- Update restaurant savings on order completion
CREATE OR REPLACE FUNCTION update_restaurant_savings()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.status = 'delivered' AND OLD.status != 'delivered' THEN
UPDATE restaurants SET
total_orders_platform = total_orders_platform + 1,
total_savings_vs_uber = total_savings_vs_uber + COALESCE(NEW.restaurant_savings, 0)
WHERE id = NEW.restaurant_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_order_savings
AFTER UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_restaurant_savings();
-- Check delivery location is within an active zone
CREATE OR REPLACE FUNCTION check_delivery_in_zone(p_location GEOGRAPHY)
RETURNS UUID AS $$
DECLARE
v_zone_id UUID;
BEGIN
SELECT id INTO v_zone_id
FROM zones
WHERE status = 'active'
AND ST_Within(p_location::geometry, boundary::geometry)
ORDER BY priority DESC
LIMIT 1;
RETURN v_zone_id;
END;
$$ LANGUAGE plpgsql;
-- Find available drivers within radius of a point
CREATE OR REPLACE FUNCTION find_nearby_drivers(
p_location GEOGRAPHY,
p_radius_km DECIMAL DEFAULT 5.0,
p_limit INTEGER DEFAULT 10
)
RETURNS TABLE(driver_id UUID, distance_m FLOAT) AS $$
BEGIN
RETURN QUERY
SELECT
dl.driver_id,
ST_Distance(dl.location, p_location) AS distance_m
FROM driver_locations dl
JOIN drivers d ON d.id = dl.driver_id
WHERE
dl.is_online = TRUE
AND dl.is_available = TRUE
AND d.is_approved = TRUE
AND ST_DWithin(dl.location, p_location, p_radius_km * 1000)
ORDER BY distance_m ASC
LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;
-- ============================================================
-- VIEWS
-- ============================================================
-- Driver dashboard view
CREATE VIEW v_driver_dashboard AS
SELECT
d.id AS driver_id,
u.first_name,
u.last_name,
ds.session_date,
ds.deliveries_count,
ds.daily_fee,
ds.delivery_revenue,
ds.tips_earned,
ds.net_earnings,
-- Break-even logic
GREATEST(0, ds.daily_fee - ds.delivery_revenue) AS remaining_to_break_even,
CEIL(GREATEST(0, ds.daily_fee - ds.delivery_revenue) / 5.0) AS deliveries_to_break_even,
ds.delivery_revenue >= ds.daily_fee AS has_broken_even,
ds.fee_paid
FROM drivers d
JOIN users u ON u.id = d.user_id
LEFT JOIN driver_sessions ds ON ds.driver_id = d.id
AND ds.session_date = CURRENT_DATE;
-- Restaurant savings dashboard view
CREATE VIEW v_restaurant_savings AS
SELECT
r.id AS restaurant_id,
r.name,
r.total_orders_platform,
r.total_savings_vs_uber,
-- Monthly stats
COUNT(o.id) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS orders_this_month,
SUM(o.platform_fee) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS platform_fees_this_month,
SUM(o.uber_equivalent_fee) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS uber_would_have_charged,
SUM(o.restaurant_savings) FILTER (WHERE o.created_at >= date_trunc('month', NOW())) AS saved_this_month
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.id AND o.status = 'delivered'
GROUP BY r.id, r.name, r.total_orders_platform, r.total_savings_vs_uber;
Reference in New Issue View Git Blame Copy Permalink