mirror of
https://github.com/AeThex-Corporation/AeThex-OS.git
synced 2026-04-18 06:17:21 +00:00
f8ba1cbb0f
Add the complete SQL schema definition, including 316 tables, to the repository for comprehensive database structure reference. Replit-Commit-Author: Agent Replit-Commit-Session-Id: 279f1558-c0e3-40e4-8217-be7e9f4c6eca Replit-Commit-Checkpoint-Type: full_checkpoint Replit-Commit-Event-Id: 4581eb35-bd44-463a-b452-7a68159bc958 Replit-Commit-Screenshot-Url: https://storage.googleapis.com/screenshot-production-us-central1/b984cb14-1d19-4944-922b-bc79e821ed35/279f1558-c0e3-40e4-8217-be7e9f4c6eca/HbU8yEz Replit-Helium-Checkpoint-Created: true
4291 lines
No EOL
181 KiB
Text
4291 lines
No EOL
181 KiB
Text
-- WARNING: This schema is for context only and is not meant to be run.
|
|
-- Table order and constraints may not be valid for execution.
|
|
|
|
CREATE TABLE public.achievements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
icon text,
|
|
points_reward integer DEFAULT 0,
|
|
badge_color text,
|
|
rarity text,
|
|
xp_reward integer DEFAULT 0,
|
|
category character varying NOT NULL DEFAULT 'milestone'::character varying,
|
|
CONSTRAINT achievements_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.activity_events (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
actor_id uuid NOT NULL,
|
|
verb text NOT NULL,
|
|
object_type text NOT NULL,
|
|
object_id uuid,
|
|
target_id uuid,
|
|
metadata jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT activity_events_pkey PRIMARY KEY (id),
|
|
CONSTRAINT activity_events_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.activity_logs (
|
|
id character varying NOT NULL DEFAULT (gen_random_uuid())::character varying,
|
|
user_id character varying,
|
|
action text NOT NULL,
|
|
details text,
|
|
ip_address text,
|
|
user_agent text,
|
|
status text NOT NULL,
|
|
timestamp timestamp without time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT activity_logs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT activity_logs_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
|
|
);
|
|
CREATE TABLE public.aethex_alerts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid,
|
|
type text NOT NULL,
|
|
severity text NOT NULL CHECK (severity = ANY (ARRAY['critical'::text, 'warning'::text, 'info'::text])),
|
|
message text NOT NULL,
|
|
is_resolved boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
resolved_at timestamp with time zone,
|
|
CONSTRAINT aethex_alerts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_alerts_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.aethex_applications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
creator_id uuid NOT NULL,
|
|
opportunity_id uuid NOT NULL,
|
|
status text DEFAULT 'submitted'::text,
|
|
cover_letter text,
|
|
response_message text,
|
|
applied_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT aethex_applications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_applications_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.aethex_creators(id),
|
|
CONSTRAINT aethex_applications_opportunity_id_fkey FOREIGN KEY (opportunity_id) REFERENCES public.aethex_opportunities(id)
|
|
);
|
|
CREATE TABLE public.aethex_badges (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
icon text,
|
|
color text DEFAULT '#ffffff'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT aethex_badges_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.aethex_creators (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
username text NOT NULL UNIQUE,
|
|
bio text,
|
|
skills ARRAY DEFAULT '{}'::text[],
|
|
avatar_url text,
|
|
experience_level text,
|
|
arm_affiliations ARRAY DEFAULT '{}'::text[],
|
|
primary_arm text,
|
|
is_discoverable boolean DEFAULT true,
|
|
allow_recommendations boolean DEFAULT true,
|
|
devconnect_linked boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT aethex_creators_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_creators_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.aethex_devconnect_links (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
aethex_creator_id uuid NOT NULL,
|
|
devconnect_username text NOT NULL,
|
|
devconnect_profile_url text,
|
|
verified boolean DEFAULT false,
|
|
linked_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT aethex_devconnect_links_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_devconnect_links_aethex_creator_id_fkey FOREIGN KEY (aethex_creator_id) REFERENCES public.aethex_creators(id)
|
|
);
|
|
CREATE TABLE public.aethex_event_registrations (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
event_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
registered_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT aethex_event_registrations_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_event_registrations_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.aethex_events(id),
|
|
CONSTRAINT aethex_event_registrations_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.aethex_events (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
date date NOT NULL,
|
|
time time without time zone NOT NULL,
|
|
location text,
|
|
capacity integer,
|
|
image_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone,
|
|
category text,
|
|
price numeric,
|
|
featured boolean,
|
|
speakers ARRAY,
|
|
agenda jsonb,
|
|
full_description text,
|
|
map_url text,
|
|
ticket_types jsonb,
|
|
CONSTRAINT aethex_events_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_events_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.aethex_opportunities (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
title text NOT NULL,
|
|
description text NOT NULL,
|
|
job_type text NOT NULL,
|
|
salary_min integer,
|
|
salary_max integer,
|
|
experience_level text,
|
|
arm_affiliation text NOT NULL,
|
|
posted_by_id uuid NOT NULL,
|
|
status text DEFAULT 'open'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT aethex_opportunities_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_opportunities_posted_by_id_fkey FOREIGN KEY (posted_by_id) REFERENCES public.aethex_creators(id)
|
|
);
|
|
CREATE TABLE public.aethex_passports (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL UNIQUE,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT aethex_passports_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_passports_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.aethex_projects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
creator_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
url text,
|
|
image_url text,
|
|
tags ARRAY DEFAULT '{}'::text[],
|
|
is_featured boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT aethex_projects_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_projects_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.aethex_creators(id)
|
|
);
|
|
CREATE TABLE public.aethex_sites (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
url text,
|
|
status text,
|
|
uptime numeric,
|
|
response_time integer,
|
|
users integer,
|
|
requests integer,
|
|
last_check timestamp with time zone,
|
|
services ARRAY,
|
|
metrics jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
metrics_history ARRAY,
|
|
owner_id uuid,
|
|
api_key_hash text,
|
|
handshake_token text,
|
|
handshake_token_expires_at timestamp with time zone,
|
|
CONSTRAINT aethex_sites_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_sites_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.aethex_skill_endorsements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
creator_id uuid NOT NULL,
|
|
endorsed_by_id uuid NOT NULL,
|
|
skill text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT aethex_skill_endorsements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT aethex_skill_endorsements_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.aethex_creators(id),
|
|
CONSTRAINT aethex_skill_endorsements_endorsed_by_id_fkey FOREIGN KEY (endorsed_by_id) REFERENCES public.aethex_creators(id)
|
|
);
|
|
CREATE TABLE public.announcements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid,
|
|
author_id uuid,
|
|
title text NOT NULL,
|
|
content text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT announcements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT announcements_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id),
|
|
CONSTRAINT announcements_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.api_keys (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
api_key text NOT NULL UNIQUE,
|
|
name text NOT NULL,
|
|
last_used_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
expires_at timestamp with time zone,
|
|
CONSTRAINT api_keys_pkey PRIMARY KEY (id),
|
|
CONSTRAINT api_keys_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_achievements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name character varying NOT NULL,
|
|
description text,
|
|
category character varying NOT NULL,
|
|
type character varying NOT NULL,
|
|
criteria jsonb NOT NULL,
|
|
rewards jsonb DEFAULT '{}'::jsonb,
|
|
icon_url text,
|
|
rarity character varying DEFAULT 'common'::character varying,
|
|
points integer DEFAULT 0,
|
|
active boolean DEFAULT true,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_achievements_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_admin_logs (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
admin_id uuid NOT NULL,
|
|
action character varying NOT NULL,
|
|
target_type character varying,
|
|
target_id uuid,
|
|
details jsonb DEFAULT '{}'::jsonb,
|
|
ip_address inet,
|
|
user_agent text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_admin_logs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_admin_logs_admin_id_fkey FOREIGN KEY (admin_id) REFERENCES public.app_2db261233c_users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_comments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
post_id uuid,
|
|
content_id uuid,
|
|
parent_id uuid,
|
|
content text NOT NULL,
|
|
likes_count integer DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_comments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id),
|
|
CONSTRAINT app_2db261233c_comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.app_2db261233c_posts(id),
|
|
CONSTRAINT app_2db261233c_comments_content_id_fkey FOREIGN KEY (content_id) REFERENCES public.app_2db261233c_content(id),
|
|
CONSTRAINT app_2db261233c_comments_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES public.app_2db261233c_comments(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_content (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
type character varying NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
content_url text,
|
|
thumbnail_url text,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
category character varying,
|
|
tags ARRAY,
|
|
status character varying DEFAULT 'published'::character varying,
|
|
visibility character varying DEFAULT 'public'::character varying,
|
|
views_count integer DEFAULT 0,
|
|
likes_count integer DEFAULT 0,
|
|
comments_count integer DEFAULT 0,
|
|
shares_count integer DEFAULT 0,
|
|
featured boolean DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_content_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_content_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_follows (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
follower_id uuid NOT NULL,
|
|
following_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_follows_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_follows_follower_id_fkey FOREIGN KEY (follower_id) REFERENCES public.app_2db261233c_users(id),
|
|
CONSTRAINT app_2db261233c_follows_following_id_fkey FOREIGN KEY (following_id) REFERENCES public.app_2db261233c_users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_gaming_stats (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL UNIQUE,
|
|
games_played integer DEFAULT 0,
|
|
total_playtime integer DEFAULT 0,
|
|
worlds_created integer DEFAULT 0,
|
|
tournaments_won integer DEFAULT 0,
|
|
current_streak integer DEFAULT 0,
|
|
best_streak integer DEFAULT 0,
|
|
favorite_games ARRAY,
|
|
stats jsonb DEFAULT '{}'::jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_gaming_stats_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_gaming_stats_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_likes (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
post_id uuid,
|
|
content_id uuid,
|
|
comment_id uuid,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_likes_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_likes_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id),
|
|
CONSTRAINT app_2db261233c_likes_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.app_2db261233c_posts(id),
|
|
CONSTRAINT app_2db261233c_likes_content_id_fkey FOREIGN KEY (content_id) REFERENCES public.app_2db261233c_content(id),
|
|
CONSTRAINT app_2db261233c_likes_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES public.app_2db261233c_comments(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_notifications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
type character varying NOT NULL,
|
|
title text NOT NULL,
|
|
message text,
|
|
data jsonb DEFAULT '{}'::jsonb,
|
|
read boolean DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_notifications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_posts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
content text NOT NULL,
|
|
media_urls ARRAY,
|
|
type character varying DEFAULT 'social'::character varying,
|
|
visibility character varying DEFAULT 'public'::character varying,
|
|
likes_count integer DEFAULT 0,
|
|
comments_count integer DEFAULT 0,
|
|
shares_count integer DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_posts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_posts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_professional_profiles (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL UNIQUE,
|
|
job_title text,
|
|
company text,
|
|
industry text,
|
|
skills ARRAY,
|
|
experience_years integer,
|
|
education jsonb DEFAULT '[]'::jsonb,
|
|
certifications jsonb DEFAULT '[]'::jsonb,
|
|
portfolio_url text,
|
|
linkedin_url text,
|
|
github_url text,
|
|
resume_url text,
|
|
availability character varying DEFAULT 'not_looking'::character varying,
|
|
salary_range text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_professional_profiles_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_professional_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_user_achievements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
achievement_id uuid NOT NULL,
|
|
progress integer DEFAULT 0,
|
|
completed boolean DEFAULT false,
|
|
completed_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_user_achievements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_user_achievements_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.app_2db261233c_users(id),
|
|
CONSTRAINT app_2db261233c_user_achievements_achievement_id_fkey FOREIGN KEY (achievement_id) REFERENCES public.app_2db261233c_achievements(id)
|
|
);
|
|
CREATE TABLE public.app_2db261233c_users (
|
|
id uuid NOT NULL,
|
|
username character varying NOT NULL UNIQUE,
|
|
full_name text,
|
|
avatar_url text,
|
|
bio text,
|
|
location text,
|
|
website text,
|
|
gaming_level integer DEFAULT 1,
|
|
gaming_xp integer DEFAULT 0,
|
|
social_score integer DEFAULT 0,
|
|
professional_tier character varying DEFAULT 'free'::character varying,
|
|
total_uploads integer DEFAULT 0,
|
|
total_achievements integer DEFAULT 0,
|
|
status character varying DEFAULT 'active'::character varying,
|
|
privacy_settings jsonb DEFAULT '{"gaming": "public", "profile": "public", "professional": "public"}'::jsonb,
|
|
notification_settings jsonb DEFAULT '{"push": true, "email": true, "in_app": true}'::jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT app_2db261233c_users_pkey PRIMARY KEY (id),
|
|
CONSTRAINT app_2db261233c_users_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.applications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
type text NOT NULL CHECK (type = ANY (ARRAY['contributor'::text, 'career'::text])),
|
|
full_name text NOT NULL,
|
|
email text NOT NULL,
|
|
location text,
|
|
role_interest text,
|
|
primary_skill text,
|
|
experience_level text,
|
|
availability text,
|
|
portfolio_url text,
|
|
resume_url text,
|
|
interests ARRAY,
|
|
message text,
|
|
status text NOT NULL DEFAULT 'new'::text,
|
|
submitted_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT applications_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.architect_waitlist (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid UNIQUE,
|
|
email text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT architect_waitlist_pkey PRIMARY KEY (id),
|
|
CONSTRAINT architect_waitlist_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.arm_follows (
|
|
id bigint NOT NULL DEFAULT nextval('arm_follows_id_seq'::regclass),
|
|
user_id uuid NOT NULL,
|
|
arm_affiliation text NOT NULL,
|
|
followed_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT arm_follows_pkey PRIMARY KEY (id),
|
|
CONSTRAINT arm_follows_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.assets (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid,
|
|
user_id uuid,
|
|
name text NOT NULL,
|
|
file_type text,
|
|
size bigint,
|
|
url text NOT NULL,
|
|
tags ARRAY,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
site_id uuid,
|
|
CONSTRAINT assets_pkey PRIMARY KEY (id),
|
|
CONSTRAINT assets_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id),
|
|
CONSTRAINT assets_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT assets_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.audit_logs (
|
|
id integer NOT NULL DEFAULT nextval('audit_logs_id_seq'::regclass),
|
|
guild_id text NOT NULL,
|
|
action text NOT NULL,
|
|
user_id text NOT NULL,
|
|
user_tag text,
|
|
details jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT audit_logs_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.auth_logs (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid,
|
|
event_type text NOT NULL,
|
|
ip_address inet,
|
|
user_agent text,
|
|
metadata jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT auth_logs_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.auth_nonces (
|
|
id bigint NOT NULL DEFAULT nextval('auth_nonces_id_seq'::regclass),
|
|
wallet_address text NOT NULL,
|
|
nonce text NOT NULL UNIQUE,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
expires_at timestamp with time zone NOT NULL,
|
|
used boolean DEFAULT false,
|
|
ip_address inet,
|
|
CONSTRAINT auth_nonces_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.automod_config (
|
|
guild_id text NOT NULL,
|
|
links_enabled boolean DEFAULT false,
|
|
links_action text DEFAULT 'delete'::text,
|
|
spam_enabled boolean DEFAULT false,
|
|
spam_threshold integer DEFAULT 5,
|
|
badwords jsonb DEFAULT '[]'::jsonb,
|
|
invites_enabled boolean DEFAULT false,
|
|
mentions_enabled boolean DEFAULT false,
|
|
mentions_limit integer DEFAULT 5,
|
|
exempt_roles jsonb DEFAULT '[]'::jsonb,
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT automod_config_pkey PRIMARY KEY (guild_id)
|
|
);
|
|
CREATE TABLE public.blockchain_transactions (
|
|
id bigint NOT NULL DEFAULT nextval('blockchain_transactions_id_seq'::regclass),
|
|
transaction_hash text NOT NULL UNIQUE,
|
|
block_number bigint NOT NULL,
|
|
chain_id integer NOT NULL,
|
|
from_address text NOT NULL,
|
|
to_address text,
|
|
value numeric DEFAULT 0,
|
|
value_currency text DEFAULT 'ETH'::text,
|
|
gas_used numeric,
|
|
gas_price numeric,
|
|
transaction_type text,
|
|
status text DEFAULT 'confirmed'::text,
|
|
timestamp timestamp with time zone NOT NULL,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT blockchain_transactions_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.blog_categories (
|
|
id text NOT NULL,
|
|
name text NOT NULL,
|
|
slug text NOT NULL UNIQUE,
|
|
description text,
|
|
theme text NOT NULL,
|
|
border_color text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT blog_categories_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.blog_comments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
parent_comment_id uuid,
|
|
content text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT blog_comments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT blog_comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.blog_posts(id),
|
|
CONSTRAINT blog_comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT blog_comments_parent_comment_id_fkey FOREIGN KEY (parent_comment_id) REFERENCES public.blog_comments(id)
|
|
);
|
|
CREATE TABLE public.blog_post_likes (
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT blog_post_likes_pkey PRIMARY KEY (post_id, user_id),
|
|
CONSTRAINT blog_post_likes_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.blog_posts(id),
|
|
CONSTRAINT blog_post_likes_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.blog_posts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
slug text NOT NULL UNIQUE,
|
|
title text NOT NULL,
|
|
content text,
|
|
author_id uuid,
|
|
image_url text,
|
|
tags ARRAY,
|
|
status text DEFAULT 'draft'::text,
|
|
published_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
image_prompt text,
|
|
excerpt text,
|
|
author text,
|
|
date text,
|
|
read_time text,
|
|
category text,
|
|
image text,
|
|
likes integer DEFAULT 0,
|
|
comments integer DEFAULT 0,
|
|
body_html text,
|
|
CONSTRAINT blog_posts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT blog_posts_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.books (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
category text,
|
|
release_date text,
|
|
rating numeric,
|
|
is_free boolean DEFAULT false,
|
|
long_description text,
|
|
tags ARRAY,
|
|
image_prompt text,
|
|
image_url text,
|
|
purchase_links jsonb,
|
|
content text,
|
|
display_order smallint,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
description text,
|
|
CONSTRAINT books_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.bounties (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
bounty_id text NOT NULL UNIQUE,
|
|
title text NOT NULL,
|
|
description text NOT NULL,
|
|
reward_usd numeric NOT NULL CHECK (reward_usd IS NULL OR reward_usd >= 0::numeric),
|
|
xp_reward integer DEFAULT 0 CHECK (xp_reward >= 0),
|
|
difficulty text DEFAULT 'intermediate'::text CHECK (difficulty = ANY (ARRAY['beginner'::text, 'intermediate'::text, 'advanced'::text, 'expert'::text])),
|
|
skills ARRAY DEFAULT '{}'::text[],
|
|
applicant_count integer DEFAULT 0 CHECK (applicant_count >= 0),
|
|
time_estimate text,
|
|
posted_by uuid,
|
|
project text,
|
|
status text DEFAULT 'open'::text CHECK (status = ANY (ARRAY['open'::text, 'in_progress'::text, 'completed'::text, 'cancelled'::text])),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
claimed_by uuid,
|
|
claimed_at timestamp with time zone,
|
|
submission_url text,
|
|
submission_notes text,
|
|
submitted_at timestamp with time zone,
|
|
CONSTRAINT bounties_pkey PRIMARY KEY (id),
|
|
CONSTRAINT bounties_posted_by_fkey FOREIGN KEY (posted_by) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT bounties_claimed_by_fkey FOREIGN KEY (claimed_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.bounty_applications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
bounty_id uuid,
|
|
user_id uuid,
|
|
message text NOT NULL,
|
|
status text DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'accepted'::text, 'rejected'::text])),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT bounty_applications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT bounty_applications_bounty_id_fkey FOREIGN KEY (bounty_id) REFERENCES public.bounties(id),
|
|
CONSTRAINT bounty_applications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.bounty_claims (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
grid_member_id uuid NOT NULL,
|
|
bounty_id uuid NOT NULL,
|
|
status character varying NOT NULL DEFAULT 'claimed'::character varying CHECK (status::text = ANY (ARRAY['claimed'::character varying, 'submitted'::character varying, 'approved'::character varying, 'rejected'::character varying]::text[])),
|
|
claimed_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
submitted_at timestamp with time zone,
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT bounty_claims_pkey PRIMARY KEY (id),
|
|
CONSTRAINT bounty_claims_grid_member_id_fkey FOREIGN KEY (grid_member_id) REFERENCES public.grid_members(id),
|
|
CONSTRAINT bounty_claims_bounty_id_fkey FOREIGN KEY (bounty_id) REFERENCES public.bounties(id)
|
|
);
|
|
CREATE TABLE public.changelogs (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
version_date date NOT NULL,
|
|
title text NOT NULL,
|
|
changes jsonb NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
site_name text,
|
|
site_id uuid,
|
|
tags jsonb,
|
|
CONSTRAINT changelogs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT changelogs_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.collaboration_comments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
content text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT collaboration_comments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT collaboration_comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.collaboration_posts(id),
|
|
CONSTRAINT collaboration_comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.collaboration_post_likes (
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT collaboration_post_likes_pkey PRIMARY KEY (post_id, user_id),
|
|
CONSTRAINT collaboration_post_likes_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.collaboration_posts(id),
|
|
CONSTRAINT collaboration_post_likes_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.collaboration_posts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
title text NOT NULL,
|
|
content text NOT NULL,
|
|
arm_affiliation text NOT NULL DEFAULT 'labs'::text,
|
|
created_by uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
is_published boolean DEFAULT true,
|
|
likes_count integer DEFAULT 0,
|
|
comments_count integer DEFAULT 0,
|
|
tags ARRAY DEFAULT ARRAY[]::text[],
|
|
category text,
|
|
CONSTRAINT collaboration_posts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT collaboration_posts_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.collaboration_posts_authors (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
collaboration_post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL DEFAULT 'contributor'::text,
|
|
joined_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT collaboration_posts_authors_pkey PRIMARY KEY (id),
|
|
CONSTRAINT collaboration_posts_authors_collaboration_post_id_fkey FOREIGN KEY (collaboration_post_id) REFERENCES public.collaboration_posts(id),
|
|
CONSTRAINT collaboration_posts_authors_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.command_cooldowns (
|
|
id integer NOT NULL DEFAULT nextval('command_cooldowns_id_seq'::regclass),
|
|
guild_id character varying NOT NULL,
|
|
user_id character varying NOT NULL,
|
|
command character varying NOT NULL,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
CONSTRAINT command_cooldowns_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.command_logs (
|
|
id integer NOT NULL DEFAULT nextval('command_logs_id_seq'::regclass),
|
|
guild_id character varying NOT NULL,
|
|
user_id character varying NOT NULL,
|
|
command character varying NOT NULL,
|
|
options jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT command_logs_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.comments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
parent_comment_id uuid,
|
|
content text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT comments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.posts(id),
|
|
CONSTRAINT comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT comments_parent_comment_id_fkey FOREIGN KEY (parent_comment_id) REFERENCES public.comments(id)
|
|
);
|
|
CREATE TABLE public.community_comments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
content text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT community_comments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT community_comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT community_comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.community_posts(id)
|
|
);
|
|
CREATE TABLE public.community_likes (
|
|
id integer NOT NULL DEFAULT nextval('community_likes_id_seq'::regclass),
|
|
post_id uuid NOT NULL,
|
|
liker_id uuid NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT community_likes_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.community_notifications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
actor_id uuid NOT NULL,
|
|
post_id uuid,
|
|
collaboration_post_id uuid,
|
|
notification_type text NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
read boolean DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT community_notifications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT community_notifications_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT community_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT community_notifications_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.community_posts(id),
|
|
CONSTRAINT community_notifications_collaboration_post_id_fkey FOREIGN KEY (collaboration_post_id) REFERENCES public.collaboration_posts(id)
|
|
);
|
|
CREATE TABLE public.community_post_likes (
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT community_post_likes_pkey PRIMARY KEY (post_id, user_id),
|
|
CONSTRAINT community_post_likes_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.community_posts(id),
|
|
CONSTRAINT community_post_likes_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.community_posts (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
author_id uuid,
|
|
title text NOT NULL,
|
|
content text NOT NULL,
|
|
category text,
|
|
tags ARRAY,
|
|
likes_count integer DEFAULT 0,
|
|
comments_count integer DEFAULT 0,
|
|
is_published boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
arm_affiliation text DEFAULT 'labs'::text CHECK (arm_affiliation = ANY (ARRAY['labs'::text, 'gameforge'::text, 'corp'::text, 'foundation'::text, 'devlink'::text, 'nexus'::text, 'staff'::text])),
|
|
CONSTRAINT community_posts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT community_posts_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.company_documents (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
file_url text,
|
|
category text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT company_documents_pkey PRIMARY KEY (id),
|
|
CONSTRAINT company_documents_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.connections (
|
|
id text NOT NULL DEFAULT (gen_random_uuid())::text,
|
|
from text NOT NULL,
|
|
to text NOT NULL,
|
|
directed boolean DEFAULT true,
|
|
status text CHECK (status = ANY (ARRAY['healthy'::text, 'warning'::text, 'error'::text])),
|
|
bandwidth numeric,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT connections_pkey PRIMARY KEY (id),
|
|
CONSTRAINT connections_from_fkey FOREIGN KEY (from) REFERENCES public.nodes(id),
|
|
CONSTRAINT connections_to_fkey FOREIGN KEY (to) REFERENCES public.nodes(id)
|
|
);
|
|
CREATE TABLE public.consultancy_services (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
title text NOT NULL UNIQUE,
|
|
price numeric NOT NULL,
|
|
price_description text,
|
|
description text,
|
|
features ARRAY NOT NULL,
|
|
is_featured boolean NOT NULL DEFAULT false,
|
|
display_order integer,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
category text,
|
|
long_description text,
|
|
CONSTRAINT consultancy_services_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.contact_submissions (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL,
|
|
email text NOT NULL,
|
|
subject text,
|
|
message text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT contact_submissions_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.contracts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
status text,
|
|
document_url text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT contracts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT contracts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT contracts_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.conversations (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
participant_ids ARRAY NOT NULL,
|
|
last_message_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT conversations_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.corp_activity_log (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
company_id uuid NOT NULL,
|
|
actor_id uuid NOT NULL,
|
|
action text NOT NULL,
|
|
resource_type text,
|
|
resource_id uuid,
|
|
metadata jsonb,
|
|
ip_address text,
|
|
user_agent text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_activity_log_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_activity_log_company_id_fkey FOREIGN KEY (company_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT corp_activity_log_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.corp_contract_milestones (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
contract_id uuid NOT NULL,
|
|
milestone_name text NOT NULL,
|
|
description text,
|
|
due_date date,
|
|
deliverables text,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'in_progress'::text, 'completed'::text, 'blocked'::text])),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_contract_milestones_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_contract_milestones_contract_id_fkey FOREIGN KEY (contract_id) REFERENCES public.corp_contracts(id)
|
|
);
|
|
CREATE TABLE public.corp_contracts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
client_company_id uuid NOT NULL,
|
|
vendor_id uuid NOT NULL,
|
|
contract_name text NOT NULL,
|
|
contract_type text NOT NULL CHECK (contract_type = ANY (ARRAY['service'::text, 'retainer'::text, 'license'::text, 'nda'::text, 'other'::text])),
|
|
description text,
|
|
start_date date,
|
|
end_date date,
|
|
contract_value numeric,
|
|
status text NOT NULL DEFAULT 'draft'::text CHECK (status = ANY (ARRAY['draft'::text, 'pending_approval'::text, 'signed'::text, 'active'::text, 'completed'::text, 'terminated'::text, 'archived'::text])),
|
|
document_url text,
|
|
signed_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_contracts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_contracts_client_company_id_fkey FOREIGN KEY (client_company_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT corp_contracts_vendor_id_fkey FOREIGN KEY (vendor_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.corp_financial_summary (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
company_id uuid NOT NULL UNIQUE,
|
|
period_start date NOT NULL,
|
|
period_end date NOT NULL,
|
|
total_invoiced numeric DEFAULT 0,
|
|
total_paid numeric DEFAULT 0,
|
|
total_overdue numeric DEFAULT 0,
|
|
active_contracts integer DEFAULT 0,
|
|
completed_contracts integer DEFAULT 0,
|
|
total_contract_value numeric DEFAULT 0,
|
|
average_payment_days integer,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_financial_summary_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_financial_summary_company_id_fkey FOREIGN KEY (company_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.corp_invoice_items (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
invoice_id uuid NOT NULL,
|
|
description text NOT NULL,
|
|
quantity numeric NOT NULL DEFAULT 1,
|
|
unit_price numeric NOT NULL,
|
|
amount numeric NOT NULL,
|
|
category text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_invoice_items_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_invoice_items_invoice_id_fkey FOREIGN KEY (invoice_id) REFERENCES public.corp_invoices(id)
|
|
);
|
|
CREATE TABLE public.corp_invoice_payments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
invoice_id uuid NOT NULL,
|
|
amount_paid numeric NOT NULL,
|
|
payment_date date NOT NULL DEFAULT now(),
|
|
payment_method text NOT NULL DEFAULT 'bank_transfer'::text,
|
|
reference_number text,
|
|
notes text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_invoice_payments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_invoice_payments_invoice_id_fkey FOREIGN KEY (invoice_id) REFERENCES public.corp_invoices(id)
|
|
);
|
|
CREATE TABLE public.corp_invoices (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
client_company_id uuid NOT NULL,
|
|
invoice_number text NOT NULL UNIQUE,
|
|
project_id uuid,
|
|
description text,
|
|
issue_date date NOT NULL DEFAULT now(),
|
|
due_date date NOT NULL,
|
|
amount_due numeric NOT NULL,
|
|
amount_paid numeric NOT NULL DEFAULT 0,
|
|
status text NOT NULL DEFAULT 'draft'::text CHECK (status = ANY (ARRAY['draft'::text, 'sent'::text, 'viewed'::text, 'paid'::text, 'overdue'::text, 'cancelled'::text])),
|
|
currency text NOT NULL DEFAULT 'USD'::text,
|
|
notes text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_invoices_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_invoices_client_company_id_fkey FOREIGN KEY (client_company_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.corp_projects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
client_company_id uuid NOT NULL,
|
|
project_name text NOT NULL,
|
|
description text,
|
|
status text NOT NULL DEFAULT 'active'::text CHECK (status = ANY (ARRAY['planning'::text, 'active'::text, 'paused'::text, 'completed'::text, 'archived'::text])),
|
|
budget numeric,
|
|
spent numeric DEFAULT 0,
|
|
start_date date,
|
|
end_date date,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_projects_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_projects_client_company_id_fkey FOREIGN KEY (client_company_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.corp_team_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
company_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL CHECK (role = ANY (ARRAY['owner'::text, 'admin'::text, 'member'::text, 'viewer'::text])),
|
|
email text NOT NULL,
|
|
full_name text,
|
|
job_title text,
|
|
status text NOT NULL DEFAULT 'active'::text CHECK (status = ANY (ARRAY['active'::text, 'inactive'::text, 'pending_invite'::text])),
|
|
invited_at timestamp with time zone,
|
|
joined_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corp_team_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corp_team_members_company_id_fkey FOREIGN KEY (company_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT corp_team_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.corporate_career_applications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
job_title text NOT NULL,
|
|
full_name text NOT NULL,
|
|
email text NOT NULL,
|
|
portfolio_url text,
|
|
cover_letter text,
|
|
submitted_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corporate_career_applications_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.corporate_career_interest (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
full_name text NOT NULL,
|
|
email text NOT NULL,
|
|
area_of_interest text,
|
|
linkedin_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corporate_career_interest_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corporate_career_interest_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.corporate_investor_docs (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
file_url text NOT NULL,
|
|
document_type text,
|
|
published_date date,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT corporate_investor_docs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corporate_investor_docs_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.corporate_job_postings (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
department text,
|
|
location text,
|
|
job_type text,
|
|
description text NOT NULL,
|
|
is_active boolean DEFAULT true,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT corporate_job_postings_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corporate_job_postings_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.corporate_leadership (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
profile_id uuid UNIQUE,
|
|
name text NOT NULL,
|
|
title text NOT NULL,
|
|
bio text,
|
|
image_url text,
|
|
social_links jsonb,
|
|
display_order integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT corporate_leadership_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corporate_leadership_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id),
|
|
CONSTRAINT corporate_leadership_profile_id_fkey FOREIGN KEY (profile_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.corporate_news (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
slug text NOT NULL UNIQUE,
|
|
title text NOT NULL,
|
|
excerpt text,
|
|
content text NOT NULL,
|
|
image_url text,
|
|
author_id uuid,
|
|
published_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
tags ARRAY,
|
|
CONSTRAINT corporate_news_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corporate_news_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id),
|
|
CONSTRAINT corporate_news_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.corporate_whitepapers (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
file_url text NOT NULL,
|
|
image_url text,
|
|
published_date date,
|
|
tags ARRAY,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT corporate_whitepapers_pkey PRIMARY KEY (id),
|
|
CONSTRAINT corporate_whitepapers_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.creations (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
category text,
|
|
description text,
|
|
tags ARRAY,
|
|
image_prompt text,
|
|
image_url text,
|
|
links jsonb,
|
|
display_order smallint,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT creations_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.creator_assets (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
experience_id uuid,
|
|
name text NOT NULL,
|
|
description text,
|
|
type text NOT NULL,
|
|
file_url text NOT NULL,
|
|
thumbnail_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT creator_assets_pkey PRIMARY KEY (id),
|
|
CONSTRAINT creator_assets_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT creator_assets_experience_id_fkey FOREIGN KEY (experience_id) REFERENCES public.experiences(id)
|
|
);
|
|
CREATE TABLE public.creator_dashboard (
|
|
user_id uuid NOT NULL,
|
|
total_projects integer DEFAULT 0,
|
|
total_assets integer DEFAULT 0,
|
|
total_experiences integer DEFAULT 0,
|
|
last_synced_at timestamp with time zone,
|
|
CONSTRAINT creator_dashboard_pkey PRIMARY KEY (user_id),
|
|
CONSTRAINT creator_dashboard_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.customers (
|
|
id uuid NOT NULL,
|
|
stripe_customer_id text,
|
|
CONSTRAINT customers_pkey PRIMARY KEY (id),
|
|
CONSTRAINT customers_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.defi_positions (
|
|
id bigint NOT NULL DEFAULT nextval('defi_positions_id_seq'::regclass),
|
|
user_id uuid NOT NULL,
|
|
wallet_address text NOT NULL,
|
|
chain_id integer NOT NULL,
|
|
protocol_name text NOT NULL,
|
|
position_type text NOT NULL,
|
|
token_address text,
|
|
token_symbol text,
|
|
amount numeric NOT NULL DEFAULT 0,
|
|
amount_usd numeric DEFAULT 0,
|
|
apy numeric DEFAULT 0,
|
|
rewards_earned numeric DEFAULT 0,
|
|
rewards_usd numeric DEFAULT 0,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
last_synced_at timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT defi_positions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT defi_positions_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.designer_waitlist (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid UNIQUE,
|
|
email text NOT NULL UNIQUE,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT designer_waitlist_pkey PRIMARY KEY (id),
|
|
CONSTRAINT designer_waitlist_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.developer_waitlist (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid UNIQUE,
|
|
email text NOT NULL UNIQUE,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT developer_waitlist_pkey PRIMARY KEY (id),
|
|
CONSTRAINT developer_waitlist_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.devhub_collaboration_sessions (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid,
|
|
user_id uuid,
|
|
status text DEFAULT 'active'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
ended_at timestamp with time zone,
|
|
site_id uuid,
|
|
CONSTRAINT devhub_collaboration_sessions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devhub_collaboration_sessions_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id),
|
|
CONSTRAINT devhub_collaboration_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT devhub_collaboration_sessions_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.devlink_achievements (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
achievement_type text NOT NULL,
|
|
title text,
|
|
description text,
|
|
xp_earned integer DEFAULT 0,
|
|
badge_url text,
|
|
unlocked_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_achievements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_achievements_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_applications (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
job_id uuid,
|
|
applicant_id uuid,
|
|
cover_letter text,
|
|
proposed_rate text,
|
|
estimated_timeline text,
|
|
availability_start_date timestamp with time zone,
|
|
portfolio_links ARRAY DEFAULT '{}'::text[],
|
|
status text DEFAULT 'pending'::text,
|
|
reviewed boolean DEFAULT false,
|
|
review_text text,
|
|
rating integer,
|
|
categories jsonb DEFAULT '{}'::jsonb,
|
|
would_work_again boolean,
|
|
company_response text,
|
|
company_response_date timestamp with time zone,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_applications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_applications_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.devlink_jobs(id),
|
|
CONSTRAINT devlink_applications_applicant_id_fkey FOREIGN KEY (applicant_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_certifications (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
skill_name text NOT NULL,
|
|
certification_level text,
|
|
score integer,
|
|
percentile integer,
|
|
certificate_url text,
|
|
verification_code text UNIQUE,
|
|
issued_date timestamp with time zone DEFAULT now(),
|
|
expiry_date timestamp with time zone,
|
|
status text DEFAULT 'active'::text,
|
|
verified boolean DEFAULT false,
|
|
CONSTRAINT devlink_certifications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_certifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_collab_messages (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
room_id uuid,
|
|
sender_id uuid,
|
|
message_type text DEFAULT 'text'::text,
|
|
content text,
|
|
file_url text,
|
|
attachments jsonb DEFAULT '[]'::jsonb,
|
|
read boolean DEFAULT false,
|
|
read_at timestamp with time zone,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_collab_messages_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_collab_messages_room_id_fkey FOREIGN KEY (room_id) REFERENCES public.devlink_collab_rooms(id),
|
|
CONSTRAINT devlink_collab_messages_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_collab_rooms (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
room_name text NOT NULL,
|
|
room_type text DEFAULT 'general'::text,
|
|
creator_id uuid,
|
|
participant_ids ARRAY DEFAULT '{}'::uuid[],
|
|
related_job_id uuid,
|
|
status text DEFAULT 'active'::text,
|
|
started_at timestamp with time zone DEFAULT now(),
|
|
code_snippets jsonb DEFAULT '[]'::jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_collab_rooms_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_collab_rooms_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.devlink_profiles(id),
|
|
CONSTRAINT devlink_collab_rooms_related_job_id_fkey FOREIGN KEY (related_job_id) REFERENCES public.devlink_jobs(id)
|
|
);
|
|
CREATE TABLE public.devlink_collab_tasks (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
room_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
priority text DEFAULT 'medium'::text,
|
|
due_date timestamp with time zone,
|
|
estimated_hours integer,
|
|
created_by uuid,
|
|
assigned_to uuid,
|
|
status text DEFAULT 'pending'::text,
|
|
blocked_by ARRAY,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_collab_tasks_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_collab_tasks_room_id_fkey FOREIGN KEY (room_id) REFERENCES public.devlink_collab_rooms(id),
|
|
CONSTRAINT devlink_collab_tasks_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.devlink_profiles(id),
|
|
CONSTRAINT devlink_collab_tasks_assigned_to_fkey FOREIGN KEY (assigned_to) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_company_profiles (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
company_name text NOT NULL,
|
|
tagline text,
|
|
description text,
|
|
industry text,
|
|
company_size text,
|
|
founded_year integer,
|
|
location text,
|
|
website_url text,
|
|
logo_url text,
|
|
banner_url text,
|
|
brand_colors jsonb DEFAULT '{}'::jsonb,
|
|
social_links jsonb DEFAULT '{}'::jsonb,
|
|
culture_values ARRAY DEFAULT '{}'::text[],
|
|
perks_benefits ARRAY DEFAULT '{}'::text[],
|
|
tech_stack ARRAY DEFAULT '{}'::text[],
|
|
current_projects jsonb DEFAULT '[]'::jsonb,
|
|
verified boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_company_profiles_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_company_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_daily_challenges (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
date date DEFAULT CURRENT_DATE,
|
|
challenge_type text,
|
|
title text,
|
|
description text,
|
|
xp_reward integer DEFAULT 0,
|
|
target_count integer DEFAULT 1,
|
|
current_progress integer DEFAULT 0,
|
|
completed boolean DEFAULT false,
|
|
completed_at timestamp with time zone,
|
|
streak_bonus integer DEFAULT 0,
|
|
bonus_xp integer DEFAULT 0,
|
|
CONSTRAINT devlink_daily_challenges_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_daily_challenges_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_endorsements (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
endorsee_id uuid,
|
|
endorser_id uuid,
|
|
skill_name text NOT NULL,
|
|
relationship text,
|
|
endorsement_strength integer DEFAULT 1,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_endorsements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_endorsements_endorsee_id_fkey FOREIGN KEY (endorsee_id) REFERENCES public.devlink_profiles(id),
|
|
CONSTRAINT devlink_endorsements_endorser_id_fkey FOREIGN KEY (endorser_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_escrow_transactions (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
job_id uuid,
|
|
developer_id uuid,
|
|
employer_id uuid,
|
|
total_amount numeric,
|
|
funded_amount numeric DEFAULT 0,
|
|
released_amount numeric DEFAULT 0,
|
|
currency text DEFAULT 'USD'::text,
|
|
status text DEFAULT 'pending'::text,
|
|
funded_date timestamp with time zone,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_escrow_transactions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_escrow_transactions_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.devlink_jobs(id),
|
|
CONSTRAINT devlink_escrow_transactions_developer_id_fkey FOREIGN KEY (developer_id) REFERENCES public.devlink_profiles(id),
|
|
CONSTRAINT devlink_escrow_transactions_employer_id_fkey FOREIGN KEY (employer_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_forum_posts (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
author_id uuid,
|
|
title text NOT NULL,
|
|
content text,
|
|
category text,
|
|
tags ARRAY DEFAULT '{}'::text[],
|
|
upvotes integer DEFAULT 0,
|
|
downvotes integer DEFAULT 0,
|
|
view_count integer DEFAULT 0,
|
|
reply_count integer DEFAULT 0,
|
|
pinned boolean DEFAULT false,
|
|
locked boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_forum_posts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_forum_posts_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_forum_replies (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
post_id uuid,
|
|
author_id uuid,
|
|
content text NOT NULL,
|
|
upvotes integer DEFAULT 0,
|
|
downvotes integer DEFAULT 0,
|
|
is_solution boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_forum_replies_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_forum_replies_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.devlink_forum_posts(id),
|
|
CONSTRAINT devlink_forum_replies_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_jobs (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
title text NOT NULL,
|
|
description text,
|
|
required_roles ARRAY DEFAULT '{}'::text[],
|
|
required_skills ARRAY DEFAULT '{}'::text[],
|
|
experience_level text,
|
|
programming_languages ARRAY DEFAULT '{}'::text[],
|
|
frameworks ARRAY DEFAULT '{}'::text[],
|
|
payment_type text DEFAULT 'fixed'::text,
|
|
budget_range jsonb DEFAULT '{}'::jsonb,
|
|
salary_min numeric,
|
|
salary_max numeric,
|
|
timeline text,
|
|
remote_type text DEFAULT 'remote'::text,
|
|
preferred_locations ARRAY DEFAULT '{}'::text[],
|
|
preferred_timezones ARRAY DEFAULT '{}'::text[],
|
|
company_size text,
|
|
status text DEFAULT 'open'::text,
|
|
application_deadline timestamp with time zone,
|
|
application_count integer DEFAULT 0,
|
|
view_count integer DEFAULT 0,
|
|
employer_id uuid,
|
|
company_name text,
|
|
company_logo text,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_jobs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_jobs_employer_id_fkey FOREIGN KEY (employer_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_learning_paths (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
path_name text NOT NULL,
|
|
target_role text,
|
|
current_level text,
|
|
target_level text,
|
|
skill_gaps jsonb DEFAULT '[]'::jsonb,
|
|
recommended_resources jsonb DEFAULT '[]'::jsonb,
|
|
suggested_projects jsonb DEFAULT '[]'::jsonb,
|
|
milestones jsonb DEFAULT '[]'::jsonb,
|
|
estimated_duration_weeks integer,
|
|
progress_percentage integer DEFAULT 0,
|
|
ai_recommendations jsonb DEFAULT '{}'::jsonb,
|
|
last_updated timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_learning_paths_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_learning_paths_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_messages (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
sender_id uuid,
|
|
receiver_id uuid,
|
|
content text NOT NULL,
|
|
read boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_messages_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_messages_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES public.devlink_profiles(id),
|
|
CONSTRAINT devlink_messages_receiver_id_fkey FOREIGN KEY (receiver_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_milestones (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
job_id uuid,
|
|
escrow_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
amount numeric,
|
|
status text DEFAULT 'pending'::text,
|
|
submission_notes text,
|
|
submitted_date timestamp with time zone,
|
|
approved_date timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_milestones_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_milestones_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.devlink_jobs(id),
|
|
CONSTRAINT devlink_milestones_escrow_id_fkey FOREIGN KEY (escrow_id) REFERENCES public.devlink_escrow_transactions(id)
|
|
);
|
|
CREATE TABLE public.devlink_notifications (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
type text DEFAULT 'info'::text,
|
|
title text NOT NULL,
|
|
message text,
|
|
link text,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
read boolean DEFAULT false,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_notifications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_portfolios (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
project_type text,
|
|
technologies ARRAY DEFAULT '{}'::text[],
|
|
images ARRAY DEFAULT '{}'::text[],
|
|
video_url text,
|
|
live_url text,
|
|
github_url text,
|
|
roblox_game_id text,
|
|
featured boolean DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_portfolios_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_portfolios_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_profiles (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
email text UNIQUE,
|
|
full_name text,
|
|
avatar_url text,
|
|
roblox_avatar_url text,
|
|
banner_url text,
|
|
bio text,
|
|
developer_roles ARRAY DEFAULT '{}'::text[],
|
|
skills ARRAY DEFAULT '{}'::text[],
|
|
experience_level text,
|
|
years_of_experience integer DEFAULT 0,
|
|
portfolio_links ARRAY DEFAULT '{}'::text[],
|
|
work_status text DEFAULT 'available'::text,
|
|
payment_preferences jsonb DEFAULT '{}'::jsonb,
|
|
hourly_rate numeric,
|
|
roblox_username text,
|
|
roblox_user_id text,
|
|
roblox_verified boolean DEFAULT false,
|
|
roblox_data jsonb DEFAULT '{}'::jsonb,
|
|
roblox_reputation_score integer DEFAULT 0,
|
|
roblox_reputation_tier text,
|
|
use_roblox_display_name boolean DEFAULT false,
|
|
use_roblox_avatar boolean DEFAULT false,
|
|
github_username text,
|
|
github_verified boolean DEFAULT false,
|
|
devforum_username text,
|
|
devforum_verified boolean DEFAULT false,
|
|
forum_reputation integer DEFAULT 0,
|
|
xp_points integer DEFAULT 0,
|
|
level integer DEFAULT 1,
|
|
community_points integer DEFAULT 0,
|
|
streak_days integer DEFAULT 0,
|
|
user_type text DEFAULT 'developer'::text,
|
|
company_name text,
|
|
company_id uuid,
|
|
platform_roles ARRAY DEFAULT '{}'::text[],
|
|
avatar_customization jsonb DEFAULT '{}'::jsonb,
|
|
admin boolean DEFAULT false,
|
|
is_premium boolean DEFAULT false,
|
|
stripe_account_id text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_profiles_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.devlink_reviews (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
job_id uuid,
|
|
reviewer_id uuid,
|
|
reviewee_id uuid,
|
|
rating integer CHECK (rating >= 1 AND rating <= 5),
|
|
review_text text,
|
|
review_type text,
|
|
categories jsonb DEFAULT '{}'::jsonb,
|
|
would_work_again boolean,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_reviews_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_reviews_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.devlink_jobs(id),
|
|
CONSTRAINT devlink_reviews_reviewer_id_fkey FOREIGN KEY (reviewer_id) REFERENCES public.devlink_profiles(id),
|
|
CONSTRAINT devlink_reviews_reviewee_id_fkey FOREIGN KEY (reviewee_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_saved_jobs (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
job_id uuid,
|
|
priority text DEFAULT 'medium'::text,
|
|
notes text,
|
|
created_date timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_saved_jobs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_saved_jobs_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id),
|
|
CONSTRAINT devlink_saved_jobs_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.devlink_jobs(id)
|
|
);
|
|
CREATE TABLE public.devlink_skill_assessments (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
skill_name text NOT NULL,
|
|
assessment_type text,
|
|
score integer,
|
|
questions_answered integer DEFAULT 0,
|
|
questions_correct integer DEFAULT 0,
|
|
time_taken_minutes integer,
|
|
time_limit_minutes integer,
|
|
passed boolean DEFAULT false,
|
|
passing_score integer,
|
|
anti_cheat_flags jsonb DEFAULT '[]'::jsonb,
|
|
proctoring_enabled boolean DEFAULT false,
|
|
questions jsonb DEFAULT '[]'::jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_skill_assessments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_skill_assessments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_studios (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
company_profile_id uuid,
|
|
studio_name text,
|
|
description text,
|
|
roblox_group_id text,
|
|
roblox_group_data jsonb DEFAULT '{}'::jsonb,
|
|
user_role_in_group text,
|
|
user_rank_in_group integer,
|
|
verified boolean DEFAULT false,
|
|
status text DEFAULT 'active'::text,
|
|
member_count integer DEFAULT 0,
|
|
banner_url text,
|
|
total_games integer DEFAULT 0,
|
|
total_visits bigint DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_studios_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_studios_company_profile_id_fkey FOREIGN KEY (company_profile_id) REFERENCES public.devlink_company_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_teams (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
name text NOT NULL,
|
|
description text,
|
|
owner_id uuid,
|
|
member_ids ARRAY DEFAULT '{}'::uuid[],
|
|
logo_url text,
|
|
status text DEFAULT 'active'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_teams_pkey PRIMARY KEY (id),
|
|
CONSTRAINT devlink_teams_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.devlink_profiles(id)
|
|
);
|
|
CREATE TABLE public.devlink_waitlist_signups (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
email text NOT NULL UNIQUE,
|
|
name text,
|
|
user_type text DEFAULT 'developer'::text,
|
|
referral_source text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT devlink_waitlist_signups_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.discord_checkins (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
discord_user_id character varying NOT NULL,
|
|
user_id uuid,
|
|
checkin_type character varying NOT NULL,
|
|
message text,
|
|
channel_id character varying,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT discord_checkins_pkey PRIMARY KEY (id),
|
|
CONSTRAINT discord_checkins_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.discord_config (
|
|
key text NOT NULL,
|
|
value jsonb NOT NULL,
|
|
updated_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT discord_config_pkey PRIMARY KEY (key)
|
|
);
|
|
CREATE TABLE public.discord_linking_sessions (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
session_token text NOT NULL UNIQUE,
|
|
expires_at timestamp without time zone NOT NULL,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT discord_linking_sessions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT discord_linking_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.discord_links (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
discord_id text NOT NULL UNIQUE,
|
|
user_id uuid NOT NULL,
|
|
primary_arm text DEFAULT 'labs'::text CHECK (primary_arm = ANY (ARRAY['labs'::text, 'gameforge'::text, 'corp'::text, 'foundation'::text, 'devlink'::text])),
|
|
linked_at timestamp without time zone DEFAULT now(),
|
|
updated_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT discord_links_pkey PRIMARY KEY (id),
|
|
CONSTRAINT discord_links_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.discord_post_webhooks (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
guild_id text NOT NULL,
|
|
channel_id text NOT NULL,
|
|
webhook_url text NOT NULL,
|
|
webhook_id text NOT NULL,
|
|
arm_affiliation text NOT NULL,
|
|
auto_post boolean DEFAULT true,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT discord_post_webhooks_pkey PRIMARY KEY (id),
|
|
CONSTRAINT discord_post_webhooks_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.discord_role_mappings (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
arm text NOT NULL CHECK (arm = ANY (ARRAY['labs'::text, 'gameforge'::text, 'corp'::text, 'foundation'::text, 'devlink'::text])),
|
|
user_type text NOT NULL CHECK (user_type = ANY (ARRAY['game_developer'::text, 'community_member'::text, 'pro_supporter'::text, 'staff'::text, 'creator'::text])),
|
|
discord_role_name text NOT NULL,
|
|
discord_role_id text,
|
|
server_id text,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
updated_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT discord_role_mappings_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.discord_user_roles (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
discord_id text NOT NULL,
|
|
server_id text NOT NULL,
|
|
role_id text NOT NULL,
|
|
role_name text NOT NULL,
|
|
assigned_at timestamp without time zone DEFAULT now(),
|
|
last_verified timestamp without time zone,
|
|
CONSTRAINT discord_user_roles_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.discord_verifications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
discord_id text NOT NULL,
|
|
verification_code text NOT NULL UNIQUE,
|
|
expires_at timestamp without time zone NOT NULL,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT discord_verifications_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.doc_edits (
|
|
id integer NOT NULL DEFAULT nextval('doc_edits_id_seq'::regclass),
|
|
space_id text NOT NULL,
|
|
page_id text NOT NULL,
|
|
title text NOT NULL,
|
|
content text NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
updated_by text,
|
|
CONSTRAINT doc_edits_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.employee_data (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
site_id uuid NOT NULL,
|
|
personal_details jsonb,
|
|
job_history jsonb,
|
|
benefits_info jsonb,
|
|
compliance_docs jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT employee_data_pkey PRIMARY KEY (id),
|
|
CONSTRAINT employee_data_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT employee_data_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.employee_edits (
|
|
id integer NOT NULL DEFAULT nextval('employee_edits_id_seq'::regclass),
|
|
employee_id text NOT NULL UNIQUE,
|
|
data jsonb NOT NULL,
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
updated_by text,
|
|
CONSTRAINT employee_edits_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.endorsements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
endorser_id uuid NOT NULL,
|
|
endorsed_id uuid NOT NULL,
|
|
skill text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT endorsements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT endorsements_endorser_id_fkey FOREIGN KEY (endorser_id) REFERENCES auth.users(id),
|
|
CONSTRAINT endorsements_endorsed_id_fkey FOREIGN KEY (endorsed_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.ethos_artist_profiles (
|
|
user_id uuid NOT NULL,
|
|
skills ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
for_hire boolean NOT NULL DEFAULT true,
|
|
bio text,
|
|
portfolio_url text,
|
|
sample_price_track numeric,
|
|
sample_price_sfx numeric,
|
|
sample_price_score numeric,
|
|
turnaround_days integer,
|
|
verified boolean NOT NULL DEFAULT false,
|
|
total_downloads integer NOT NULL DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
price_list jsonb DEFAULT '{"day_rate": null, "sfx_pack": null, "full_score": null, "track_custom": null, "contact_for_quote": false}'::jsonb,
|
|
ecosystem_license_accepted boolean NOT NULL DEFAULT false,
|
|
ecosystem_license_accepted_at timestamp with time zone,
|
|
CONSTRAINT ethos_artist_profiles_pkey PRIMARY KEY (user_id),
|
|
CONSTRAINT ethos_artist_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.ethos_ecosystem_licenses (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
track_id uuid NOT NULL,
|
|
artist_id uuid NOT NULL,
|
|
accepted_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
agreement_version text NOT NULL DEFAULT '1.0'::text,
|
|
agreement_text_hash text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT ethos_ecosystem_licenses_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ethos_ecosystem_licenses_track_id_fkey FOREIGN KEY (track_id) REFERENCES public.ethos_tracks(id),
|
|
CONSTRAINT ethos_ecosystem_licenses_artist_id_fkey FOREIGN KEY (artist_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.ethos_guild_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL DEFAULT 'member'::text CHECK (role = ANY (ARRAY['member'::text, 'curator'::text, 'admin'::text])),
|
|
joined_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
bio text,
|
|
CONSTRAINT ethos_guild_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ethos_guild_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.ethos_licensing_agreements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
track_id uuid NOT NULL,
|
|
licensee_id uuid NOT NULL,
|
|
license_type text NOT NULL CHECK (license_type = ANY (ARRAY['commercial_one_time'::text, 'commercial_exclusive'::text, 'broadcast'::text])),
|
|
agreement_url text,
|
|
approved boolean NOT NULL DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
expires_at timestamp with time zone,
|
|
CONSTRAINT ethos_licensing_agreements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ethos_licensing_agreements_track_id_fkey FOREIGN KEY (track_id) REFERENCES public.ethos_tracks(id),
|
|
CONSTRAINT ethos_licensing_agreements_licensee_id_fkey FOREIGN KEY (licensee_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.ethos_service_requests (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
artist_id uuid NOT NULL,
|
|
requester_id uuid NOT NULL,
|
|
service_type text NOT NULL CHECK (service_type = ANY (ARRAY['track_custom'::text, 'sfx_pack'::text, 'full_score'::text, 'day_rate'::text, 'contact_for_quote'::text])),
|
|
description text NOT NULL,
|
|
budget numeric,
|
|
deadline timestamp with time zone,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'accepted'::text, 'declined'::text, 'in_progress'::text, 'completed'::text, 'cancelled'::text])),
|
|
notes text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT ethos_service_requests_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ethos_service_requests_artist_id_fkey FOREIGN KEY (artist_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT ethos_service_requests_requester_id_fkey FOREIGN KEY (requester_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.ethos_tracks (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
file_url text NOT NULL,
|
|
duration_seconds integer,
|
|
genre ARRAY,
|
|
license_type text NOT NULL DEFAULT 'ecosystem'::text CHECK (license_type = ANY (ARRAY['ecosystem'::text, 'commercial_sample'::text])),
|
|
bpm integer,
|
|
is_published boolean NOT NULL DEFAULT true,
|
|
download_count integer NOT NULL DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT ethos_tracks_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ethos_tracks_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.ethos_verification_audit_log (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
request_id uuid NOT NULL,
|
|
action text NOT NULL CHECK (action = ANY (ARRAY['submitted'::text, 'approved'::text, 'rejected'::text, 'resubmitted'::text])),
|
|
actor_id uuid,
|
|
notes text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT ethos_verification_audit_log_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ethos_verification_audit_log_request_id_fkey FOREIGN KEY (request_id) REFERENCES public.ethos_verification_requests(id),
|
|
CONSTRAINT ethos_verification_audit_log_actor_id_fkey FOREIGN KEY (actor_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.ethos_verification_requests (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
artist_profile_id uuid NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'approved'::text, 'rejected'::text])),
|
|
submitted_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
reviewed_at timestamp with time zone,
|
|
reviewed_by uuid,
|
|
rejection_reason text,
|
|
submission_notes text,
|
|
portfolio_links ARRAY,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT ethos_verification_requests_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ethos_verification_requests_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT ethos_verification_requests_artist_profile_id_fkey FOREIGN KEY (artist_profile_id) REFERENCES public.ethos_artist_profiles(user_id),
|
|
CONSTRAINT ethos_verification_requests_reviewed_by_fkey FOREIGN KEY (reviewed_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.experiences (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
creator_user_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
thumbnail_url text,
|
|
banner_url text,
|
|
version text DEFAULT '1.0.0'::text,
|
|
status text DEFAULT 'private'::text CHECK (status = ANY (ARRAY['public'::text, 'private'::text, 'under_review'::text])),
|
|
tags ARRAY,
|
|
player_count integer DEFAULT 0,
|
|
max_players integer DEFAULT 10,
|
|
server_size integer DEFAULT 10,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
slug text,
|
|
is_published boolean DEFAULT false,
|
|
creator_group_id uuid,
|
|
roblox_place_id bigint,
|
|
CONSTRAINT experiences_pkey PRIMARY KEY (id),
|
|
CONSTRAINT experiences_creator_id_fkey FOREIGN KEY (creator_user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT experiences_creator_group_id_fkey FOREIGN KEY (creator_group_id) REFERENCES public.groups(id)
|
|
);
|
|
CREATE TABLE public.fan_art (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid,
|
|
user_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
image_url text NOT NULL,
|
|
is_approved boolean DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
is_featured boolean NOT NULL DEFAULT false,
|
|
username text,
|
|
CONSTRAINT fan_art_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.faqs (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
question text NOT NULL,
|
|
answer text NOT NULL,
|
|
category text,
|
|
display_order integer,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT faqs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT fk_site FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.featured_studios (
|
|
id bigint NOT NULL DEFAULT nextval('featured_studios_id_seq'::regclass),
|
|
name text NOT NULL UNIQUE,
|
|
tagline text,
|
|
metrics text,
|
|
specialties ARRAY,
|
|
rank integer,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT featured_studios_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.federation_applications (
|
|
id integer NOT NULL DEFAULT nextval('federation_applications_id_seq'::regclass),
|
|
guild_id character varying NOT NULL UNIQUE,
|
|
guild_name character varying,
|
|
guild_icon character varying,
|
|
owner_id character varying,
|
|
member_count integer DEFAULT 0,
|
|
reason text,
|
|
status character varying DEFAULT 'pending'::character varying,
|
|
reviewed_by character varying,
|
|
reviewed_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT federation_applications_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.federation_bans (
|
|
id integer NOT NULL DEFAULT nextval('federation_bans_id_seq'::regclass),
|
|
user_id character varying NOT NULL,
|
|
username character varying,
|
|
reason text,
|
|
evidence text,
|
|
severity character varying DEFAULT 'low'::character varying,
|
|
reported_by character varying,
|
|
reported_by_guild_id character varying,
|
|
active boolean DEFAULT true,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT federation_bans_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.federation_featured (
|
|
id integer NOT NULL DEFAULT nextval('federation_featured_id_seq'::regclass),
|
|
guild_id character varying NOT NULL UNIQUE,
|
|
subscription_id character varying,
|
|
active boolean DEFAULT true,
|
|
expires_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT federation_featured_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.federation_mappings (
|
|
role_id text NOT NULL,
|
|
role_name text,
|
|
guild_id text,
|
|
guild_name text,
|
|
linked_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT federation_mappings_pkey PRIMARY KEY (role_id)
|
|
);
|
|
CREATE TABLE public.federation_reputation (
|
|
id integer NOT NULL DEFAULT nextval('federation_reputation_id_seq'::regclass),
|
|
guild_id character varying NOT NULL UNIQUE,
|
|
valid_reports integer DEFAULT 0,
|
|
false_positives integer DEFAULT 0,
|
|
total_reports integer DEFAULT 0,
|
|
last_updated timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT federation_reputation_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.federation_servers (
|
|
id integer NOT NULL DEFAULT nextval('federation_servers_id_seq'::regclass),
|
|
guild_id character varying NOT NULL UNIQUE,
|
|
guild_name character varying,
|
|
guild_icon character varying,
|
|
owner_id character varying,
|
|
member_count integer DEFAULT 0,
|
|
status character varying DEFAULT 'pending'::character varying,
|
|
tier character varying DEFAULT 'free'::character varying,
|
|
trust_level character varying DEFAULT 'bronze'::character varying,
|
|
reputation_score integer DEFAULT 0,
|
|
subscription_id character varying,
|
|
subscription_status character varying,
|
|
invite_code character varying,
|
|
description text,
|
|
joined_federation_at timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT federation_servers_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.feedback_surveys (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
questions jsonb,
|
|
is_active boolean DEFAULT true,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT feedback_surveys_pkey PRIMARY KEY (id),
|
|
CONSTRAINT feedback_surveys_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.followers (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
follower_id uuid NOT NULL,
|
|
following_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT followers_pkey PRIMARY KEY (id),
|
|
CONSTRAINT followers_follower_id_fkey FOREIGN KEY (follower_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT followers_following_id_fkey FOREIGN KEY (following_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.forum_categories (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
display_order integer,
|
|
site_id uuid,
|
|
slug text UNIQUE,
|
|
CONSTRAINT forum_categories_pkey PRIMARY KEY (id),
|
|
CONSTRAINT forum_categories_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.forum_comments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
post_id uuid,
|
|
user_id uuid,
|
|
parent_comment_id uuid,
|
|
content text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT forum_comments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT forum_comments_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.forum_posts(id),
|
|
CONSTRAINT forum_comments_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT forum_comments_parent_comment_id_fkey FOREIGN KEY (parent_comment_id) REFERENCES public.forum_comments(id)
|
|
);
|
|
CREATE TABLE public.forum_likes (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
post_id uuid,
|
|
comment_id uuid,
|
|
user_id uuid,
|
|
reaction_type text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT forum_likes_pkey PRIMARY KEY (id),
|
|
CONSTRAINT forum_likes_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.forum_posts(id),
|
|
CONSTRAINT forum_likes_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES public.forum_comments(id),
|
|
CONSTRAINT forum_likes_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.forum_post_tags (
|
|
post_id uuid NOT NULL,
|
|
tag_id uuid NOT NULL,
|
|
CONSTRAINT forum_post_tags_pkey PRIMARY KEY (post_id, tag_id),
|
|
CONSTRAINT forum_post_tags_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.forum_posts(id),
|
|
CONSTRAINT forum_post_tags_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES public.forum_tags(id)
|
|
);
|
|
CREATE TABLE public.forum_posts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
category_id uuid,
|
|
user_id uuid,
|
|
title text NOT NULL,
|
|
content text,
|
|
views_count integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
site_id uuid,
|
|
slug text,
|
|
last_activity_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT forum_posts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT forum_posts_category_id_fkey FOREIGN KEY (category_id) REFERENCES public.forum_categories(id),
|
|
CONSTRAINT forum_posts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT forum_posts_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.forum_tags (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT forum_tags_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.foundation_activity (
|
|
id integer NOT NULL DEFAULT nextval('foundation_activity_id_seq'::regclass),
|
|
member_id uuid,
|
|
action text,
|
|
points integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT foundation_activity_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.foundation_contributions (
|
|
id integer NOT NULL DEFAULT nextval('foundation_contributions_id_seq'::regclass),
|
|
contributor_id uuid,
|
|
type text,
|
|
amount numeric,
|
|
description text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT foundation_contributions_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.foundation_course_content (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
course_id uuid NOT NULL,
|
|
content_type text NOT NULL CHECK (content_type = ANY (ARRAY['markdown'::text, 'pdf'::text, 'code-sample'::text, 'video-link'::text])),
|
|
title text NOT NULL,
|
|
content_url text,
|
|
file_size_kb integer DEFAULT 0,
|
|
chapter_count integer DEFAULT 1,
|
|
download_count integer DEFAULT 0,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
updated_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT foundation_course_content_pkey PRIMARY KEY (id),
|
|
CONSTRAINT foundation_course_content_course_id_fkey FOREIGN KEY (course_id) REFERENCES public.foundation_courses(id)
|
|
);
|
|
CREATE TABLE public.foundation_course_materials (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
course_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
content text NOT NULL,
|
|
format text NOT NULL CHECK (format = ANY (ARRAY['markdown'::text, 'pdf'::text, 'code'::text, 'video'::text])),
|
|
file_size_kb integer DEFAULT 0,
|
|
download_count integer DEFAULT 0,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
updated_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT foundation_course_materials_pkey PRIMARY KEY (id),
|
|
CONSTRAINT foundation_course_materials_course_id_fkey FOREIGN KEY (course_id) REFERENCES public.foundation_courses(id)
|
|
);
|
|
CREATE TABLE public.foundation_courses (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
slug text NOT NULL UNIQUE,
|
|
title text NOT NULL,
|
|
description text NOT NULL,
|
|
content text,
|
|
category text NOT NULL CHECK (category = ANY (ARRAY['game-dev'::text, 'web-dev'::text, 'ai-ml'::text, 'design'::text, 'business'::text])),
|
|
difficulty text NOT NULL CHECK (difficulty = ANY (ARRAY['beginner'::text, 'intermediate'::text, 'advanced'::text])),
|
|
instructor_id uuid,
|
|
cover_image_url text,
|
|
estimated_hours integer DEFAULT 0,
|
|
is_published boolean DEFAULT false,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
updated_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT foundation_courses_pkey PRIMARY KEY (id),
|
|
CONSTRAINT foundation_courses_instructor_id_fkey FOREIGN KEY (instructor_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.foundation_mentorship_requests (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
mentor_id uuid NOT NULL,
|
|
mentee_id uuid NOT NULL,
|
|
message text,
|
|
expertise_area text,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'accepted'::text, 'rejected'::text, 'cancelled'::text])),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT foundation_mentorship_requests_pkey PRIMARY KEY (id),
|
|
CONSTRAINT foundation_mentorship_requests_mentor_id_fkey FOREIGN KEY (mentor_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT foundation_mentorship_requests_mentee_id_fkey FOREIGN KEY (mentee_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.fourthwall_orders (
|
|
id bigint NOT NULL DEFAULT nextval('fourthwall_orders_id_seq'::regclass),
|
|
fourthwall_order_id text NOT NULL UNIQUE,
|
|
customer_email text NOT NULL,
|
|
items jsonb DEFAULT '[]'::jsonb,
|
|
total_amount numeric NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending'::text,
|
|
paid_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT fourthwall_orders_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.fourthwall_products (
|
|
id bigint NOT NULL DEFAULT nextval('fourthwall_products_id_seq'::regclass),
|
|
fourthwall_id text NOT NULL UNIQUE,
|
|
name text NOT NULL,
|
|
description text,
|
|
price numeric NOT NULL,
|
|
currency text NOT NULL DEFAULT 'USD'::text,
|
|
image_url text,
|
|
category text,
|
|
synced_at timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT fourthwall_products_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.fourthwall_webhook_logs (
|
|
id bigint NOT NULL DEFAULT nextval('fourthwall_webhook_logs_id_seq'::regclass),
|
|
event_type text NOT NULL,
|
|
payload jsonb,
|
|
received_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT fourthwall_webhook_logs_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.game_auth_tokens (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
game text NOT NULL,
|
|
token text NOT NULL UNIQUE,
|
|
player_token text,
|
|
expires_at timestamp without time zone NOT NULL,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
last_used timestamp without time zone,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
CONSTRAINT game_auth_tokens_pkey PRIMARY KEY (id),
|
|
CONSTRAINT game_auth_tokens_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.game_sessions (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
game text NOT NULL,
|
|
session_token text NOT NULL UNIQUE,
|
|
device_id text,
|
|
platform text,
|
|
expires_at timestamp without time zone NOT NULL,
|
|
last_activity timestamp without time zone DEFAULT now(),
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT game_sessions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT game_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.gameforge_builds (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL,
|
|
version text NOT NULL,
|
|
build_type text NOT NULL CHECK (build_type = ANY (ARRAY['alpha'::text, 'beta'::text, 'release_candidate'::text, 'final'::text])),
|
|
release_date timestamp with time zone NOT NULL DEFAULT now(),
|
|
download_url text,
|
|
changelog text,
|
|
file_size bigint,
|
|
target_platforms ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
download_count integer NOT NULL DEFAULT 0,
|
|
created_by uuid,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT gameforge_builds_pkey PRIMARY KEY (id),
|
|
CONSTRAINT gameforge_builds_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.gameforge_projects(id),
|
|
CONSTRAINT gameforge_builds_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.gameforge_metrics (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL,
|
|
metric_date timestamp with time zone NOT NULL DEFAULT now(),
|
|
metric_type text NOT NULL CHECK (metric_type = ANY (ARRAY['monthly'::text, 'sprint'::text, 'milestone'::text])),
|
|
velocity integer,
|
|
hours_logged integer,
|
|
team_size_avg integer,
|
|
bugs_found integer DEFAULT 0,
|
|
bugs_fixed integer DEFAULT 0,
|
|
build_count integer DEFAULT 0,
|
|
days_from_planned_to_release integer,
|
|
on_schedule boolean,
|
|
budget_allocated numeric,
|
|
budget_spent numeric,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT gameforge_metrics_pkey PRIMARY KEY (id),
|
|
CONSTRAINT gameforge_metrics_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.gameforge_projects(id)
|
|
);
|
|
CREATE TABLE public.gameforge_projects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
status text NOT NULL DEFAULT 'planning'::text CHECK (status = ANY (ARRAY['planning'::text, 'in_development'::text, 'qa'::text, 'released'::text, 'hiatus'::text, 'cancelled'::text])),
|
|
lead_id uuid NOT NULL,
|
|
platform text NOT NULL CHECK (platform = ANY (ARRAY['Unity'::text, 'Unreal'::text, 'Godot'::text, 'Custom'::text, 'WebGL'::text])),
|
|
genre ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
target_release_date timestamp with time zone,
|
|
actual_release_date timestamp with time zone,
|
|
budget numeric,
|
|
current_spend numeric NOT NULL DEFAULT 0,
|
|
team_size integer DEFAULT 0,
|
|
repository_url text,
|
|
documentation_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT gameforge_projects_pkey PRIMARY KEY (id),
|
|
CONSTRAINT gameforge_projects_lead_id_fkey FOREIGN KEY (lead_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.gameforge_sprint_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
sprint_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL DEFAULT 'contributor'::text CHECK (role = ANY (ARRAY['lead'::text, 'contributor'::text, 'reviewer'::text])),
|
|
joined_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT gameforge_sprint_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT gameforge_sprint_members_sprint_id_fkey FOREIGN KEY (sprint_id) REFERENCES public.gameforge_sprints(id),
|
|
CONSTRAINT gameforge_sprint_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.gameforge_sprints (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL,
|
|
sprint_number integer NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
phase text NOT NULL DEFAULT 'planning'::text CHECK (phase = ANY (ARRAY['planning'::text, 'active'::text, 'completed'::text, 'cancelled'::text])),
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'active'::text, 'on_hold'::text, 'completed'::text])),
|
|
goal text,
|
|
start_date timestamp with time zone,
|
|
end_date timestamp with time zone,
|
|
planned_velocity integer,
|
|
actual_velocity integer,
|
|
created_by uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT gameforge_sprints_pkey PRIMARY KEY (id),
|
|
CONSTRAINT gameforge_sprints_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.gameforge_projects(id),
|
|
CONSTRAINT gameforge_sprints_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.gameforge_tasks (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
sprint_id uuid,
|
|
project_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
status text NOT NULL DEFAULT 'todo'::text CHECK (status = ANY (ARRAY['todo'::text, 'in_progress'::text, 'in_review'::text, 'done'::text, 'blocked'::text])),
|
|
priority text NOT NULL DEFAULT 'medium'::text CHECK (priority = ANY (ARRAY['low'::text, 'medium'::text, 'high'::text, 'critical'::text])),
|
|
estimated_hours numeric,
|
|
actual_hours numeric,
|
|
assigned_to uuid,
|
|
created_by uuid NOT NULL,
|
|
due_date timestamp with time zone,
|
|
completed_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT gameforge_tasks_pkey PRIMARY KEY (id),
|
|
CONSTRAINT gameforge_tasks_sprint_id_fkey FOREIGN KEY (sprint_id) REFERENCES public.gameforge_sprints(id),
|
|
CONSTRAINT gameforge_tasks_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.gameforge_projects(id),
|
|
CONSTRAINT gameforge_tasks_assigned_to_fkey FOREIGN KEY (assigned_to) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT gameforge_tasks_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.gameforge_team_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL CHECK (role = ANY (ARRAY['engineer'::text, 'designer'::text, 'artist'::text, 'producer'::text, 'qa'::text, 'sound_designer'::text, 'writer'::text, 'manager'::text])),
|
|
position text,
|
|
contract_type text NOT NULL DEFAULT 'employee'::text CHECK (contract_type = ANY (ARRAY['employee'::text, 'contractor'::text, 'consultant'::text, 'intern'::text])),
|
|
hourly_rate numeric,
|
|
project_ids ARRAY NOT NULL DEFAULT '{}'::uuid[],
|
|
skills ARRAY DEFAULT '{}'::text[],
|
|
bio text,
|
|
joined_date timestamp with time zone NOT NULL DEFAULT now(),
|
|
left_date timestamp with time zone,
|
|
is_active boolean NOT NULL DEFAULT true,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT gameforge_team_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT gameforge_team_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.giveaways (
|
|
message_id text NOT NULL,
|
|
channel_id text,
|
|
guild_id text,
|
|
prize text,
|
|
winners_count integer DEFAULT 1,
|
|
required_role text,
|
|
host_id text,
|
|
end_time timestamp with time zone,
|
|
entries jsonb DEFAULT '[]'::jsonb,
|
|
status text DEFAULT 'active'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT giveaways_pkey PRIMARY KEY (message_id)
|
|
);
|
|
CREATE TABLE public.goals (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
status text DEFAULT 'in_progress'::text,
|
|
progress integer DEFAULT 0,
|
|
due_date date,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT goals_pkey PRIMARY KEY (id),
|
|
CONSTRAINT goals_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT goals_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.grid_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
discord_id text NOT NULL UNIQUE,
|
|
discord_username text NOT NULL,
|
|
display_name text,
|
|
avatar_url text,
|
|
bio text DEFAULT 'Grid member via Discord Activity'::text,
|
|
xp integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT grid_members_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.group_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
group_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
joined_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
group_role_id uuid,
|
|
role text NOT NULL DEFAULT 'member'::text,
|
|
CONSTRAINT group_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT group_members_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id),
|
|
CONSTRAINT group_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT group_members_group_role_id_fkey FOREIGN KEY (group_role_id) REFERENCES public.group_roles(id)
|
|
);
|
|
CREATE TABLE public.group_roles (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
group_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
permissions jsonb NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT group_roles_pkey PRIMARY KEY (id),
|
|
CONSTRAINT group_roles_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id)
|
|
);
|
|
CREATE TABLE public.groups (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
owner_id uuid NOT NULL,
|
|
name text NOT NULL UNIQUE,
|
|
slug text NOT NULL UNIQUE,
|
|
description text,
|
|
avatar_url text,
|
|
banner_url text,
|
|
is_verified boolean DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT groups_pkey PRIMARY KEY (id),
|
|
CONSTRAINT groups_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.guild_user_xp (
|
|
id integer NOT NULL DEFAULT nextval('guild_user_xp_id_seq'::regclass),
|
|
guild_id character varying NOT NULL,
|
|
user_id character varying NOT NULL,
|
|
xp integer DEFAULT 0,
|
|
level integer DEFAULT 0,
|
|
prestige integer DEFAULT 0,
|
|
messages integer DEFAULT 0,
|
|
voice_minutes integer DEFAULT 0,
|
|
last_xp_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT guild_user_xp_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.help_requests (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
requester_id uuid NOT NULL,
|
|
requester_username character varying NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
category character varying NOT NULL,
|
|
status character varying DEFAULT 'open'::character varying,
|
|
helper_id uuid,
|
|
helper_username character varying,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
resolved_at timestamp with time zone,
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT help_requests_pkey PRIMARY KEY (id),
|
|
CONSTRAINT help_requests_requester_id_fkey FOREIGN KEY (requester_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT help_requests_helper_id_fkey FOREIGN KEY (helper_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.inspirations (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
title text NOT NULL,
|
|
description text,
|
|
image_url text,
|
|
category text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
location_name text,
|
|
latitude numeric,
|
|
longitude numeric,
|
|
CONSTRAINT inspirations_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.interested_prospects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
full_name text NOT NULL,
|
|
email text NOT NULL UNIQUE,
|
|
area_of_interest text,
|
|
linkedin_url text,
|
|
submitted_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT interested_prospects_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.invitations (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
token text NOT NULL UNIQUE,
|
|
email text NOT NULL,
|
|
role text NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
expires_at timestamp with time zone NOT NULL,
|
|
CONSTRAINT invitations_pkey PRIMARY KEY (id),
|
|
CONSTRAINT invitations_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.invites (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
inviter_id uuid NOT NULL,
|
|
invitee_email text NOT NULL,
|
|
token text NOT NULL UNIQUE,
|
|
status text NOT NULL DEFAULT 'pending'::text,
|
|
accepted_by uuid,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
accepted_at timestamp with time zone,
|
|
message text,
|
|
CONSTRAINT invites_pkey PRIMARY KEY (id),
|
|
CONSTRAINT invites_inviter_id_fkey FOREIGN KEY (inviter_id) REFERENCES auth.users(id),
|
|
CONSTRAINT invites_accepted_by_fkey FOREIGN KEY (accepted_by) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.invoices (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
sender_id uuid NOT NULL,
|
|
recipient_id uuid,
|
|
site_id uuid NOT NULL,
|
|
amount numeric NOT NULL,
|
|
due_date date,
|
|
status text DEFAULT 'pending'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT invoices_pkey PRIMARY KEY (id),
|
|
CONSTRAINT invoices_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT invoices_recipient_id_fkey FOREIGN KEY (recipient_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT invoices_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.ip_blacklist (
|
|
id character varying NOT NULL DEFAULT (gen_random_uuid())::character varying,
|
|
ip_address text NOT NULL UNIQUE,
|
|
reason text,
|
|
added_by character varying,
|
|
expires_at timestamp without time zone,
|
|
created_at timestamp without time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT ip_blacklist_pkey PRIMARY KEY (id),
|
|
CONSTRAINT ip_blacklist_added_by_fkey FOREIGN KEY (added_by) REFERENCES public.users(id)
|
|
);
|
|
CREATE TABLE public.job_applications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
job_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
resume_url text NOT NULL,
|
|
cover_letter text,
|
|
status text NOT NULL DEFAULT 'Under Review'::text,
|
|
submitted_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT job_applications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT job_applications_job_id_fkey FOREIGN KEY (job_id) REFERENCES public.job_openings(id),
|
|
CONSTRAINT job_applications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.job_openings (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
department text,
|
|
location text,
|
|
status text DEFAULT 'open'::text,
|
|
is_internal boolean DEFAULT false,
|
|
posted_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT job_openings_pkey PRIMARY KEY (id),
|
|
CONSTRAINT job_openings_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.knowledge_base (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
question text NOT NULL,
|
|
answer text NOT NULL,
|
|
category text,
|
|
tags ARRAY,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT knowledge_base_pkey PRIMARY KEY (id),
|
|
CONSTRAINT knowledge_base_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.level_roles (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
level_threshold integer NOT NULL UNIQUE,
|
|
role_name text NOT NULL,
|
|
icon text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT level_roles_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.listings (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
seller_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
price numeric NOT NULL,
|
|
category text,
|
|
status text NOT NULL DEFAULT 'active'::text,
|
|
images ARRAY,
|
|
featured boolean DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT listings_pkey PRIMARY KEY (id),
|
|
CONSTRAINT listings_seller_id_fkey FOREIGN KEY (seller_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.marketplace_listings (
|
|
id bigint NOT NULL DEFAULT nextval('marketplace_listings_id_seq'::regclass),
|
|
asset_id bigint,
|
|
token_id text,
|
|
contract_address text,
|
|
chain_id integer,
|
|
seller_address text NOT NULL,
|
|
seller_id uuid,
|
|
listing_type text NOT NULL,
|
|
item_type text NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
price numeric NOT NULL,
|
|
price_currency text NOT NULL DEFAULT 'ETH'::text,
|
|
quantity numeric DEFAULT 1,
|
|
starting_price numeric,
|
|
reserve_price numeric,
|
|
current_bid numeric,
|
|
highest_bidder_address text,
|
|
auction_end_time timestamp with time zone,
|
|
file_url text,
|
|
download_url text,
|
|
preview_url text,
|
|
license_type text,
|
|
status text DEFAULT 'active'::text,
|
|
expires_at timestamp with time zone,
|
|
signature text,
|
|
nonce text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT marketplace_listings_pkey PRIMARY KEY (id),
|
|
CONSTRAINT marketplace_listings_asset_id_fkey FOREIGN KEY (asset_id) REFERENCES public.nft_assets(id),
|
|
CONSTRAINT marketplace_listings_seller_id_fkey FOREIGN KEY (seller_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.marketplace_offers (
|
|
id bigint NOT NULL DEFAULT nextval('marketplace_offers_id_seq'::regclass),
|
|
listing_id bigint,
|
|
asset_id bigint,
|
|
collection_id bigint,
|
|
offerer_address text NOT NULL,
|
|
offerer_id uuid,
|
|
price numeric NOT NULL,
|
|
price_currency text NOT NULL DEFAULT 'ETH'::text,
|
|
quantity numeric DEFAULT 1,
|
|
status text DEFAULT 'active'::text,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
signature text,
|
|
nonce text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT marketplace_offers_pkey PRIMARY KEY (id),
|
|
CONSTRAINT marketplace_offers_listing_id_fkey FOREIGN KEY (listing_id) REFERENCES public.marketplace_listings(id),
|
|
CONSTRAINT marketplace_offers_asset_id_fkey FOREIGN KEY (asset_id) REFERENCES public.nft_assets(id),
|
|
CONSTRAINT marketplace_offers_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES public.nft_collections(id),
|
|
CONSTRAINT marketplace_offers_offerer_id_fkey FOREIGN KEY (offerer_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.mentors (
|
|
user_id uuid NOT NULL,
|
|
bio text,
|
|
expertise ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
available boolean NOT NULL DEFAULT true,
|
|
hourly_rate numeric,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT mentors_pkey PRIMARY KEY (user_id),
|
|
CONSTRAINT mentors_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.mentorship_requests (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
mentor_id uuid NOT NULL,
|
|
mentee_id uuid NOT NULL,
|
|
message text,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'accepted'::text, 'rejected'::text, 'cancelled'::text])),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT mentorship_requests_pkey PRIMARY KEY (id),
|
|
CONSTRAINT mentorship_requests_mentor_id_fkey FOREIGN KEY (mentor_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT mentorship_requests_mentee_id_fkey FOREIGN KEY (mentee_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.messages (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
conversation_id uuid NOT NULL,
|
|
sender_id uuid NOT NULL,
|
|
content text NOT NULL,
|
|
is_read boolean NOT NULL DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
subject text,
|
|
type text NOT NULL DEFAULT 'dm'::text,
|
|
CONSTRAINT messages_pkey PRIMARY KEY (id),
|
|
CONSTRAINT messages_conversation_id_fkey FOREIGN KEY (conversation_id) REFERENCES public.conversations(id),
|
|
CONSTRAINT messages_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.mod_actions (
|
|
id integer NOT NULL DEFAULT nextval('mod_actions_id_seq'::regclass),
|
|
guild_id text NOT NULL,
|
|
action text NOT NULL,
|
|
user_id text NOT NULL,
|
|
user_tag text,
|
|
moderator_id text NOT NULL,
|
|
moderator_tag text,
|
|
reason text,
|
|
duration_minutes integer,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT mod_actions_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.moderation_reports (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
reporter_id uuid,
|
|
target_type text NOT NULL CHECK (target_type = ANY (ARRAY['post'::text, 'comment'::text, 'user'::text, 'project'::text, 'other'::text])),
|
|
target_id uuid,
|
|
reason text NOT NULL,
|
|
details text,
|
|
status text NOT NULL DEFAULT 'open'::text CHECK (status = ANY (ARRAY['open'::text, 'resolved'::text, 'ignored'::text])),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT moderation_reports_pkey PRIMARY KEY (id),
|
|
CONSTRAINT moderation_reports_reporter_id_fkey FOREIGN KEY (reporter_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.mrpiglr_events (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
start_time timestamp with time zone NOT NULL,
|
|
end_time timestamp with time zone,
|
|
event_type text,
|
|
image_url text,
|
|
stream_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT mrpiglr_events_pkey PRIMARY KEY (id),
|
|
CONSTRAINT mrpiglr_events_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.mrpiglr_site_badges (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
icon text,
|
|
color text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
category text,
|
|
tier integer,
|
|
required_value integer,
|
|
CONSTRAINT mrpiglr_site_badges_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.mrpiglr_waitlist (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
email text NOT NULL UNIQUE,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT mrpiglr_waitlist_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.music (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
artist text NOT NULL,
|
|
album text,
|
|
year text,
|
|
genre text,
|
|
duration text,
|
|
youtube_id text,
|
|
description text,
|
|
image_prompt text,
|
|
image_url text,
|
|
links jsonb,
|
|
display_order smallint,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT music_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.newsletter_subscribers (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
email text NOT NULL UNIQUE,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT newsletter_subscribers_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.nexus_applications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
opportunity_id uuid NOT NULL,
|
|
creator_id uuid NOT NULL,
|
|
status text NOT NULL DEFAULT 'submitted'::text CHECK (status = ANY (ARRAY['submitted'::text, 'reviewing'::text, 'accepted'::text, 'rejected'::text, 'hired'::text, 'archived'::text])),
|
|
cover_letter text,
|
|
proposed_rate numeric,
|
|
proposal text,
|
|
application_questions jsonb,
|
|
viewed_at timestamp with time zone,
|
|
responded_at timestamp with time zone,
|
|
response_message text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_applications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_applications_opportunity_id_fkey FOREIGN KEY (opportunity_id) REFERENCES public.nexus_opportunities(id),
|
|
CONSTRAINT nexus_applications_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nexus_commission_ledger (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
payment_id uuid,
|
|
period_start date,
|
|
period_end date,
|
|
total_volume numeric NOT NULL,
|
|
total_commission numeric NOT NULL,
|
|
creator_payouts numeric NOT NULL,
|
|
aethex_revenue numeric NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'settled'::text, 'disputed'::text])),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_commission_ledger_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_commission_ledger_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES public.nexus_payments(id)
|
|
);
|
|
CREATE TABLE public.nexus_contracts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
opportunity_id uuid,
|
|
creator_id uuid NOT NULL,
|
|
client_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
contract_type text NOT NULL CHECK (contract_type = ANY (ARRAY['one-time'::text, 'retainer'::text, 'hourly'::text])),
|
|
total_amount numeric NOT NULL,
|
|
aethex_commission_percent numeric NOT NULL DEFAULT 20,
|
|
aethex_commission_amount numeric NOT NULL DEFAULT 0,
|
|
creator_payout_amount numeric NOT NULL DEFAULT 0,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'active'::text, 'completed'::text, 'disputed'::text, 'cancelled'::text])),
|
|
start_date timestamp with time zone,
|
|
end_date timestamp with time zone,
|
|
milestone_count integer DEFAULT 1,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
stripe_payment_intent_id text,
|
|
CONSTRAINT nexus_contracts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_contracts_opportunity_id_fkey FOREIGN KEY (opportunity_id) REFERENCES public.nexus_opportunities(id),
|
|
CONSTRAINT nexus_contracts_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_contracts_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nexus_conversations (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
participant_1 uuid NOT NULL,
|
|
participant_2 uuid NOT NULL,
|
|
opportunity_id uuid,
|
|
contract_id uuid,
|
|
subject text,
|
|
last_message_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_conversations_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_conversations_participant_1_fkey FOREIGN KEY (participant_1) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_conversations_participant_2_fkey FOREIGN KEY (participant_2) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_conversations_opportunity_id_fkey FOREIGN KEY (opportunity_id) REFERENCES public.nexus_opportunities(id),
|
|
CONSTRAINT nexus_conversations_contract_id_fkey FOREIGN KEY (contract_id) REFERENCES public.nexus_contracts(id)
|
|
);
|
|
CREATE TABLE public.nexus_creator_profiles (
|
|
user_id uuid NOT NULL,
|
|
headline text,
|
|
bio text,
|
|
profile_image_url text,
|
|
skills ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
experience_level text NOT NULL DEFAULT 'intermediate'::text CHECK (experience_level = ANY (ARRAY['beginner'::text, 'intermediate'::text, 'advanced'::text, 'expert'::text])),
|
|
hourly_rate numeric,
|
|
portfolio_url text,
|
|
availability_status text NOT NULL DEFAULT 'available'::text CHECK (availability_status = ANY (ARRAY['available'::text, 'busy'::text, 'unavailable'::text])),
|
|
availability_hours_per_week integer,
|
|
verified boolean NOT NULL DEFAULT false,
|
|
total_earnings numeric NOT NULL DEFAULT 0,
|
|
rating numeric,
|
|
review_count integer NOT NULL DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
stripe_connect_account_id text,
|
|
stripe_account_verified boolean DEFAULT false,
|
|
CONSTRAINT nexus_creator_profiles_pkey PRIMARY KEY (user_id),
|
|
CONSTRAINT nexus_creator_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nexus_disputes (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
contract_id uuid NOT NULL,
|
|
reported_by uuid NOT NULL,
|
|
reason text NOT NULL,
|
|
description text,
|
|
evidence_urls ARRAY DEFAULT '{}'::text[],
|
|
status text NOT NULL DEFAULT 'open'::text CHECK (status = ANY (ARRAY['open'::text, 'reviewing'::text, 'resolved'::text, 'escalated'::text])),
|
|
resolution_notes text,
|
|
resolved_by uuid,
|
|
resolved_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_disputes_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_disputes_contract_id_fkey FOREIGN KEY (contract_id) REFERENCES public.nexus_contracts(id),
|
|
CONSTRAINT nexus_disputes_reported_by_fkey FOREIGN KEY (reported_by) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_disputes_resolved_by_fkey FOREIGN KEY (resolved_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nexus_messages (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
conversation_id uuid,
|
|
sender_id uuid NOT NULL,
|
|
recipient_id uuid NOT NULL,
|
|
opportunity_id uuid,
|
|
contract_id uuid,
|
|
message_text text NOT NULL,
|
|
is_read boolean NOT NULL DEFAULT false,
|
|
read_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_messages_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_messages_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_messages_recipient_id_fkey FOREIGN KEY (recipient_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_messages_opportunity_id_fkey FOREIGN KEY (opportunity_id) REFERENCES public.nexus_opportunities(id),
|
|
CONSTRAINT nexus_messages_contract_id_fkey FOREIGN KEY (contract_id) REFERENCES public.nexus_contracts(id)
|
|
);
|
|
CREATE TABLE public.nexus_milestones (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
contract_id uuid NOT NULL,
|
|
milestone_number integer NOT NULL,
|
|
description text,
|
|
amount numeric NOT NULL,
|
|
due_date timestamp with time zone,
|
|
status text NOT NULL DEFAULT 'pending'::text CHECK (status = ANY (ARRAY['pending'::text, 'submitted'::text, 'approved'::text, 'paid'::text, 'rejected'::text])),
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_milestones_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_milestones_contract_id_fkey FOREIGN KEY (contract_id) REFERENCES public.nexus_contracts(id)
|
|
);
|
|
CREATE TABLE public.nexus_opportunities (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
posted_by uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text NOT NULL,
|
|
category text NOT NULL,
|
|
required_skills ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
budget_type text NOT NULL CHECK (budget_type = ANY (ARRAY['hourly'::text, 'fixed'::text, 'range'::text])),
|
|
budget_min numeric,
|
|
budget_max numeric,
|
|
timeline_type text NOT NULL DEFAULT 'flexible'::text CHECK (timeline_type = ANY (ARRAY['urgent'::text, 'short-term'::text, 'long-term'::text, 'ongoing'::text, 'flexible'::text])),
|
|
duration_weeks integer,
|
|
location_requirement text DEFAULT 'remote'::text CHECK (location_requirement = ANY (ARRAY['remote'::text, 'onsite'::text, 'hybrid'::text])),
|
|
required_experience text DEFAULT 'any'::text CHECK (required_experience = ANY (ARRAY['any'::text, 'beginner'::text, 'intermediate'::text, 'advanced'::text, 'expert'::text])),
|
|
company_name text,
|
|
status text NOT NULL DEFAULT 'open'::text CHECK (status = ANY (ARRAY['open'::text, 'in_progress'::text, 'filled'::text, 'closed'::text, 'cancelled'::text])),
|
|
application_count integer NOT NULL DEFAULT 0,
|
|
selected_creator_id uuid,
|
|
views integer NOT NULL DEFAULT 0,
|
|
is_featured boolean NOT NULL DEFAULT false,
|
|
published_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
closed_at timestamp with time zone,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_opportunities_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_opportunities_posted_by_fkey FOREIGN KEY (posted_by) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_opportunities_selected_creator_id_fkey FOREIGN KEY (selected_creator_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nexus_payments (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
contract_id uuid NOT NULL,
|
|
milestone_id uuid,
|
|
amount numeric NOT NULL,
|
|
creator_payout numeric NOT NULL,
|
|
aethex_commission numeric NOT NULL,
|
|
payment_method text NOT NULL DEFAULT 'stripe'::text,
|
|
payment_status text NOT NULL DEFAULT 'pending'::text CHECK (payment_status = ANY (ARRAY['pending'::text, 'processing'::text, 'completed'::text, 'failed'::text, 'refunded'::text])),
|
|
payment_date timestamp with time zone,
|
|
payout_date timestamp with time zone,
|
|
stripe_payment_intent_id text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
stripe_charge_id text,
|
|
CONSTRAINT nexus_payments_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_payments_contract_id_fkey FOREIGN KEY (contract_id) REFERENCES public.nexus_contracts(id),
|
|
CONSTRAINT nexus_payments_milestone_id_fkey FOREIGN KEY (milestone_id) REFERENCES public.nexus_milestones(id)
|
|
);
|
|
CREATE TABLE public.nexus_portfolio_items (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
project_url text,
|
|
image_url text,
|
|
skills_used ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
featured boolean NOT NULL DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_portfolio_items_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_portfolio_items_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nexus_reviews (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
application_id uuid NOT NULL,
|
|
opportunity_id uuid NOT NULL,
|
|
reviewer_id uuid NOT NULL,
|
|
creator_id uuid NOT NULL,
|
|
rating integer NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
|
review_text text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_reviews_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_reviews_application_id_fkey FOREIGN KEY (application_id) REFERENCES public.nexus_applications(id),
|
|
CONSTRAINT nexus_reviews_opportunity_id_fkey FOREIGN KEY (opportunity_id) REFERENCES public.nexus_opportunities(id),
|
|
CONSTRAINT nexus_reviews_reviewer_id_fkey FOREIGN KEY (reviewer_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_reviews_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nexus_skill_endorsements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
creator_id uuid NOT NULL,
|
|
endorsed_by uuid NOT NULL,
|
|
skill text NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT nexus_skill_endorsements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nexus_skill_endorsements_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT nexus_skill_endorsements_endorsed_by_fkey FOREIGN KEY (endorsed_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.nft_assets (
|
|
id bigint NOT NULL DEFAULT nextval('nft_assets_id_seq'::regclass),
|
|
token_id text NOT NULL,
|
|
collection_id bigint,
|
|
contract_address text NOT NULL,
|
|
chain_id integer NOT NULL,
|
|
owner_address text NOT NULL,
|
|
creator_address text,
|
|
token_uri text,
|
|
name text,
|
|
description text,
|
|
image_url text,
|
|
animation_url text,
|
|
external_url text,
|
|
attributes jsonb DEFAULT '[]'::jsonb,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
rarity_score numeric,
|
|
rarity_rank integer,
|
|
last_sale_price numeric,
|
|
last_sale_price_currency text,
|
|
last_sale_date timestamp with time zone,
|
|
cached_at timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT nft_assets_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nft_assets_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES public.nft_collections(id)
|
|
);
|
|
CREATE TABLE public.nft_collections (
|
|
id bigint NOT NULL DEFAULT nextval('nft_collections_id_seq'::regclass),
|
|
contract_address text NOT NULL,
|
|
chain_id integer NOT NULL,
|
|
name text NOT NULL,
|
|
symbol text,
|
|
description text,
|
|
image_url text,
|
|
banner_url text,
|
|
creator_address text NOT NULL,
|
|
owner_id uuid,
|
|
token_standard text,
|
|
total_supply numeric,
|
|
floor_price numeric DEFAULT 0,
|
|
floor_price_currency text DEFAULT 'ETH'::text,
|
|
volume_traded numeric DEFAULT 0,
|
|
total_sales integer DEFAULT 0,
|
|
royalty_percentage numeric DEFAULT 0,
|
|
royalty_address text,
|
|
is_verified boolean DEFAULT false,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
social_links jsonb DEFAULT '{}'::jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT nft_collections_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nft_collections_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.nft_sales (
|
|
id bigint NOT NULL DEFAULT nextval('nft_sales_id_seq'::regclass),
|
|
transaction_hash text NOT NULL,
|
|
block_number bigint,
|
|
chain_id integer NOT NULL,
|
|
event_timestamp timestamp with time zone NOT NULL,
|
|
asset_id bigint,
|
|
token_id text NOT NULL,
|
|
contract_address text NOT NULL,
|
|
collection_id bigint,
|
|
sale_type text,
|
|
auction_type text,
|
|
price numeric,
|
|
price_currency text DEFAULT 'ETH'::text,
|
|
price_usd numeric,
|
|
quantity numeric DEFAULT 1,
|
|
seller_address text,
|
|
buyer_address text,
|
|
platform_address text,
|
|
platform_fee numeric DEFAULT 0,
|
|
royalty_fee numeric DEFAULT 0,
|
|
gas_fee numeric DEFAULT 0,
|
|
marketplace text,
|
|
metadata jsonb DEFAULT '{}'::jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT nft_sales_pkey PRIMARY KEY (id),
|
|
CONSTRAINT nft_sales_asset_id_fkey FOREIGN KEY (asset_id) REFERENCES public.nft_assets(id),
|
|
CONSTRAINT nft_sales_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES public.nft_collections(id)
|
|
);
|
|
CREATE TABLE public.nodes (
|
|
id text NOT NULL DEFAULT (gen_random_uuid())::text,
|
|
x numeric NOT NULL,
|
|
y numeric NOT NULL,
|
|
z numeric DEFAULT 0,
|
|
title text NOT NULL,
|
|
content text NOT NULL,
|
|
category text,
|
|
details ARRAY,
|
|
type text,
|
|
status text CHECK (status = ANY (ARRAY['healthy'::text, 'warning'::text, 'error'::text, 'inactive'::text])),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT nodes_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.notification_preferences (
|
|
user_id uuid NOT NULL,
|
|
prefs jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT notification_preferences_pkey PRIMARY KEY (user_id),
|
|
CONSTRAINT notification_preferences_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.notifications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
type text NOT NULL,
|
|
data jsonb,
|
|
is_read boolean DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT notifications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.now_playing (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
track_title text NOT NULL,
|
|
artist_name text NOT NULL,
|
|
album_art_url text,
|
|
track_url text,
|
|
is_active boolean DEFAULT true,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT now_playing_pkey PRIMARY KEY (id),
|
|
CONSTRAINT now_playing_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.oauth_authorization_codes (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
code text NOT NULL UNIQUE,
|
|
client_id text NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
redirect_uri text NOT NULL,
|
|
code_challenge text,
|
|
code_challenge_method text,
|
|
scope text NOT NULL DEFAULT 'openid profile email'::text,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
used boolean NOT NULL DEFAULT false,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT oauth_authorization_codes_pkey PRIMARY KEY (id),
|
|
CONSTRAINT oauth_authorization_codes_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.oauth_clients(client_id)
|
|
);
|
|
CREATE TABLE public.oauth_clients (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
client_id text NOT NULL UNIQUE,
|
|
client_secret text,
|
|
name text NOT NULL,
|
|
description text,
|
|
redirect_uris jsonb NOT NULL DEFAULT '[]'::jsonb,
|
|
allowed_scopes ARRAY NOT NULL DEFAULT ARRAY['openid'::text, 'profile'::text, 'email'::text],
|
|
is_trusted boolean NOT NULL DEFAULT false,
|
|
is_active boolean NOT NULL DEFAULT true,
|
|
logo_url text,
|
|
website_url text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT oauth_clients_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.oauth_refresh_tokens (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
token text NOT NULL UNIQUE,
|
|
client_id text NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
scope text NOT NULL DEFAULT 'openid profile email'::text,
|
|
expires_at timestamp with time zone NOT NULL,
|
|
revoked boolean NOT NULL DEFAULT false,
|
|
last_used_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT oauth_refresh_tokens_pkey PRIMARY KEY (id),
|
|
CONSTRAINT oauth_refresh_tokens_client_id_fkey FOREIGN KEY (client_id) REFERENCES public.oauth_clients(client_id)
|
|
);
|
|
CREATE TABLE public.orders (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
listing_id uuid NOT NULL,
|
|
buyer_id uuid NOT NULL,
|
|
seller_id uuid NOT NULL,
|
|
price_at_purchase numeric NOT NULL,
|
|
status text NOT NULL DEFAULT 'pending'::text,
|
|
stripe_account_id text,
|
|
stripe_payment_intent_id text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT orders_pkey PRIMARY KEY (id),
|
|
CONSTRAINT orders_listing_id_fkey FOREIGN KEY (listing_id) REFERENCES public.listings(id),
|
|
CONSTRAINT orders_buyer_id_fkey FOREIGN KEY (buyer_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT orders_seller_id_fkey FOREIGN KEY (seller_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.pages (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
slug text NOT NULL UNIQUE,
|
|
title text NOT NULL,
|
|
content jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT pages_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.pair_sessions (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
initiator_id uuid NOT NULL,
|
|
initiator_username character varying NOT NULL,
|
|
partner_id uuid,
|
|
partner_username character varying,
|
|
session_type character varying NOT NULL,
|
|
status character varying DEFAULT 'waiting'::character varying,
|
|
started_at timestamp with time zone,
|
|
ended_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT pair_sessions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT pair_sessions_initiator_id_fkey FOREIGN KEY (initiator_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT pair_sessions_partner_id_fkey FOREIGN KEY (partner_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.passport_scores (
|
|
id bigint NOT NULL DEFAULT nextval('passport_scores_id_seq'::regclass),
|
|
user_id uuid NOT NULL,
|
|
wallet_address text NOT NULL,
|
|
score numeric NOT NULL DEFAULT 0,
|
|
status text,
|
|
evidence jsonb DEFAULT '{}'::jsonb,
|
|
last_updated timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT passport_scores_pkey PRIMARY KEY (id),
|
|
CONSTRAINT passport_scores_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.payslips (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid,
|
|
site_id uuid,
|
|
pay_period_start date NOT NULL,
|
|
pay_period_end date NOT NULL,
|
|
amount numeric NOT NULL,
|
|
file_url text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT payslips_pkey PRIMARY KEY (id),
|
|
CONSTRAINT payslips_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT payslips_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.performance_reviews (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
reviewer_id uuid,
|
|
site_id uuid NOT NULL,
|
|
review_date date NOT NULL,
|
|
ratings jsonb,
|
|
comments text,
|
|
document_url text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT performance_reviews_pkey PRIMARY KEY (id),
|
|
CONSTRAINT performance_reviews_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT performance_reviews_reviewer_id_fkey FOREIGN KEY (reviewer_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT performance_reviews_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.permissions (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT permissions_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.personal_site_config (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL UNIQUE,
|
|
site_status text NOT NULL DEFAULT 'live'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT personal_site_config_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.polls (
|
|
id integer NOT NULL DEFAULT nextval('polls_id_seq'::regclass),
|
|
guild_id text NOT NULL,
|
|
channel_id text,
|
|
message_id text,
|
|
question text NOT NULL,
|
|
options jsonb NOT NULL,
|
|
votes jsonb DEFAULT '{}'::jsonb,
|
|
created_by text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT polls_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.portfolio_projects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid,
|
|
slug text NOT NULL UNIQUE,
|
|
title text NOT NULL,
|
|
subtitle text,
|
|
cover_image_url text,
|
|
description text,
|
|
content jsonb,
|
|
tags ARRAY,
|
|
project_date date,
|
|
live_url text,
|
|
github_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT portfolio_projects_pkey PRIMARY KEY (id),
|
|
CONSTRAINT portfolio_projects_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.post_likes (
|
|
post_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT post_likes_pkey PRIMARY KEY (post_id, user_id),
|
|
CONSTRAINT post_likes_post_id_fkey FOREIGN KEY (post_id) REFERENCES public.posts(id),
|
|
CONSTRAINT post_likes_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.posts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
content text NOT NULL,
|
|
media_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT posts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT posts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.products (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
price numeric,
|
|
category text,
|
|
description text,
|
|
image_prompt text,
|
|
image_url text,
|
|
tags ARRAY,
|
|
stock integer,
|
|
rating numeric,
|
|
display_order smallint,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
title text NOT NULL,
|
|
CONSTRAINT products_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.profiles (
|
|
id uuid NOT NULL,
|
|
username text NOT NULL UNIQUE,
|
|
role text NOT NULL DEFAULT 'member'::text,
|
|
onboarded boolean NOT NULL DEFAULT false,
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
bio text,
|
|
skills ARRAY,
|
|
avatar_url text,
|
|
banner_url text,
|
|
social_links jsonb,
|
|
loyalty_points bigint NOT NULL DEFAULT 0,
|
|
email text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
user_type USER-DEFINED DEFAULT 'community_member'::user_type_enum,
|
|
experience_level USER-DEFINED DEFAULT 'beginner'::experience_level_enum,
|
|
full_name text,
|
|
location text,
|
|
website_url text,
|
|
github_url text,
|
|
twitter_url text,
|
|
linkedin_url text,
|
|
total_xp integer DEFAULT 0,
|
|
level integer DEFAULT 1,
|
|
featured_badge_ids ARRAY,
|
|
aethex_passport_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
|
|
signature text,
|
|
status text DEFAULT 'offline'::text,
|
|
telemetry_api_key text,
|
|
active_title text,
|
|
suspended_at timestamp with time zone,
|
|
suspension_reason text,
|
|
roblox_user_id bigint UNIQUE,
|
|
roblox_access_token text,
|
|
roblox_refresh_token text,
|
|
roblox_token_expires_at timestamp with time zone,
|
|
experience jsonb,
|
|
education jsonb,
|
|
primary_role text,
|
|
specialization jsonb,
|
|
is_verified boolean DEFAULT false,
|
|
CONSTRAINT profiles_pkey PRIMARY KEY (id),
|
|
CONSTRAINT profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.project_members (
|
|
project_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL DEFAULT 'contributor'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT project_members_pkey PRIMARY KEY (project_id, user_id),
|
|
CONSTRAINT project_members_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id),
|
|
CONSTRAINT project_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.project_tasks (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
status text NOT NULL DEFAULT 'todo'::text,
|
|
assignee_id uuid,
|
|
due_date date,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT project_tasks_pkey PRIMARY KEY (id),
|
|
CONSTRAINT project_tasks_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id),
|
|
CONSTRAINT project_tasks_assignee_id_fkey FOREIGN KEY (assignee_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.project_team_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid,
|
|
user_id uuid,
|
|
role text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
site_id uuid,
|
|
CONSTRAINT project_team_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT project_team_members_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id),
|
|
CONSTRAINT project_team_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT fk_site_id FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.projects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
owner_id uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
status text DEFAULT 'In Progress'::text,
|
|
github_url text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
user_id uuid,
|
|
site_id uuid,
|
|
engine text,
|
|
priority text DEFAULT 'medium'::text,
|
|
progress integer DEFAULT 0,
|
|
live_url text,
|
|
technologies ARRAY,
|
|
CONSTRAINT projects_pkey PRIMARY KEY (id),
|
|
CONSTRAINT projects_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT projects_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT projects_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.quests (
|
|
id integer NOT NULL DEFAULT nextval('quests_id_seq'::regclass),
|
|
guild_id character varying NOT NULL,
|
|
name character varying NOT NULL,
|
|
description text,
|
|
quest_type character varying DEFAULT 'daily'::character varying,
|
|
trigger_type character varying NOT NULL,
|
|
target_value integer NOT NULL,
|
|
xp_reward integer DEFAULT 100,
|
|
active boolean DEFAULT true,
|
|
starts_at timestamp with time zone,
|
|
expires_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT quests_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.refresh_tokens (
|
|
id character varying NOT NULL DEFAULT (gen_random_uuid())::character varying,
|
|
user_id character varying NOT NULL,
|
|
token text NOT NULL UNIQUE,
|
|
expires_at timestamp without time zone NOT NULL,
|
|
created_at timestamp without time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT refresh_tokens_pkey PRIMARY KEY (id),
|
|
CONSTRAINT refresh_tokens_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
|
|
);
|
|
CREATE TABLE public.reputation_events (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
aethex_passport_id uuid NOT NULL,
|
|
event_type text NOT NULL,
|
|
points integer NOT NULL,
|
|
site_id text,
|
|
metadata jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT reputation_events_pkey PRIMARY KEY (id),
|
|
CONSTRAINT reputation_events_aethex_passport_id_fkey FOREIGN KEY (aethex_passport_id) REFERENCES public.aethex_passports(id)
|
|
);
|
|
CREATE TABLE public.reputation_scores (
|
|
aethex_passport_id uuid NOT NULL,
|
|
score integer NOT NULL DEFAULT 0,
|
|
updated_at timestamp with time zone,
|
|
CONSTRAINT reputation_scores_pkey PRIMARY KEY (aethex_passport_id),
|
|
CONSTRAINT reputation_scores_aethex_passport_id_fkey FOREIGN KEY (aethex_passport_id) REFERENCES public.aethex_passports(id)
|
|
);
|
|
CREATE TABLE public.resources (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
title character varying NOT NULL,
|
|
description text NOT NULL,
|
|
category character varying NOT NULL,
|
|
file_url text NOT NULL,
|
|
file_type character varying NOT NULL,
|
|
file_size_bytes bigint,
|
|
thumbnail_url text,
|
|
tags ARRAY DEFAULT '{}'::text[],
|
|
download_count integer NOT NULL DEFAULT 0,
|
|
published_by uuid,
|
|
published_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
is_featured boolean DEFAULT false,
|
|
CONSTRAINT resources_pkey PRIMARY KEY (id),
|
|
CONSTRAINT resources_published_by_fkey FOREIGN KEY (published_by) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.reviews (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
order_id uuid NOT NULL,
|
|
reviewer_id uuid NOT NULL,
|
|
reviewee_id uuid NOT NULL,
|
|
rating integer NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
|
comment text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT reviews_pkey PRIMARY KEY (id),
|
|
CONSTRAINT reviews_order_id_fkey FOREIGN KEY (order_id) REFERENCES public.orders(id),
|
|
CONSTRAINT reviews_reviewer_id_fkey FOREIGN KEY (reviewer_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT reviews_reviewee_id_fkey FOREIGN KEY (reviewee_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.reward_events (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
type text NOT NULL,
|
|
points_kind text NOT NULL DEFAULT 'xp'::text,
|
|
amount integer NOT NULL DEFAULT 0,
|
|
metadata jsonb,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT reward_events_pkey PRIMARY KEY (id),
|
|
CONSTRAINT reward_events_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.rewards (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
type text NOT NULL,
|
|
value jsonb,
|
|
title_id uuid,
|
|
site_id uuid,
|
|
cost integer NOT NULL DEFAULT 100,
|
|
stock integer,
|
|
rarity text DEFAULT 'Common'::text,
|
|
bg_color text DEFAULT '#4A5568'::text,
|
|
color text DEFAULT '#FFFFFF'::text,
|
|
CONSTRAINT rewards_pkey PRIMARY KEY (id),
|
|
CONSTRAINT rewards_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.roblox_links (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
roblox_user_id text NOT NULL UNIQUE,
|
|
roblox_username text NOT NULL,
|
|
linked_at timestamp without time zone DEFAULT now(),
|
|
last_verified timestamp without time zone,
|
|
CONSTRAINT roblox_links_pkey PRIMARY KEY (id),
|
|
CONSTRAINT roblox_links_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.role_panels (
|
|
message_id text NOT NULL,
|
|
channel_id text,
|
|
guild_id text,
|
|
title text,
|
|
description text,
|
|
color text,
|
|
roles jsonb DEFAULT '[]'::jsonb,
|
|
created_by text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT role_panels_pkey PRIMARY KEY (message_id)
|
|
);
|
|
CREATE TABLE public.role_permissions (
|
|
role_id uuid NOT NULL,
|
|
permission_id uuid NOT NULL,
|
|
CONSTRAINT role_permissions_pkey PRIMARY KEY (role_id, permission_id),
|
|
CONSTRAINT role_permissions_role_id_fkey FOREIGN KEY (role_id) REFERENCES public.roles(id),
|
|
CONSTRAINT role_permissions_permission_id_fkey FOREIGN KEY (permission_id) REFERENCES public.permissions(id)
|
|
);
|
|
CREATE TABLE public.roles (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL UNIQUE,
|
|
description text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT roles_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.saved_views (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL,
|
|
description text,
|
|
user_id text,
|
|
filters jsonb DEFAULT '{}'::jsonb,
|
|
layout_type text NOT NULL,
|
|
camera_state jsonb NOT NULL,
|
|
hidden_node_ids ARRAY DEFAULT '{}'::text[],
|
|
hidden_edge_ids ARRAY DEFAULT '{}'::text[],
|
|
node_color_property text,
|
|
node_size_property text,
|
|
edge_style_property text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT saved_views_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.scheduled_messages (
|
|
id text NOT NULL,
|
|
guild_id text,
|
|
channel_id text,
|
|
type text,
|
|
content text,
|
|
embed_data jsonb,
|
|
send_time timestamp with time zone,
|
|
created_by text,
|
|
status text DEFAULT 'pending'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT scheduled_messages_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.scrub_logs (
|
|
id character varying NOT NULL DEFAULT (gen_random_uuid())::character varying,
|
|
user_id character varying,
|
|
original_length integer NOT NULL,
|
|
scrubbed_length integer NOT NULL,
|
|
detection_count integer NOT NULL DEFAULT 0,
|
|
detection_types ARRAY,
|
|
status text NOT NULL,
|
|
ip_address text,
|
|
timestamp timestamp without time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT scrub_logs_pkey PRIMARY KEY (id),
|
|
CONSTRAINT scrub_logs_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
|
|
);
|
|
CREATE TABLE public.server_config (
|
|
guild_id text NOT NULL,
|
|
welcome_channel text,
|
|
goodbye_channel text,
|
|
modlog_channel text,
|
|
level_up_channel text,
|
|
auto_role text,
|
|
verified_role text,
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT server_config_pkey PRIMARY KEY (guild_id)
|
|
);
|
|
CREATE TABLE public.service_inquiries (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
service_id uuid NOT NULL,
|
|
service_title text NOT NULL,
|
|
name text NOT NULL,
|
|
email text NOT NULL,
|
|
message text,
|
|
status text NOT NULL DEFAULT 'pending'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT service_inquiries_pkey PRIMARY KEY (id),
|
|
CONSTRAINT fk_service FOREIGN KEY (service_id) REFERENCES public.consultancy_services(id)
|
|
);
|
|
CREATE TABLE public.sessions (
|
|
id character varying NOT NULL DEFAULT (gen_random_uuid())::character varying,
|
|
user_id character varying NOT NULL,
|
|
username text NOT NULL,
|
|
token text NOT NULL,
|
|
expires_at timestamp without time zone NOT NULL,
|
|
created_at timestamp without time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT sessions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id)
|
|
);
|
|
CREATE TABLE public.shop_items (
|
|
id integer NOT NULL DEFAULT nextval('shop_items_id_seq'::regclass),
|
|
guild_id character varying NOT NULL,
|
|
name character varying NOT NULL,
|
|
description text,
|
|
price integer NOT NULL,
|
|
item_type character varying NOT NULL,
|
|
item_data jsonb DEFAULT '{}'::jsonb,
|
|
stock integer,
|
|
enabled boolean DEFAULT true,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT shop_items_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.shorts (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
video_url text NOT NULL,
|
|
description text,
|
|
likes_count integer NOT NULL DEFAULT 0,
|
|
comments_count integer NOT NULL DEFAULT 0,
|
|
shares_count integer NOT NULL DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT shorts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT shorts_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.showcase_contributors (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
title text,
|
|
avatar text,
|
|
CONSTRAINT showcase_contributors_pkey PRIMARY KEY (id),
|
|
CONSTRAINT showcase_contributors_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.showcase_projects(id)
|
|
);
|
|
CREATE TABLE public.showcase_project_links (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
project_id uuid NOT NULL,
|
|
label text NOT NULL,
|
|
href text NOT NULL,
|
|
CONSTRAINT showcase_project_links_pkey PRIMARY KEY (id),
|
|
CONSTRAINT showcase_project_links_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.showcase_projects(id)
|
|
);
|
|
CREATE TABLE public.showcase_projects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
title text NOT NULL,
|
|
org_unit text CHECK (org_unit = ANY (ARRAY['Studio'::text, 'Labs'::text, 'Platform'::text, 'Community'::text])),
|
|
role text,
|
|
timeframe text,
|
|
description text,
|
|
tags ARRAY NOT NULL DEFAULT '{}'::text[],
|
|
image text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT showcase_projects_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.site_config (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL UNIQUE,
|
|
site_name text,
|
|
site_description text,
|
|
logo_url text,
|
|
favicon_url text,
|
|
primary_color text,
|
|
secondary_color text,
|
|
updated_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
maintenance_ends_at timestamp with time zone,
|
|
system_status text NOT NULL DEFAULT 'online'::text,
|
|
system_status_message text,
|
|
maintenance_mode boolean DEFAULT false,
|
|
status_updates jsonb DEFAULT '[]'::jsonb,
|
|
CONSTRAINT site_config_pkey PRIMARY KEY (id),
|
|
CONSTRAINT fk_site_id FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.site_context (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
site_id uuid NOT NULL,
|
|
role text NOT NULL DEFAULT 'member'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT site_context_pkey PRIMARY KEY (id),
|
|
CONSTRAINT site_context_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.site_metrics (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
metric_name text NOT NULL,
|
|
metric_value text NOT NULL,
|
|
display_order integer,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT site_metrics_pkey PRIMARY KEY (id),
|
|
CONSTRAINT fk_site_id FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.site_settings (
|
|
key text NOT NULL,
|
|
value jsonb NOT NULL DEFAULT '{}'::jsonb,
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT site_settings_pkey PRIMARY KEY (key)
|
|
);
|
|
CREATE TABLE public.social_links (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
url text NOT NULL,
|
|
icon text NOT NULL,
|
|
display_order smallint,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT social_links_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.sponsors (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
logo_url text,
|
|
website_url text,
|
|
display_order integer,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT sponsors_pkey PRIMARY KEY (id),
|
|
CONSTRAINT fk_site FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.sprints (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name character varying NOT NULL,
|
|
description text,
|
|
start_date date NOT NULL,
|
|
end_date date NOT NULL,
|
|
total_points integer DEFAULT 0,
|
|
completed_points integer DEFAULT 0,
|
|
status character varying DEFAULT 'active'::character varying,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT sprints_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.staff_contractors (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid UNIQUE,
|
|
email text NOT NULL UNIQUE,
|
|
full_name text NOT NULL,
|
|
position text,
|
|
company text,
|
|
phone text,
|
|
avatar_url text,
|
|
contract_type text DEFAULT 'contractor'::text CHECK (contract_type = ANY (ARRAY['contractor'::text, 'consultant'::text, 'partner'::text])),
|
|
is_active boolean DEFAULT true,
|
|
start_date date,
|
|
end_date date,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
location text,
|
|
CONSTRAINT staff_contractors_pkey PRIMARY KEY (id),
|
|
CONSTRAINT staff_contractors_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.staff_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid UNIQUE,
|
|
email text NOT NULL UNIQUE,
|
|
full_name text NOT NULL,
|
|
position text,
|
|
department text,
|
|
phone text,
|
|
avatar_url text,
|
|
role text DEFAULT 'employee'::text CHECK (role = ANY (ARRAY['owner'::text, 'admin'::text, 'founder'::text, 'staff'::text, 'employee'::text])),
|
|
is_active boolean DEFAULT true,
|
|
hired_date date,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
location text,
|
|
CONSTRAINT staff_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT staff_members_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.stripe_accounts (
|
|
id uuid NOT NULL,
|
|
stripe_account_id text NOT NULL UNIQUE,
|
|
charges_enabled boolean NOT NULL DEFAULT false,
|
|
payouts_enabled boolean NOT NULL DEFAULT false,
|
|
details_submitted boolean NOT NULL DEFAULT false,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT stripe_accounts_pkey PRIMARY KEY (id),
|
|
CONSTRAINT stripe_accounts_id_fkey FOREIGN KEY (id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.studio_projects (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
title text NOT NULL,
|
|
description text,
|
|
likes integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT studio_projects_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.survey_responses (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
survey_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
responses jsonb,
|
|
submitted_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT survey_responses_pkey PRIMARY KEY (id),
|
|
CONSTRAINT survey_responses_survey_id_fkey FOREIGN KEY (survey_id) REFERENCES public.feedback_surveys(id),
|
|
CONSTRAINT survey_responses_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.system_settings (
|
|
key text NOT NULL,
|
|
value jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT system_settings_pkey PRIMARY KEY (key)
|
|
);
|
|
CREATE TABLE public.team_activity_log (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
username character varying NOT NULL,
|
|
activity_type character varying NOT NULL,
|
|
activity_metadata jsonb,
|
|
points integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT team_activity_log_pkey PRIMARY KEY (id),
|
|
CONSTRAINT team_activity_log_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.team_members (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
profile_id uuid UNIQUE,
|
|
name text NOT NULL,
|
|
role text NOT NULL,
|
|
bio text,
|
|
avatar_url text,
|
|
social_links jsonb,
|
|
order_index integer DEFAULT 0,
|
|
created_at timestamp with time zone NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
CONSTRAINT team_members_pkey PRIMARY KEY (id),
|
|
CONSTRAINT team_members_profile_id_fkey FOREIGN KEY (profile_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.team_memberships (
|
|
team_id uuid NOT NULL,
|
|
user_id uuid NOT NULL,
|
|
role text NOT NULL DEFAULT 'member'::text,
|
|
status text NOT NULL DEFAULT 'active'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT team_memberships_pkey PRIMARY KEY (team_id, user_id),
|
|
CONSTRAINT team_memberships_team_id_fkey FOREIGN KEY (team_id) REFERENCES public.teams(id),
|
|
CONSTRAINT team_memberships_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.team_presence (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
discord_user_id character varying NOT NULL,
|
|
discord_username character varying NOT NULL,
|
|
status text,
|
|
is_online boolean DEFAULT true,
|
|
last_seen timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT team_presence_pkey PRIMARY KEY (id),
|
|
CONSTRAINT team_presence_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.teams (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
owner_id uuid NOT NULL,
|
|
name text NOT NULL,
|
|
slug text UNIQUE,
|
|
description text,
|
|
visibility text NOT NULL DEFAULT 'private'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT teams_pkey PRIMARY KEY (id),
|
|
CONSTRAINT teams_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.tickets (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
site_id uuid,
|
|
project_id uuid,
|
|
created_by uuid,
|
|
assigned_to uuid,
|
|
title text NOT NULL,
|
|
description text,
|
|
status text DEFAULT 'Open'::text,
|
|
priority text DEFAULT 'Medium'::text,
|
|
type text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT tickets_pkey PRIMARY KEY (id),
|
|
CONSTRAINT tickets_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id),
|
|
CONSTRAINT tickets_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.projects(id),
|
|
CONSTRAINT tickets_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.profiles(id),
|
|
CONSTRAINT tickets_assigned_to_fkey FOREIGN KEY (assigned_to) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.time_off_requests (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid,
|
|
site_id uuid,
|
|
start_date date NOT NULL,
|
|
end_date date NOT NULL,
|
|
reason text,
|
|
status text DEFAULT 'Pending'::text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT time_off_requests_pkey PRIMARY KEY (id),
|
|
CONSTRAINT time_off_requests_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT time_off_requests_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.time_tracking (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid,
|
|
site_id uuid,
|
|
clock_in timestamp with time zone NOT NULL,
|
|
clock_out timestamp with time zone,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT time_tracking_pkey PRIMARY KEY (id),
|
|
CONSTRAINT time_tracking_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT time_tracking_site_id_fkey FOREIGN KEY (site_id) REFERENCES public.aethex_sites(id)
|
|
);
|
|
CREATE TABLE public.timesheet_entries (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
work_date date NOT NULL,
|
|
total_hours numeric NOT NULL,
|
|
billable_hours numeric DEFAULT 0,
|
|
status character varying DEFAULT 'draft'::character varying,
|
|
approved_by uuid,
|
|
approved_at timestamp with time zone,
|
|
exported_to_nexus boolean DEFAULT false,
|
|
nexus_sync_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT timesheet_entries_pkey PRIMARY KEY (id),
|
|
CONSTRAINT timesheet_entries_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
|
|
CONSTRAINT timesheet_entries_approved_by_fkey FOREIGN KEY (approved_by) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.token_balances (
|
|
id bigint NOT NULL DEFAULT nextval('token_balances_id_seq'::regclass),
|
|
user_id uuid NOT NULL,
|
|
wallet_address text NOT NULL,
|
|
chain_id integer NOT NULL,
|
|
token_address text,
|
|
token_symbol text NOT NULL,
|
|
token_name text,
|
|
token_decimals integer DEFAULT 18,
|
|
balance numeric NOT NULL DEFAULT 0,
|
|
balance_usd numeric DEFAULT 0,
|
|
price_usd numeric DEFAULT 0,
|
|
last_synced_at timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT token_balances_pkey PRIMARY KEY (id),
|
|
CONSTRAINT token_balances_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.type_racer_games (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
username character varying NOT NULL,
|
|
challenge_text text NOT NULL,
|
|
wpm integer NOT NULL,
|
|
accuracy numeric NOT NULL,
|
|
time_seconds integer NOT NULL,
|
|
is_practice boolean DEFAULT true,
|
|
race_id uuid,
|
|
placement integer,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT type_racer_games_pkey PRIMARY KEY (id),
|
|
CONSTRAINT type_racer_games_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.user_achievements (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid,
|
|
achievement_id uuid,
|
|
site_id text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
unlocked_at timestamp with time zone DEFAULT now(),
|
|
earned_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_achievements_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_achievements_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT user_achievements_achievement_id_fkey FOREIGN KEY (achievement_id) REFERENCES public.achievements(id)
|
|
);
|
|
CREATE TABLE public.user_activities (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid,
|
|
activity_type text,
|
|
message text,
|
|
metadata jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_activities_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_activities_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.user_aethex_badges (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
badge_id uuid NOT NULL,
|
|
unlocked_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT user_aethex_badges_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_aethex_badges_badge_id_fkey FOREIGN KEY (badge_id) REFERENCES public.aethex_badges(id),
|
|
CONSTRAINT user_aethex_badges_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id)
|
|
);
|
|
CREATE TABLE public.user_connections (
|
|
user_id uuid NOT NULL,
|
|
connection_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT user_connections_pkey PRIMARY KEY (user_id, connection_id),
|
|
CONSTRAINT user_connections_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
|
|
CONSTRAINT user_connections_connection_id_fkey FOREIGN KEY (connection_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.user_email_links (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
email text NOT NULL UNIQUE,
|
|
is_primary boolean DEFAULT false,
|
|
verified_at timestamp with time zone DEFAULT now(),
|
|
linked_at timestamp with time zone DEFAULT now(),
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_email_links_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_email_links_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.user_experience_link (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
experience_id uuid NOT NULL,
|
|
asset_id uuid NOT NULL,
|
|
asset_type text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_experience_link_pkey PRIMARY KEY (id),
|
|
CONSTRAINT experience_assets_experience_id_fkey FOREIGN KEY (experience_id) REFERENCES public.experiences(id),
|
|
CONSTRAINT experience_assets_asset_id_fkey FOREIGN KEY (asset_id) REFERENCES public.assets(id)
|
|
);
|
|
CREATE TABLE public.user_favorites (
|
|
id bigint NOT NULL DEFAULT nextval('user_favorites_id_seq'::regclass),
|
|
user_id uuid NOT NULL,
|
|
asset_id bigint,
|
|
collection_id bigint,
|
|
listing_id bigint,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_favorites_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_favorites_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
|
|
CONSTRAINT user_favorites_asset_id_fkey FOREIGN KEY (asset_id) REFERENCES public.nft_assets(id),
|
|
CONSTRAINT user_favorites_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES public.nft_collections(id),
|
|
CONSTRAINT user_favorites_listing_id_fkey FOREIGN KEY (listing_id) REFERENCES public.marketplace_listings(id)
|
|
);
|
|
CREATE TABLE public.user_follows (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
follower_id uuid NOT NULL,
|
|
following_id uuid NOT NULL,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT user_follows_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_follows_follower_id_fkey FOREIGN KEY (follower_id) REFERENCES public.user_profiles(id),
|
|
CONSTRAINT user_follows_following_id_fkey FOREIGN KEY (following_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.user_interests (
|
|
id uuid NOT NULL DEFAULT uuid_generate_v4(),
|
|
user_id uuid,
|
|
interest text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_interests_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_interests_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.user_mrpiglr_site_badges (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
badge_id uuid NOT NULL,
|
|
unlocked_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT user_mrpiglr_site_badges_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_mrpiglr_site_badges_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT user_mrpiglr_site_badges_badge_id_fkey FOREIGN KEY (badge_id) REFERENCES public.mrpiglr_site_badges(id)
|
|
);
|
|
CREATE TABLE public.user_profiles (
|
|
id uuid NOT NULL,
|
|
username text UNIQUE,
|
|
full_name text,
|
|
avatar_url text,
|
|
user_type USER-DEFINED NOT NULL,
|
|
experience_level USER-DEFINED DEFAULT 'beginner'::experience_level_enum,
|
|
bio text,
|
|
location text,
|
|
website_url text,
|
|
github_url text,
|
|
twitter_url text,
|
|
linkedin_url text,
|
|
total_xp integer DEFAULT 0,
|
|
level integer DEFAULT 1,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
current_streak integer DEFAULT 0,
|
|
longest_streak integer DEFAULT 0,
|
|
last_streak_at date,
|
|
loyalty_points integer DEFAULT 0,
|
|
reputation_score integer DEFAULT 0,
|
|
wallet_address character varying DEFAULT NULL::character varying UNIQUE,
|
|
show_in_creator_directory boolean NOT NULL DEFAULT false,
|
|
arms ARRAY DEFAULT '{}'::text[],
|
|
roles ARRAY DEFAULT '{}'::text[],
|
|
last_active_at timestamp with time zone DEFAULT now(),
|
|
streak_days integer DEFAULT 0,
|
|
roblox_user_id text UNIQUE,
|
|
roblox_username text,
|
|
unity_player_id text UNIQUE,
|
|
unreal_player_id text UNIQUE,
|
|
godot_player_id text UNIQUE,
|
|
merged_to_user_id uuid,
|
|
aethex_domain text,
|
|
discord_id text UNIQUE,
|
|
discord_username text,
|
|
is_architect boolean DEFAULT false,
|
|
xp integer DEFAULT 0,
|
|
daily_streak integer DEFAULT 0,
|
|
last_daily timestamp with time zone,
|
|
last_xp_message timestamp with time zone,
|
|
badges jsonb DEFAULT '[]'::jsonb,
|
|
CONSTRAINT user_profiles_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id),
|
|
CONSTRAINT user_profiles_merged_to_user_id_fkey FOREIGN KEY (merged_to_user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.user_rewards (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
reward_id uuid NOT NULL,
|
|
claimed_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT user_rewards_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_rewards_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT user_rewards_reward_id_fkey FOREIGN KEY (reward_id) REFERENCES public.rewards(id)
|
|
);
|
|
CREATE TABLE public.user_roles (
|
|
user_id uuid NOT NULL,
|
|
role_id uuid NOT NULL,
|
|
CONSTRAINT user_roles_pkey PRIMARY KEY (user_id, role_id),
|
|
CONSTRAINT user_roles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT user_roles_role_id_fkey FOREIGN KEY (role_id) REFERENCES public.roles(id)
|
|
);
|
|
CREATE TABLE public.user_stats (
|
|
id integer NOT NULL DEFAULT nextval('user_stats_id_seq'::regclass),
|
|
user_id uuid,
|
|
guild_id character varying NOT NULL,
|
|
messages_sent integer DEFAULT 0,
|
|
reactions_given integer DEFAULT 0,
|
|
reactions_received integer DEFAULT 0,
|
|
voice_minutes integer DEFAULT 0,
|
|
commands_used integer DEFAULT 0,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_stats_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.user_titles (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid,
|
|
title_id uuid,
|
|
unlocked_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_titles_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_titles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT user_titles_title_id_fkey FOREIGN KEY (title_id) REFERENCES public.rewards(id)
|
|
);
|
|
CREATE TABLE public.user_wallets (
|
|
id bigint NOT NULL DEFAULT nextval('user_wallets_id_seq'::regclass),
|
|
user_id uuid NOT NULL,
|
|
wallet_address text NOT NULL UNIQUE,
|
|
chain_id integer NOT NULL,
|
|
chain_name text,
|
|
is_primary boolean DEFAULT false,
|
|
verified_at timestamp with time zone,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
updated_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT user_wallets_pkey PRIMARY KEY (id),
|
|
CONSTRAINT user_wallets_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id)
|
|
);
|
|
CREATE TABLE public.users (
|
|
id character varying NOT NULL DEFAULT (gen_random_uuid())::character varying,
|
|
username text NOT NULL UNIQUE,
|
|
password text NOT NULL,
|
|
is_active boolean NOT NULL DEFAULT true,
|
|
is_admin boolean NOT NULL DEFAULT false,
|
|
created_at timestamp without time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT users_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.voice_states (
|
|
discord_user_id character varying NOT NULL,
|
|
discord_username character varying NOT NULL,
|
|
channel_id character varying NOT NULL,
|
|
guild_id character varying NOT NULL,
|
|
joined_at timestamp with time zone DEFAULT now(),
|
|
last_updated timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT voice_states_pkey PRIMARY KEY (discord_user_id)
|
|
);
|
|
CREATE TABLE public.volunteer_applications (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
opportunity_id uuid NOT NULL,
|
|
message text,
|
|
status text NOT NULL DEFAULT 'pending'::text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT volunteer_applications_pkey PRIMARY KEY (id),
|
|
CONSTRAINT volunteer_applications_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id),
|
|
CONSTRAINT volunteer_applications_opportunity_id_fkey FOREIGN KEY (opportunity_id) REFERENCES public.volunteer_opportunities(id)
|
|
);
|
|
CREATE TABLE public.volunteer_opportunities (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
title text NOT NULL,
|
|
description text NOT NULL,
|
|
responsibilities ARRAY,
|
|
time_commitment text,
|
|
skills_required ARRAY,
|
|
is_active boolean NOT NULL DEFAULT true,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT volunteer_opportunities_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.volunteer_testimonials (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL,
|
|
role text NOT NULL,
|
|
testimonial text NOT NULL,
|
|
avatar_url text,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
CONSTRAINT volunteer_testimonials_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.waitlist (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
email text NOT NULL UNIQUE,
|
|
created_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
segment text,
|
|
site_id uuid,
|
|
CONSTRAINT waitlist_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.warnings (
|
|
id integer NOT NULL DEFAULT nextval('warnings_id_seq'::regclass),
|
|
guild_id text NOT NULL,
|
|
user_id text NOT NULL,
|
|
user_tag text,
|
|
moderator_id text NOT NULL,
|
|
moderator_tag text,
|
|
reason text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT warnings_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.web3_nonces (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
wallet_address text NOT NULL UNIQUE CHECK (wallet_address ~ '^0x[a-fA-F0-9]{40}$'::text),
|
|
nonce text NOT NULL,
|
|
used_at timestamp without time zone,
|
|
expires_at timestamp without time zone NOT NULL,
|
|
created_at timestamp without time zone DEFAULT now(),
|
|
CONSTRAINT web3_nonces_pkey PRIMARY KEY (id)
|
|
);
|
|
CREATE TABLE public.web3_wallets (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
wallet_address text NOT NULL UNIQUE CHECK (wallet_address ~ '^0x[a-fA-F0-9]{40}$'::text),
|
|
chain_id integer DEFAULT 1,
|
|
linked_at timestamp without time zone DEFAULT now(),
|
|
last_verified timestamp without time zone,
|
|
CONSTRAINT web3_wallets_pkey PRIMARY KEY (id),
|
|
CONSTRAINT web3_wallets_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(id)
|
|
);
|
|
CREATE TABLE public.work_sessions (
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL,
|
|
project_id uuid,
|
|
task_id uuid,
|
|
clock_in timestamp with time zone NOT NULL DEFAULT now(),
|
|
clock_out timestamp with time zone,
|
|
duration_minutes integer DEFAULT (EXTRACT(epoch FROM (clock_out - clock_in)) / (60)::numeric),
|
|
source character varying DEFAULT 'web'::character varying,
|
|
notes text,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT work_sessions_pkey PRIMARY KEY (id),
|
|
CONSTRAINT work_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id),
|
|
CONSTRAINT work_sessions_project_id_fkey FOREIGN KEY (project_id) REFERENCES public.gameforge_projects(id),
|
|
CONSTRAINT work_sessions_task_id_fkey FOREIGN KEY (task_id) REFERENCES public.gameforge_tasks(id)
|
|
);
|
|
CREATE TABLE public.xp_config (
|
|
id integer NOT NULL DEFAULT nextval('xp_config_id_seq'::regclass),
|
|
guild_id character varying NOT NULL UNIQUE,
|
|
xp_per_message integer DEFAULT 15,
|
|
xp_cooldown integer DEFAULT 60,
|
|
voice_xp_per_minute integer DEFAULT 5,
|
|
reaction_xp integer DEFAULT 5,
|
|
difficulty character varying DEFAULT 'normal'::character varying,
|
|
bonus_channels jsonb DEFAULT '[]'::jsonb,
|
|
role_multipliers jsonb DEFAULT '{}'::jsonb,
|
|
created_at timestamp with time zone DEFAULT now(),
|
|
CONSTRAINT xp_config_pkey PRIMARY KEY (id)
|
|
); |