Design Database Schema

[tinypell3ts]

Description:
Design and implement a database schema using Supabase.

Tasks:

1. Define Database Schema:

   • Create an ERD (Entity-Relationship Diagram) to display the database tables and their relationships.

2. Implement Database:

   • Set up the database in Supabase. Use the login details from the 1Password for the OPENFORMAT Supabase account.
   • Create tables in accordance with the defined database schema.

3. Documentation:

   • Update the README.md to include the finalized database schema.
   • Add instructions in the README.md for developers on setting up Supabase with the agreed-upon database schema.

Acceptance Criteria:

• Database and tables established in Supabase.
• Inclusion of a basic ERD Diagram and database setup instructions in README.md.

[tinypell3ts]

https://supabase.com/docs/guides/auth/managing-user-data

[refugene]

Postgres schema generation:

-- Create the enum type
CREATE TYPE user_type_enum AS ENUM ('web3', 'magic_link');

-- Create Profiles Table
CREATE TABLE "profiles" (
  "id" SERIAL PRIMARY KEY,
  "auth_user_id" UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  "nickname" VARCHAR(255),
  "email_address" VARCHAR(255) UNIQUE,
  "user_type" user_type_enum NOT NULL DEFAULT 'web3'
);

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Indexes for Profiles Table
CREATE UNIQUE INDEX "profiles_nickname_key" ON "profiles" ("nickname");
CREATE INDEX "idx_nickname" ON "profiles" ("nickname");
CREATE INDEX "idx_email_address" ON "profiles" ("email_address");

-- Create Wallets Table
CREATE TABLE "wallets" (
  "eth_address" VARCHAR(42) PRIMARY KEY,
  "profile_id" UUID UNIQUE NOT NULL,
  "private_key" VARCHAR(64),
  FOREIGN KEY ("profile_id") REFERENCES "profiles" ("id")
);

ALTER TABLE public.wallets ENABLE ROW LEVEL SECURITY;

-- Indexes for Wallets Table
CREATE INDEX "idx_wallets_profile_id" ON "wallets" ("profile_id");

-- Function to update email_address in profiles on update of auth.users email
CREATE FUNCTION public.update_user_email()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public
AS $$
BEGIN
  UPDATE public.profiles
  SET email_address = NEW.email
  WHERE id = NEW.id;
  RETURN NEW;
END;
$$;

-- Trigger to call update_user_email function on email update
CREATE TRIGGER on_auth_user_email_updated
AFTER UPDATE OF email ON auth.users
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email)
EXECUTE PROCEDURE public.update_user_email();

[refugene]

Updated the README file with instructions for setting up the database in Supabase, SQL schema and an entity relationship diagram

[refugene]

• Added a enum type to public.profiles which can be web3 or magic_link and lets us know if the user registered with an email address and hence has an entry in auth.users
• Added a DB trigger to update public.profiles.email_address on auth.users.email update