Заметки и сниппеты

SELECT string_agg(column_name, ', ' ORDER BY ordinal_position) AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'workflow_costs';

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'llm_generated_articles'
ORDER BY ordinal_position;

WITH deleted AS (
  DELETE FROM llm_generated_articles
  WHERE article_id = 'ARTICLE_UUID'
    AND batch_id = 'BATCH_ID'
  RETURNING *
)
INSERT INTO llm_generated_articles_archive
SELECT * FROM deleted;

WITH restored AS (
  DELETE FROM llm_generated_articles_archive
  WHERE article_id = 'ARTICLE_UUID'
    AND batch_id = 'BATCH_ID'
  RETURNING *
)
INSERT INTO llm_generated_articles
SELECT * FROM restored;

SELECT
  run_id,
  MIN(created_at) AS начало,
  MAX(created_at) AS конец,
  MAX(created_at)::timestamp - MIN(created_at)::timestamp AS длительность,
  COUNT(*)        AS вызовов_llm,
  SUM(input_tokens)        AS input_tok,
  SUM(output_tokens)       AS output_tok,
  ROUND(SUM(estimated_cost_usd)::numeric, 4) AS стоимость_usd
FROM llm_usage_log
WHERE run_id = (
  SELECT run_id FROM llm_usage_log
  ORDER BY created_at DESC
  LIMIT 1
)
GROUP BY run_id;

SELECT
  ROUND(SUM(estimated_cost_usd)::numeric, 4) AS total_cost_usd,
  SUM(input_tokens)                          AS total_in_tok,
  SUM(output_tokens)                         AS total_out_tok,
  COUNT(DISTINCT run_id)                     AS distinct_runs,
  COUNT(*)                                   AS total_llm_calls
FROM llm_usage_log
WHERE created_at >= CURRENT_DATE;

SELECT
  channel_handle,
  COUNT(*)                                              AS videos,
  COUNT(*) FILTER (WHERE view_count IS NOT NULL)        AS with_metrics,
  COUNT(*) FILTER (WHERE raw_subtitles IS NOT NULL)     AS with_subs,
  COUNT(*) FILTER (WHERE processing_status = 'scraped') AS scraped,
  COUNT(*) FILTER (WHERE processing_status = 'skipped') AS skipped,
  COUNT(*) FILTER (WHERE score IS NOT NULL)             AS scored,
  MIN(published_at)::date                               AS oldest,
  MAX(published_at)::date                               AS newest
FROM youtube_knowledge
GROUP BY channel_handle
ORDER BY videos DESC;

SELECT
  COUNT(*)                                                 AS total,
  COUNT(*) FILTER (WHERE title IS NULL OR title = '')      AS no_title,
  COUNT(*) FILTER (WHERE view_count IS NULL)               AS no_views,
  COUNT(*) FILTER (WHERE raw_subtitles IS NULL
                    AND processing_status = 'scraped')     AS scraped_but_no_subs
FROM youtube_knowledge
WHERE channel_handle = 'elton_labs';

SELECT video_id, score, published_at::date AS published, title
FROM youtube_knowledge
WHERE score >= 7
  AND channel_handle = 'elton_labs'
ORDER BY score DESC, published_at DESC;

SELECT string_agg(video_id, ',') AS ids
FROM youtube_knowledge
WHERE score >= 7
  AND channel_handle = 'elton_labs';

SELECT score, COUNT(*) AS videos
FROM youtube_knowledge
WHERE channel_handle = 'elton_labs'
  AND score IS NOT NULL
GROUP BY score
ORDER BY score DESC;

UPDATE youtube_knowledge
SET score = NULL,
    score_reason = NULL,
    score_weaknesses = NULL,
    processing_status = 'scraped'
WHERE channel_handle = 'elton_labs';

DELETE FROM youtube_knowledge
WHERE channel_handle = 'elton_labs'
  AND raw_subtitles IS NULL;

SELECT channel_handle, channel_id, niche,
       scorer_skill_name,
       length(scorer_system_prompt) AS prompt_len,
       is_active, scrape_status
FROM ytk_channels
ORDER BY channel_handle;

INSERT INTO ytk_channels (
  channel_id, channel_handle, channel_title,
  scorer_skill_name, niche, scorer_system_prompt
) VALUES (
  'UC...',
  'channel_handle',
  'Channel Title',
  'youtube-scorer-NAME-v1',
  'niche-slug',
  $prompt$Ты — асессор видео канала ...
ШКАЛА ОЦЕНОК: ...
Выдай JSON: {"score": 1-10, "weaknesses": "...", "reason": "..."}$prompt$
);

SELECT workflow_name, total_tokens, total_cost_usd, status, created_at
FROM workflow_costs
WHERE category = 'youtube-knowledge-scoring'
ORDER BY created_at DESC
LIMIT 10;

function Push-Site {
    param([Parameter(Mandatory)][string]$Name)
    $local = "$HOME\Sites\$Name\index.html"
    if (-not (Test-Path $local)) {
        Write-Host "Файл не найден: $local" -ForegroundColor Red
        return
    }
    scp $local "n8n:/opt/static/$Name/index.html"
    if ($LASTEXITCODE -eq 0) {
        Write-Host "✓ https://$Name.peniaze-ai.work" -ForegroundColor Green
    }
}

function Edit-Site {
    param([Parameter(Mandatory)][string]$Name)
    notepad "$HOME\Sites\$Name\index.html"
}

docker exec n8n-caddy-1 caddy reload --config /etc/caddy/Caddyfile

# Записать всю сессию в файл
Start-Transcript -Path "$HOME\Downloads\diag-$(Get-Date -Format 'yyyyMMdd-HHmm').log"

# ... ваши ssh-команды ...

Stop-Transcript

# Затем искать в файле:
Select-String -Path "$HOME\Downloads\diag-*.log" -Pattern "UA='Mozilla" -Context 0,200

ssh n8n 'bash -s' < commands.sh `
  | Tee-Object -FilePath "$HOME\Downloads\out.log"