The Schema Doesn't Work

[2-fly-4-ai]

As mentioned, the Schema is not working. Try this if you having issues.

`CREATE OR REPLACE FUNCTION moddatetime()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE SCHEMA IF NOT EXISTS public;

CREATE TABLE public.profiles (
id uuid NOT NULL,
updated_at timestamp with time zone NULL,
username text NULL,
full_name text NULL,
avatar_url text NULL,
website text NULL,
CONSTRAINT profiles_pkey PRIMARY KEY (id),
CONSTRAINT profiles_username_key UNIQUE (username),
CONSTRAINT profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users (id),
CONSTRAINT username_length CHECK ((char_length(username) >= 3))
) TABLESPACE pg_default;

CREATE TABLE public.categories (
id uuid NOT NULL DEFAULT gen_random_uuid(),
title text NULL DEFAULT ''::text,
created_at timestamp with time zone NULL DEFAULT now(),
slug text NULL,
CONSTRAINT category_pkey PRIMARY KEY (id),
CONSTRAINT category_id_key UNIQUE (id)
) TABLESPACE pg_default;

CREATE TABLE public.posts (
id uuid NOT NULL DEFAULT gen_random_uuid(),
category_id uuid NULL,
title text NULL,
image text NULL,
description text NULL,
content text NULL,
created_at timestamp with time zone NULL DEFAULT now(),
updated_at timestamp with time zone NULL,
slug text NULL DEFAULT ''::text,
author_id uuid NULL,
published boolean NULL DEFAULT false,
CONSTRAINT post_pkey PRIMARY KEY (id),
CONSTRAINT post_id_key UNIQUE (id),
CONSTRAINT post_slug_key UNIQUE (slug),
CONSTRAINT posts_author_id_fkey FOREIGN KEY (author_id) REFERENCES profiles (id),
CONSTRAINT posts_category_id_fkey FOREIGN KEY (category_id) REFERENCES categories (id)
) TABLESPACE pg_default;

CREATE TABLE public.comments (
id uuid NOT NULL DEFAULT gen_random_uuid(),
comment text NULL DEFAULT ''::text,
created_at timestamp with time zone NULL DEFAULT now(),
user_id uuid NULL,
post_id uuid NULL,
CONSTRAINT comments_pkey PRIMARY KEY (id),
CONSTRAINT comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
CONSTRAINT comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES profiles (id) ON DELETE CASCADE
) TABLESPACE pg_default;

CREATE TABLE public.bookmarks (
id uuid NOT NULL,
user_id uuid NULL,
created_at timestamp with time zone NULL DEFAULT now(),
CONSTRAINT bookmarks_pkey PRIMARY KEY (id),
CONSTRAINT bookmarks_id_fkey FOREIGN KEY (id) REFERENCES posts (id) ON DELETE CASCADE,
CONSTRAINT bookmarks_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users (id) ON DELETE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER handle_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION moddatetime('updated_at');

CREATE TABLE public.drafts (
id uuid NOT NULL DEFAULT gen_random_uuid(),
category_id uuid NULL,
title text NULL DEFAULT 'Untitled'::text,
slug text NULL DEFAULT 'untitled'::text,
image text NULL,
description text NULL,
content text NULL,
created_at timestamp with time zone NOT NULL DEFAULT now(),
updated_at timestamp without time zone NULL,
author_id uuid NULL,
published boolean NULL DEFAULT false,
CONSTRAINT drafts_pkey PRIMARY KEY (id),
CONSTRAINT drafts_author_id_fkey FOREIGN KEY (author_id) REFERENCES profiles (id),
CONSTRAINT drafts_category_id_fkey FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE
) TABLESPACE pg_default;

CREATE TRIGGER handle_updated_at
BEFORE UPDATE ON drafts
FOR EACH ROW
EXECUTE FUNCTION moddatetime('updated_at');

-- Modify the handle_new_user() function
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'handle_new_user() called for user ID: %', NEW.id;
INSERT INTO public.profiles (id, updated_at, full_name, avatar_url)
VALUES (
NEW.id,
CURRENT_TIMESTAMP,
COALESCE(jsonb_extract_path_text(NEW.raw_user_meta_data, 'full_name'), NULL),
COALESCE(jsonb_extract_path_text(NEW.raw_user_meta_data, 'picture'), jsonb_extract_path_text(NEW.raw_user_meta_data, 'avatar_url'), NULL)
)
ON CONFLICT (id) DO NOTHING;

RETURN NEW;

END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create the trigger to handle new user creation
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Grant necessary permissions
GRANT USAGE ON SCHEMA "public" TO anon;
GRANT USAGE ON SCHEMA "public" TO authenticated;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "public" TO authenticated;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "public" TO anon;

GRANT EXECUTE ON FUNCTION public.handle_new_user() TO postgres;
GRANT INSERT ON public.profiles TO authenticated;
GRANT INSERT ON public.profiles TO anon;`

[1337Impact]

try creating each table on it's own. this will give you a dependency conflict, just go trow it till you find the table that deosn't depend on any other table. start from that and go down to the other tables.

note: also triggers for updated_at will not work. you basically need to create them separately. (or if you are lazy like me, I just chose to not add them).

[Mohhaliim]

you evert got this sorted especially the moddatetime function?

[Chaitanya-NK]

{
code: '23503',
details: 'Key (author_id)=(97cbb3ce-c748-449a-a185-815a547b370e) is not present in table "profiles".',
hint: null,
message: 'insert or update on table "drafts" violates foreign key constraint "drafts_author_id_fkey"'
}
I am getting this issue. What to do