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?