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
JavaScript ai-agentsdashboarddeveloper-toolsdigital-twinquantified-selfsecond-brainself-hostedtailscale
// 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)`,
];