Database Schema

// wrangler.toml
[[d1_databases]]
binding = "DB"
database_name = "orquestra-prod"
database_id = "7308ceeb-9f86-4948-a1bf-916d21078788"

# Development (local)
npm run db:migrate:dev

# Production (remote)
wrangler d1 execute orquestra-prod \
  --file ./migrations/001_initial_schema.sql --remote

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  github_id INTEGER UNIQUE NOT NULL,
  username TEXT UNIQUE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  avatar_url TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_github_id ON users(github_id);
CREATE INDEX idx_users_username ON users(username);

// Create user after GitHub OAuth
const user = await c.env.DB.prepare(`
  INSERT INTO users (id, github_id, username, email, avatar_url)
  VALUES (?, ?, ?, ?, ?)
`).bind(userId, githubId, username, email, avatarUrl).run()

CREATE TABLE IF NOT EXISTS projects (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  program_id TEXT NOT NULL,
  is_public BOOLEAN DEFAULT TRUE,
  custom_docs TEXT,  -- Added in migration 003
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE(user_id, program_id)
);

CREATE INDEX idx_projects_user_id ON projects(user_id);
CREATE INDEX idx_projects_program_id ON projects(program_id);
CREATE INDEX idx_projects_is_public ON projects(is_public);

// Create new project
const project = await c.env.DB.prepare(`
  INSERT INTO projects (id, user_id, name, description, program_id, is_public)
  VALUES (?, ?, ?, ?, ?, ?)
`).bind(projectId, userId, name, description, programId, true).run()

// Get user's projects
const projects = await c.env.DB.prepare(`
  SELECT * FROM projects WHERE user_id = ? ORDER BY created_at DESC
`).bind(userId).all()

CREATE TABLE IF NOT EXISTS idl_versions (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL,
  idl_json TEXT NOT NULL,
  cpi_md TEXT,
  version INTEGER NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX idx_idl_versions_project_id ON idl_versions(project_id);
CREATE INDEX idx_idl_versions_created_at ON idl_versions(created_at);

// Upload new IDL version
const version = await c.env.DB.prepare(`
  INSERT INTO idl_versions (id, project_id, idl_json, cpi_md, version)
  VALUES (?, ?, ?, ?, ?)
`).bind(versionId, projectId, JSON.stringify(idl), cpiMarkdown, 1).run()

// Get latest version
const latest = await c.env.DB.prepare(`
  SELECT * FROM idl_versions 
  WHERE project_id = ? 
  ORDER BY version DESC 
  LIMIT 1
`).bind(projectId).first()

CREATE TABLE IF NOT EXISTS api_keys (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL,
  key TEXT UNIQUE NOT NULL,
  last_used DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX idx_api_keys_project_id ON api_keys(project_id);
CREATE INDEX idx_api_keys_key ON api_keys(key);

// Create API key
const apiKey = await c.env.DB.prepare(`
  INSERT INTO api_keys (id, project_id, key, expires_at)
  VALUES (?, ?, ?, ?)
`).bind(keyId, projectId, hashedKey, expiresAt).run()

// Validate and update last_used
const key = await c.env.DB.prepare(`
  UPDATE api_keys SET last_used = CURRENT_TIMESTAMP
  WHERE key = ? AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)
  RETURNING *
`).bind(providedKey).first()

CREATE TABLE IF NOT EXISTS project_socials (
  id TEXT PRIMARY KEY,
  project_id TEXT UNIQUE NOT NULL,
  twitter TEXT,
  discord TEXT,
  telegram TEXT,
  github TEXT,
  website TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX idx_project_socials_project_id ON project_socials(project_id);

// Add social links
const socials = await c.env.DB.prepare(`
  INSERT INTO project_socials (id, project_id, twitter, discord, github, website)
  VALUES (?, ?, ?, ?, ?, ?)
  ON CONFLICT(project_id) DO UPDATE SET
    twitter = excluded.twitter,
    discord = excluded.discord,
    updated_at = CURRENT_TIMESTAMP
`).bind(id, projectId, twitter, discord, github, website).run()

CREATE TABLE IF NOT EXISTS known_addresses (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL,
  label TEXT NOT NULL,
  address TEXT NOT NULL,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE INDEX idx_known_addresses_project_id ON known_addresses(project_id);

// Add known address
const address = await c.env.DB.prepare(`
  INSERT INTO known_addresses (id, project_id, label, address, description)
  VALUES (?, ?, ?, ?, ?)
`).bind(id, projectId, 'Treasury', treasuryAddress, 'Main treasury account').run()

// Get all known addresses for project
const addresses = await c.env.DB.prepare(`
  SELECT * FROM known_addresses WHERE project_id = ?
`).bind(projectId).all()

┌─────────────┐
│    users    │
│  (1 user)   │
└──────┬──────┘
       │ 1:N
       ↓
┌─────────────┐
│  projects   │
│ (N projects)│
└──────┬──────┘
       │
       ├─────────────┬──────────────┬──────────────┬─────────────┐
       │ 1:N         │ 1:N          │ 1:1          │ 1:N         │
       ↓             ↓              ↓              ↓             ↓
┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐
│   idl    │  │   api    │  │ project  │  │  known   │  │ (future) │
│ versions │  │   keys   │  │ socials  │  │addresses │  │  tables  │
└──────────┘  └──────────┘  └──────────┘  └──────────┘  └──────────┘

// Store IDL in KV
await c.env.IDLS.put(
  `project:${projectId}:idl`,
  JSON.stringify(idl),
  { expirationTtl: 86400 } // 24 hour TTL
)

// Retrieve IDL from KV
const idlJson = await c.env.IDLS.get(`project:${projectId}:idl`)

// Cache API response
await c.env.CACHE.put(
  `api:${projectId}:instructions`,
  JSON.stringify(instructions),
  { expirationTtl: 3600 } // 1 hour TTL
)

// Retrieve from cache
const cached = await c.env.CACHE.get(`api:${projectId}:instructions`)

const userWithProjects = await c.env.DB.prepare(`
  SELECT 
    u.*,
    json_group_array(
      json_object(
        'id', p.id,
        'name', p.name,
        'program_id', p.program_id,
        'is_public', p.is_public
      )
    ) as projects
  FROM users u
  LEFT JOIN projects p ON u.id = p.user_id
  WHERE u.id = ?
  GROUP BY u.id
`).bind(userId).first()

const projectDetails = await c.env.DB.prepare(`
  SELECT 
    p.*,
    i.idl_json,
    i.version as idl_version,
    s.twitter,
    s.discord,
    s.github,
    s.website
  FROM projects p
  LEFT JOIN (
    SELECT * FROM idl_versions 
    WHERE project_id = ? 
    ORDER BY version DESC 
    LIMIT 1
  ) i ON p.id = i.project_id
  LEFT JOIN project_socials s ON p.id = s.project_id
  WHERE p.id = ?
`).bind(projectId, projectId).first()

const publicProjects = await c.env.DB.prepare(`
  SELECT 
    p.*,
    u.username,
    u.avatar_url,
    COUNT(i.id) as version_count
  FROM projects p
  INNER JOIN users u ON p.user_id = u.id
  LEFT JOIN idl_versions i ON p.id = i.project_id
  WHERE p.is_public = true
  GROUP BY p.id
  ORDER BY p.created_at DESC
  LIMIT ? OFFSET ?
`).bind(limit, offset).all()

wrangler d1 shell orquestra-dev

wrangler d1 shell orquestra-prod --remote

wrangler d1 export orquestra-prod --output backup.sql --remote

-- Count users
SELECT COUNT(*) FROM users;

-- Count projects
SELECT COUNT(*) FROM projects WHERE is_public = true;

-- Find recent uploads
SELECT p.name, i.version, i.created_at 
FROM idl_versions i
JOIN projects p ON i.project_id = p.id
ORDER BY i.created_at DESC
LIMIT 10;

-- Get user statistics
SELECT 
  u.username,
  COUNT(p.id) as project_count,
  COUNT(i.id) as version_count
FROM users u
LEFT JOIN projects p ON u.id = p.user_id
LEFT JOIN idl_versions i ON p.id = i.project_id
GROUP BY u.id;