flexible database design

[hitaspdotnet]

Thanks for amazing product! as a boilerplate | template you should continue with flexible database so users can customize it with less conflict between database and UI. Here is an example for user table:

-- Table per migration for initial migrations with common props and extra properties for future user's needs. 
-------------------------------------------------------
-- Section - Tables
-------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.users(
    id uuid NOT NULL REFERENCES auth.users(id),
    -- COLUMNS
    account_name app.valid_name NOT NULL UNIQUE,
    display_name text CHECK (LENGTH(display_name) <= 128),
    bio text CHECK (LENGTH(bio) <= 512),
    avatar_id uuid REFERENCES storage.objects(id) ON DELETE SET NULL,
    -- TRACKER
    created_at timestamp with time zone DEFAULT NOW(),
    created_by uuid REFERENCES auth.users(id),
    updated_at timestamp with time zone DEFAULT NOW(),
    updated_by uuid REFERENCES auth.users(id),
    -- EXTRA PROPERTIES
    private_metadata jsonb DEFAULT '{}' ::jsonb,
    public_metadata jsonb DEFAULT '{}' ::jsonb,
    -- KEYS
    PRIMARY KEY (id),
);

-------------------------------------------------------
-- Section - Indexs
-------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_users_account_registry_account_name ON public.users(account_name);

-------------------------------------------------------
-- Section - Trigger Functions
-------------------------------------------------------
-- understandable naming for domain and public functions. 
-- ex: schema.returnType_table_on_event for trigger function
CREATE OR REPLACE FUNCTION app.trigger_users_on_auth_user_creating()

-------------------------------------------------------
-- Section - TRIGGERS
-------------------------------------------------------
-- understandable naming for triggers. 
-- ex: schema.returnType_table_on_event for trigger function
CREATE TRIGGER app_set_users_timestamp
    BEFORE INSERT OR UPDATE ON public.users
    FOR EACH ROW
    EXECUTE PROCEDURE app.trigger_set_timestamps();

CREATE TRIGGER app_set_users_tracker
    BEFORE INSERT OR UPDATE ON public.users
    FOR EACH ROW
    EXECUTE PROCEDURE app.trigger_set_tracker();

CREATE TRIGGER app_users_on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE PROCEDURE app.trigger_users_on_auth_user_created();

-------------------------------------------------------
-- Section - domain Functions
-------------------------------------------------------
-- keep domain functions (security or db related functions) in secure schema 
CREATE OR REPLACE FUNCTION app.is_account_owner(user_id uuid, account_name app.valid_name)

GRANT EXECUTE ON FUNCTION app.is_account_owner(uuid, app.valid_name) TO authenticated, service_role;

-------------------------------------------------------
-- Section - Security
-------------------------------------------------------
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
GRANT UPDATE (display_name, bio, public_metadata) ON public.users TO authenticated;

-- use structed naming for easy maintaining 
CREATE POLICY users_delete_policy ON public.users AS permissive
    FOR DELETE TO authenticated
        USING (id = auth.uid());

-------------------------------------------------------
-- Section - Views
-------------------------------------------------------
-- use VIEWS for get queries instead of tables 
CREATE OR REPLACE VIEW public.v_users WITH ( security_invoker = TRUE
) AS
SELECT
    acc.id,
    acc.account_name,
    obj.name AS avatar_url,
    acc.display_name,
    acc.bio,
    acc.created_at,
    acc.public_metadata
FROM
    public.users acc
    LEFT JOIN storage.objects obj ON acc.avatar_id = obj.id;

-------------------------------------------------------
-- Section - RPC Functions
-------------------------------------------------------
-- use RPC for complext mutations instead of direct write 
CREATE OR REPLACE FUNCTION public.get_user_by_id(user_id uuid)

Again, you are wonderful. I can help on this.