Skip to content

Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS

9.4 KB
Raw Blame
// Stacker News community stewardship. Configuration, untrusted snapshots,
// analysis provenance, and the review ledger are kept separate from encrypted
// credentials so no list/read path can accidentally expose a secret.
export const stackerNewsDdl = [
  `CREATE TABLE IF NOT EXISTS stacker_news_accounts (
    id UUID PRIMARY KEY,
    label TEXT NOT NULL,
    username TEXT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    monitoring_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    monitoring_interval_minutes INTEGER NOT NULL DEFAULT 30 CHECK (monitoring_interval_minutes BETWEEN 5 AND 1440),
    sync_item_limit INTEGER NOT NULL DEFAULT 30 CHECK (sync_item_limit BETWEEN 1 AND 100),
    analysis_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    text_model TEXT NOT NULL DEFAULT '',
    vision_model TEXT NOT NULL DEFAULT '',
    rules JSONB NOT NULL DEFAULT '{}'::jsonb,
    policy_version TEXT NOT NULL DEFAULT 'v1',
    read_transport TEXT NOT NULL DEFAULT 'browser' CHECK (read_transport IN ('browser','api')),
    last_sync_at TIMESTAMPTZ,
    last_error TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (username)
  )`,
  `CREATE UNIQUE INDEX IF NOT EXISTS idx_stacker_news_accounts_username_ci ON stacker_news_accounts (LOWER(username))`,
  `ALTER TABLE stacker_news_accounts ADD COLUMN IF NOT EXISTS monitoring_interval_minutes INTEGER NOT NULL DEFAULT 30 CHECK (monitoring_interval_minutes BETWEEN 5 AND 1440)`,
  `ALTER TABLE stacker_news_accounts ADD COLUMN IF NOT EXISTS sync_item_limit INTEGER NOT NULL DEFAULT 30 CHECK (sync_item_limit BETWEEN 1 AND 100)`,
  `ALTER TABLE stacker_news_accounts ADD COLUMN IF NOT EXISTS analysis_enabled BOOLEAN NOT NULL DEFAULT FALSE`,
  `ALTER TABLE stacker_news_accounts ADD COLUMN IF NOT EXISTS policy_version TEXT NOT NULL DEFAULT 'v1'`,
  `ALTER TABLE stacker_news_accounts ADD COLUMN IF NOT EXISTS last_sync_at TIMESTAMPTZ`,
  `ALTER TABLE stacker_news_accounts ADD COLUMN IF NOT EXISTS last_error TEXT NOT NULL DEFAULT ''`,
  // Reads default to the signed-in pinned browser: Stacker News grants API keys
  // only on request, so a key-gated read path leaves a fresh install unable to
  // sync at all. Existing installs pick this up with no manual step.
  `ALTER TABLE stacker_news_accounts ADD COLUMN IF NOT EXISTS read_transport TEXT NOT NULL DEFAULT 'browser' CHECK (read_transport IN ('browser','api'))`,
  `CREATE TABLE IF NOT EXISTS stacker_news_credentials (
    account_id UUID PRIMARY KEY REFERENCES stacker_news_accounts (id) ON DELETE CASCADE,
    api_key_enc TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
  )`,
  `CREATE TABLE IF NOT EXISTS stacker_news_territories (
    id UUID PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES stacker_news_accounts (id) ON DELETE CASCADE,
    slug TEXT NOT NULL,
    label TEXT NOT NULL DEFAULT '',
    is_owned BOOLEAN NOT NULL DEFAULT FALSE,
    monitoring_enabled BOOLEAN,
    inherit_account_rules BOOLEAN NOT NULL DEFAULT TRUE,
    rules JSONB NOT NULL DEFAULT '{}'::jsonb,
    remote_settings JSONB NOT NULL DEFAULT '{}'::jsonb,
    remote_refreshed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (account_id, slug)
  )`,
  `CREATE INDEX IF NOT EXISTS idx_stacker_news_territories_account ON stacker_news_territories (account_id)`,
  `ALTER TABLE stacker_news_territories ADD COLUMN IF NOT EXISTS monitoring_enabled BOOLEAN`,
  `ALTER TABLE stacker_news_territories ADD COLUMN IF NOT EXISTS inherit_account_rules BOOLEAN NOT NULL DEFAULT TRUE`,
  `ALTER TABLE stacker_news_territories ADD COLUMN IF NOT EXISTS remote_refreshed_at TIMESTAMPTZ`,
  `CREATE TABLE IF NOT EXISTS stacker_news_items (
    id UUID PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES stacker_news_accounts (id) ON DELETE CASCADE,
    territory_id UUID REFERENCES stacker_news_territories (id) ON DELETE SET NULL,
    remote_id TEXT NOT NULL,
    kind TEXT NOT NULL,
    author_name TEXT NOT NULL DEFAULT '',
    title TEXT NOT NULL DEFAULT '',
    body TEXT NOT NULL DEFAULT '',
    source_url TEXT NOT NULL DEFAULT '',
    image_urls JSONB NOT NULL DEFAULT '[]'::jsonb,
    content_hash TEXT NOT NULL,
    remote_created_at TIMESTAMPTZ,
    remote_updated_at TIMESTAMPTZ,
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (account_id, remote_id)
  )`,
  `CREATE INDEX IF NOT EXISTS idx_stacker_news_items_account_received ON stacker_news_items (account_id, received_at DESC)`,
  `ALTER TABLE stacker_news_items ADD COLUMN IF NOT EXISTS remote_created_at TIMESTAMPTZ`,
  `ALTER TABLE stacker_news_items ADD COLUMN IF NOT EXISTS remote_updated_at TIMESTAMPTZ`,
  `CREATE INDEX IF NOT EXISTS idx_stacker_news_items_account_remote_created ON stacker_news_items (account_id, remote_created_at DESC NULLS LAST, received_at DESC)`,
  `CREATE TABLE IF NOT EXISTS stacker_news_media (
    id UUID PRIMARY KEY,
    item_id UUID NOT NULL REFERENCES stacker_news_items (id) ON DELETE CASCADE,
    source_url_hash TEXT NOT NULL,
    content_hash TEXT NOT NULL DEFAULT '',
    mime_type TEXT NOT NULL DEFAULT '',
    width INTEGER,
    height INTEGER,
    byte_length INTEGER,
    status TEXT NOT NULL DEFAULT 'pending',
    error TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (item_id, source_url_hash)
  )`,
  `CREATE INDEX IF NOT EXISTS idx_stacker_news_media_item ON stacker_news_media (item_id, created_at DESC)`,
  `CREATE TABLE IF NOT EXISTS stacker_news_analyses (
    id UUID PRIMARY KEY,
    item_id UUID NOT NULL REFERENCES stacker_news_items (id) ON DELETE CASCADE,
    stage TEXT NOT NULL,
    provider TEXT NOT NULL DEFAULT 'deterministic',
    model TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'completed',
    source_content_hash TEXT NOT NULL,
    rules_hash TEXT NOT NULL DEFAULT '',
    policy_version TEXT NOT NULL DEFAULT 'v1',
    result JSONB NOT NULL DEFAULT '{}'::jsonb,
    moderator_feedback TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ DEFAULT NOW()
  )`,
  `CREATE INDEX IF NOT EXISTS idx_stacker_news_analyses_item ON stacker_news_analyses (item_id, created_at DESC)`,
  `ALTER TABLE stacker_news_analyses ADD COLUMN IF NOT EXISTS status TEXT NOT NULL DEFAULT 'completed'`,
  `ALTER TABLE stacker_news_analyses ADD COLUMN IF NOT EXISTS source_content_hash TEXT NOT NULL DEFAULT ''`,
  `ALTER TABLE stacker_news_analyses ADD COLUMN IF NOT EXISTS rules_hash TEXT NOT NULL DEFAULT ''`,
  `ALTER TABLE stacker_news_analyses ADD COLUMN IF NOT EXISTS policy_version TEXT NOT NULL DEFAULT 'v1'`,
  `ALTER TABLE stacker_news_analyses ADD COLUMN IF NOT EXISTS moderator_feedback TEXT NOT NULL DEFAULT ''`,
  `CREATE TABLE IF NOT EXISTS stacker_news_actions (
    id UUID PRIMARY KEY,
    account_id UUID NOT NULL REFERENCES stacker_news_accounts (id) ON DELETE CASCADE,
    item_id UUID REFERENCES stacker_news_items (id) ON DELETE SET NULL,
    territory_id UUID REFERENCES stacker_news_territories (id) ON DELETE SET NULL,
    kind TEXT NOT NULL,
    state TEXT NOT NULL DEFAULT 'draft',
    destination TEXT NOT NULL DEFAULT '',
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    source_content_hash TEXT NOT NULL DEFAULT '',
    rules_hash TEXT NOT NULL DEFAULT '',
    policy_version TEXT NOT NULL DEFAULT 'v1',
    idempotency_key TEXT NOT NULL,
    reviewed_target JSONB NOT NULL DEFAULT '{}'::jsonb,
    review_note TEXT NOT NULL DEFAULT '',
    result JSONB NOT NULL DEFAULT '{}'::jsonb,
    error TEXT NOT NULL DEFAULT '',
    approved_at TIMESTAMPTZ,
    executed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
  )`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS destination TEXT NOT NULL DEFAULT ''`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS source_content_hash TEXT NOT NULL DEFAULT ''`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS rules_hash TEXT NOT NULL DEFAULT ''`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS policy_version TEXT NOT NULL DEFAULT 'v1'`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS idempotency_key TEXT`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS reviewed_target JSONB NOT NULL DEFAULT '{}'::jsonb`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS result JSONB NOT NULL DEFAULT '{}'::jsonb`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS error TEXT NOT NULL DEFAULT ''`,
  `ALTER TABLE stacker_news_actions ADD COLUMN IF NOT EXISTS approved_at TIMESTAMPTZ`,
  `DROP INDEX IF EXISTS idx_stacker_news_actions_idempotency_key`,
  `CREATE UNIQUE INDEX IF NOT EXISTS idx_stacker_news_actions_active_idempotency_key ON stacker_news_actions (idempotency_key) WHERE state IN ('pending_review','approved','executing')`,
  `CREATE INDEX IF NOT EXISTS idx_stacker_news_actions_account_state ON stacker_news_actions (account_id, state, created_at DESC)`,
  `CREATE TABLE IF NOT EXISTS stacker_news_action_events (
    id UUID PRIMARY KEY,
    action_id UUID NOT NULL REFERENCES stacker_news_actions (id) ON DELETE CASCADE,
    from_state TEXT,
    to_state TEXT NOT NULL,
    note TEXT NOT NULL DEFAULT '',
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
  )`,
  `CREATE INDEX IF NOT EXISTS idx_stacker_news_action_events_action ON stacker_news_action_events (action_id, created_at ASC)`,
];