This repository has been archived on 2026-11-17. You can view files and clone it, but cannot push or open issues or pull requests.
gitgram-ai/aitimemachine/api-server/drizzle/0004_add_gitgram_social_layer.sql
caelum0x 49b183818c Unify gitgram + aitimemachine into the monorepo (de-submodule) and merge vendored engines
Convert gitgram/ and aitimemachine/ from embedded git repos (which showed as
opaque submodule gitlinks on GitHub) into fully-tracked first-party content.

AITM now merges its 7 vendored repos as first-party engines:
- memory: beads (Go task DAG) + context7 (docs SDK), unified into saved_contexts
- git: vendored C git (bundled binary) + gitnexus (in-process code intelligence)
- agentic: a2a (@a2a-js/sdk) + pal (AITM-managed Python subprocess)

Includes the full session's product build: Gitgram web/mobile/backend features,
AITM website/CLI/MCP, the unified memory layer, engine build tooling
(scripts/build-engines.sh, Dockerfile, CI), docs (README, ENGINES.md, ARCHITECTURE.md),
and extensive test suites.

Build artifacts, node_modules, external/, and gemini-cli are gitignored.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
2026-06-23 14:14:09 +03:00

127 lines
5.3 KiB
SQL

-- Migration: Canonical v1 social layer for Gitgram
-- Source: ../../../../CANONICAL_SQL_SCHEMA_V1.sql
-- Notes:
-- 1) Existing users/repositories/projects tables use text IDs.
-- 2) Social tables are additive and reference existing entities.
-- Canonical membership mapping (kept separate from existing org_members)
CREATE TABLE IF NOT EXISTS organization_memberships (
organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role text NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
created_at timestamp DEFAULT now(),
PRIMARY KEY (organization_id, user_id)
);
-- Canonical project/repository mapping table
CREATE TABLE IF NOT EXISTS project_repositories (
project_id text NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
repository_id text NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
created_at timestamp DEFAULT now(),
PRIMARY KEY (project_id, repository_id)
);
CREATE TABLE IF NOT EXISTS repo_profiles (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
repository_id text NOT NULL UNIQUE REFERENCES repositories(id) ON DELETE CASCADE,
headline text,
bio text,
hero_media_url text,
visibility text NOT NULL DEFAULT 'public'
CHECK (visibility IN ('public', 'unlisted', 'private')),
created_at timestamp DEFAULT now(),
updated_at timestamp DEFAULT now()
);
CREATE TABLE IF NOT EXISTS posts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
author_user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
repository_id text REFERENCES repositories(id) ON DELETE SET NULL,
project_id text REFERENCES projects(id) ON DELETE SET NULL,
type text NOT NULL CHECK (type IN ('post', 'reel', 'story', 'change_story', 'snippet')),
caption text,
code_snippet text,
language text,
linked_checkpoint_id text REFERENCES checkpoints(id) ON DELETE SET NULL,
visibility text NOT NULL DEFAULT 'public'
CHECK (visibility IN ('public', 'followers', 'private')),
published_at timestamp,
created_at timestamp DEFAULT now(),
updated_at timestamp DEFAULT now()
);
CREATE TABLE IF NOT EXISTS post_assets (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
post_id uuid NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
media_type text NOT NULL CHECK (media_type IN ('image', 'video', 'gif')),
storage_key text NOT NULL,
mime_type text,
width integer,
height integer,
duration_ms integer,
position integer NOT NULL DEFAULT 0,
created_at timestamp DEFAULT now()
);
CREATE TABLE IF NOT EXISTS comments (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
post_id uuid NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
author_user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
parent_comment_id uuid REFERENCES comments(id) ON DELETE CASCADE,
body text NOT NULL,
created_at timestamp DEFAULT now(),
updated_at timestamp DEFAULT now()
);
CREATE TABLE IF NOT EXISTS reactions (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
target_type text NOT NULL CHECK (target_type IN ('post', 'comment', 'repository')),
target_id text NOT NULL,
reaction text NOT NULL CHECK (reaction IN ('like', 'love', 'fire', 'clap', 'rocket')),
created_at timestamp DEFAULT now(),
UNIQUE (user_id, target_type, target_id, reaction)
);
CREATE TABLE IF NOT EXISTS follows (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
follower_user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
target_type text NOT NULL CHECK (target_type IN ('user', 'repository')),
target_user_id text REFERENCES users(id) ON DELETE CASCADE,
target_repository_id text REFERENCES repositories(id) ON DELETE CASCADE,
created_at timestamp DEFAULT now(),
CHECK (
(target_type = 'user' AND target_user_id IS NOT NULL AND target_repository_id IS NULL) OR
(target_type = 'repository' AND target_repository_id IS NOT NULL AND target_user_id IS NULL)
)
);
CREATE TABLE IF NOT EXISTS trend_scores (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
repository_id text NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
window_start timestamp NOT NULL,
window_end timestamp NOT NULL,
score numeric(12,4) NOT NULL,
signals jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamp DEFAULT now(),
UNIQUE (repository_id, window_start, window_end)
);
-- Indexes
CREATE INDEX IF NOT EXISTS idx_project_repo_repo ON project_repositories(repository_id);
CREATE INDEX IF NOT EXISTS idx_posts_repo_created ON posts(repository_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_posts_author_created ON posts(author_user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_comments_post_created ON comments(post_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_reactions_target ON reactions(target_type, target_id);
CREATE INDEX IF NOT EXISTS idx_follows_target_user ON follows(target_user_id);
CREATE INDEX IF NOT EXISTS idx_follows_target_repo ON follows(target_repository_id);
CREATE INDEX IF NOT EXISTS idx_trend_scores_repo_window ON trend_scores(repository_id, window_end DESC);
CREATE UNIQUE INDEX IF NOT EXISTS uq_follows_user_target
ON follows(follower_user_id, target_user_id)
WHERE target_type = 'user';
CREATE UNIQUE INDEX IF NOT EXISTS uq_follows_repo_target
ON follows(follower_user_id, target_repository_id)
WHERE target_type = 'repository';