Construindo um Pipeline de Canonicalização de URLs de Vídeo
Executei uma consulta no meu banco de dados SQLite de produção e encontrei um problema enorme.
A tabela de vídeos tinha 41.283 linhas, mas apenas 29.000 títulos únicos. Estávamos armazenando os mesmos vídeos várias vezes.
Por que isso aconteceu? O mesmo vídeo do YouTube chegava em diferentes formatos de URL:
• Links curtos: youtu.be/ID • Links de desktop: youtube.com/watch?v=ID • Links de mobile: m.youtube.com/watch?v=ID • Shorts: youtube.com/shorts/ID
Um vídeo significava quatro linhas no banco de dados e quatro páginas web quase idênticas. Isso desperdiçou meu orçamento de rastreamento (crawl budget) e gerou erros no Google Search Console.
Eu administro o TrendVidStream. Coletamos dados de tendências de oito regiões diferentes. Cada região pode apresentar o mesmo vídeo viral usando formatos de URL diferentes.
Construí um pipeline para corrigir isso usando PHP 8.4 e SQLite.
Veja como o pipeline funciona:
- Extrair: Transformar qualquer variante de URL em um ID de vídeo estável de 11 caracteres.
- Normalizar: Criar uma única URL canônica a partir desse ID.
- Validar: Executar verificações rápidas antes de tocar no banco de dados.
- Upsert: Usar o UPSERT do SQLite para mesclar dados em vez de adicionar novas linhas.
- Emitir: Adicionar tags canônicas e redirecionamentos 301 para que os mecanismos de busca encontrem uma única URL.
Principais decisões técnicas:
• Use uma lista de permissões (allowlist) de host exata. Não use correspondência de sufixo. Isso evita riscos de segurança. • Nunca transforme os IDs em letras minúsculas. Os IDs do YouTube diferenciam maiúsculas de minúsculas (case-sensitive). Transformá-los em minúsculas mescla vídeos diferentes. • Use uma restrição UNIQUE no video_id. Esta é a sua defesa mais forte contra duplicatas. • Use o modo WAL do SQLite. Isso torna as gravações rápidas e confiáveis.
O resultado:
Nossa tabela de vídeos caiu de 41.283 linhas para 28.094 linhas. Não perdemos nenhum vídeo. Os erros no Google Search Console caíram de 412 para 9. Os resultados de pesquisa ficaram mais limpos porque paramos de mostrar o mesmo vídeo quatro vezes.
A lição é simples: quando você agrega dados de muitas fontes, construa a identidade no seu sistema primeiro. Extraia um ID estável, aplique-o com restrições de banco de dados e torne cada gravação idempotente.
Você não precisa de ferramentas complexas. PHP e SQLite são suficientes.