66 lines
2.1 KiB
SQL
66 lines
2.1 KiB
SQL
-- Add route generation tables and API usage tracking
|
|
|
|
-- Generated routes table
|
|
CREATE TABLE IF NOT EXISTS generated_routes (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
trip_id UUID REFERENCES trips(id) ON DELETE CASCADE,
|
|
user_id UUID REFERENCES auth.users(id),
|
|
preferences JSONB NOT NULL,
|
|
route_data JSONB NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- API usage tracking table
|
|
CREATE TABLE IF NOT EXISTS api_usage (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id UUID REFERENCES auth.users(id),
|
|
api_type TEXT NOT NULL,
|
|
endpoint TEXT NOT NULL,
|
|
request_count INTEGER DEFAULT 1,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_generated_routes_trip_id ON generated_routes(trip_id);
|
|
CREATE INDEX IF NOT EXISTS idx_generated_routes_user_id ON generated_routes(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_api_usage_user_id ON api_usage(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_api_usage_created_at ON api_usage(created_at);
|
|
|
|
-- RLS Policies for generated_routes
|
|
ALTER TABLE generated_routes ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Users can view their own generated routes"
|
|
ON generated_routes FOR SELECT
|
|
TO authenticated
|
|
USING (user_id = auth.uid());
|
|
|
|
CREATE POLICY "Users can insert their own generated routes"
|
|
ON generated_routes FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
CREATE POLICY "Users can update their own generated routes"
|
|
ON generated_routes FOR UPDATE
|
|
TO authenticated
|
|
USING (user_id = auth.uid())
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
CREATE POLICY "Users can delete their own generated routes"
|
|
ON generated_routes FOR DELETE
|
|
TO authenticated
|
|
USING (user_id = auth.uid());
|
|
|
|
-- RLS Policies for api_usage
|
|
ALTER TABLE api_usage ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Users can view their own API usage"
|
|
ON api_usage FOR SELECT
|
|
TO authenticated
|
|
USING (user_id = auth.uid());
|
|
|
|
-- Only service_role can insert API usage records
|
|
CREATE POLICY "Service role can insert API usage"
|
|
ON api_usage FOR INSERT
|
|
WITH CHECK (auth.role() = 'service_role');
|