77 lines
2.5 KiB
PL/PgSQL
77 lines
2.5 KiB
PL/PgSQL
-- Add tables for AI route generation feature
|
|
|
|
-- 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
|
|
USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can insert their own generated routes"
|
|
ON generated_routes FOR INSERT
|
|
WITH CHECK (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can update their own generated routes"
|
|
ON generated_routes FOR UPDATE
|
|
USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can delete their own generated routes"
|
|
ON generated_routes FOR DELETE
|
|
USING (auth.uid() = user_id);
|
|
|
|
-- 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
|
|
USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can insert their own API usage"
|
|
ON api_usage FOR INSERT
|
|
WITH CHECK (auth.uid() = user_id);
|
|
|
|
-- Function to update updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_generated_routes_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Trigger for updated_at
|
|
CREATE TRIGGER update_generated_routes_updated_at
|
|
BEFORE UPDATE ON generated_routes
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_generated_routes_updated_at();
|
|
|
|
COMMENT ON TABLE generated_routes IS 'Stores AI-generated route recommendations for trips';
|
|
COMMENT ON TABLE api_usage IS 'Tracks API usage for rate limiting and analytics';
|