Supabase Inactive Fix

This project helps prevent Supabase projects from pausing due to inactivity by periodically inserting, monitoring, and deleting entries in the specified tables of multiple Supabase databases. The project uses a configuration file (config.json) to define multiple databases and automate the keep-alive actions.

Features

• Insert a random string into a specified table for each Supabase database.
• Monitor the number of entries in the table.
• Automatically delete entries if the table contains more than a specified number of records.
• Log successes and failures, and generate a detailed status report.

Requirements

• Python 3.8+
• A Supabase account and API keys for each database.

Setup

1. Clone the repository:

   git clone https://github.com/travisvn/supabase-inactive-fix.git
   cd supabase-inactive-fix

2. Install the required dependencies:

   python -m venv venv
   source venv/bin/activate  # On Windows use `venv\Scripts\activate`
   pip install -r requirements.txt

3. Create a config.json file in the project root. This file defines your Supabase databases.

   Example configuration:

   [
     {
       "name": "Database1",
       "supabase_url": "https://your-supabase-url-1.supabase.co",
       "supabase_key_env": "SUPABASE_KEY_1",  // Use environment variable for the key
       "table_name": "KeepAlive"
     },
     {
       "name": "Database2",
       "supabase_url": "https://your-supabase-url-2.supabase.co",
       "supabase_key": "your-direct-supabase-key",  // Directly define the key
       "table_name": "keep-alive"
     }
   ]

   See the section below for how to easily configure your database

   Environment Variables Explained

   In the config.json file, you can define either:

   • Direct API Key: Use the "supabase_key" field to directly specify your Supabase API key.
   • Environment Variable: Use the "supabase_key_env" field to reference an environment variable where the key is stored. This is more secure, especially when running the script in different environments.

   Example:

   • "supabase_key_env": "SUPABASE_KEY_1": This tells the script to look for an environment variable called SUPABASE_KEY_1 that contains the actual API key.
   • "supabase_key": "your-direct-supabase-key": This directly provides the API key within the config.json file, which is less secure but simpler for local setups.

4. Set up your environment variables if you're using them:

   Create a .env file and store variables there

   SUPABASE_KEY_1="your-supabase-key-1"
   SUPABASE_KEY_2="your-supabase-key-2"
   

5. Run the script:

   python main.py

Supabase Database Setup

This project is predicated on accessing a keep-alive table in your Postgres database on Supabase.

Sample SQL

Here's a SQL query for a keep-alive table

CREATE TABLE "keep-alive" (
  id BIGINT generated BY DEFAULT AS IDENTITY,
  name text NULL DEFAULT '':: text,
  random uuid NULL DEFAULT gen_random_uuid (),
  CONSTRAINT "keep-alive_pkey" PRIMARY key (id)
);

INSERT INTO
  "keep-alive"(name)
VALUES
  ('placeholder'),
  ('example');

Cron Job Setup

To automate this script, you can create a cron job that runs the script periodically. Below are instructions for setting this up on macOS, Linux, and Windows.

macOS/Linux

1. Open your crontab file for editing:

   crontab -e

2. Add a new cron job to run the script every Monday and Thursday at midnight:

   0 0 * * 1,4 cd /path/to/your/project && /path/to/your/project/venv/bin/python main.py >> /path/to/your/project/logfile.log 2>&1

This example cron job will:

• Navigate to the project directory.
• Run the Python script using the virtual environment.
• Append the output to a logfile.

For reference, here’s an example used in development:

0 0 * * 1,4 cd /Users/travis/Workspace/supabase-inactive-fix && /Users/travis/Workspace/supabase-inactive-fix/venv/bin/python main.py >> /Users/travis/Workspace/supabase-inactive-fix/logfile.log 2>&1

Windows (Task Scheduler)

Windows does not have cron jobs, but you can achieve similar functionality using Task Scheduler.

1. Open Task Scheduler and select Create Basic Task.

2. Name the task and set the trigger to run weekly.

3. Set the days (e.g., Monday and Thursday) and time (e.g., midnight) when the script should run.

4. In the Action step, select Start a Program, and point it to your Python executable within your virtual environment. For example:

   C:\path\to\your\project\venv\Scripts\python.exe

5. In the Arguments field, specify the path to the script:

   C:\path\to\your\project\main.py

6. Save the task. The script will now run automatically according to the schedule you specified.

Contribution

Feel free to open an issue or submit a pull request if you'd like to contribute to this project.

License

This project is licensed under the MIT License. See the LICENSE file for more details.