The update of the database of buidlerboard

[leeduckgo]

Now the op system is using the database of the AWS Cloud.

I think it will be better if we using supabase for instead.

It's more convenient & more low cost for data management.

And... The data for the buidler board is along from the others, so it's a good beginning!❤️

[leeduckgo]

Snippets for the Creating of Database:

-- Create ENUM type first
CREATE TYPE tag_type AS ENUM ('ECOSYSTEM', 'SECTOR');

CREATE TABLE ranking_tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  type tag_type NOT NULL,
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (name, type)
);

-- Create trigger to automatically update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_ranking_tags_updated_at
    BEFORE UPDATE ON ranking_tags
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE operation_developer (
  id SERIAL PRIMARY KEY,
  github_url VARCHAR(191) NOT NULL,
  login VARCHAR(191) DEFAULT NULL,
  name VARCHAR(191) DEFAULT NULL,
  bio TEXT,
  company VARCHAR(191) DEFAULT NULL,
  location VARCHAR(191) DEFAULT NULL,
  email VARCHAR(191) DEFAULT NULL,
  twitter_username VARCHAR(191) DEFAULT NULL,
  followers INTEGER DEFAULT NULL,
  following INTEGER DEFAULT NULL,
  public_repos INTEGER DEFAULT NULL,
  repos JSONB DEFAULT NULL,
  created_at TIMESTAMP(3) DEFAULT NULL,
  updated_at TIMESTAMP(3) DEFAULT NULL,
  ecosystems JSONB DEFAULT NULL,
  sectors JSONB DEFAULT NULL,
  created_at_internal TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at_internal TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (github_url),
  UNIQUE (login)
);

-- Create trigger to automatically update updated_at_internal
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at_internal = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_operation_developer_updated_at
    BEFORE UPDATE ON operation_developer
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE operation_project (
  id SERIAL PRIMARY KEY,
  repo_url VARCHAR(191) NOT NULL,
  full_name VARCHAR(191) DEFAULT NULL,
  description TEXT,
  stargazers_count INTEGER DEFAULT NULL,
  forks_count INTEGER DEFAULT NULL,
  languages JSONB DEFAULT NULL,
  contributors JSONB DEFAULT NULL,
  created_at TIMESTAMP(3) DEFAULT NULL,
  updated_at TIMESTAMP(3) DEFAULT NULL,
  ecosystems JSONB DEFAULT NULL,
  sectors JSONB DEFAULT NULL,
  created_at_internal TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at_internal TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (repo_url),
  UNIQUE (full_name)
);

-- Create trigger to automatically update updated_at_internal
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at_internal = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_operation_project_updated_at
    BEFORE UPDATE ON operation_project
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TABLE operation_crawl_record (
  id SERIAL PRIMARY KEY,
  day VARCHAR(10) NOT NULL,
  result JSONB DEFAULT NULL, -- COMMENT 'Store crawler results'
  is_running BOOLEAN NOT NULL DEFAULT false,
  current_type VARCHAR(10) DEFAULT NULL, -- COMMENT 'ALL | PROJECTS | DEVELOPERS'
  start_time TIMESTAMP DEFAULT NULL,
  current_progress JSONB DEFAULT NULL, -- COMMENT '{ total: number, current: number }'
  last_error TEXT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (day)
);

-- Create trigger to automatically update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_operation_crawl_record_updated_at
    BEFORE UPDATE ON operation_crawl_record
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();