When time to live of redis expires, we need to save this data in a historical table

[nang-dev]

@nang-dev thoughts on this addition to the schema

-- Ensure the table does not already exist to avoid errors

DROP TABLE IF EXISTS historical_quotas;



-- Create the table for storing historical quotas

CREATE TABLE IF NOT EXISTS historical_quotas (

    id SERIAL PRIMARY KEY,

    user_id UUID NOT NULL,

    quota_type VARCHAR(50) NOT NULL,

    usage_value INTEGER NOT NULL CHECK (usage_value >= 0),

    recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES profiles (id) ON DELETE CASCADE

);



-- Create an index to optimize querying by user_id/recorded_at/type

CREATE INDEX IF NOT EXISTS idx_historical_quotas_recorded_at

ON historical_quotas (recorded_at);



CREATE INDEX IF NOT EXISTS idx_historical_quotas_user_id

ON historical_quotas (user_id);



CREATE INDEX IF NOT EXISTS idx_historical_quotas_quota_type

ON historical_quotas (quota_type);



-- Enable row-level security on the historical_quotas table

ALTER TABLE historical_quotas ENABLE ROW LEVEL SECURITY;



-- Policy to allow users to read their own historical quota data

CREATE POLICY "User can read their own historical quota data."

ON historical_quotas FOR SELECT

USING ( (SELECT auth.uid()) = user_id );

Originally posted by @ItWasEnder in #88

[nang-dev]

oh also if requests from redis become < curr requests in supabase that means we should have another row (quota got reset)