Projeto 1: Diário de Tarefas Local

Objetivo: construir um diário de tarefas que funcione inteiramente localmente, sem depender de serviços remotos, com foco em consistência de dados e consultas eficientes offline.

Modelagem essencial

• Tabelas principais: projects (id, name, color) e tasks (id, title, notes, due_date, completed, project_id, created_at).
• Chaves: foreign key (tasks.project_id) referencia projects(id).
• Boas práticas: timestamps em created_at, uso de booleanos para completed, e data/hora no formato ISO para facilitar consultas.

Consultas-chave e fluxos

• Listar tarefas pendentes ordenadas por data de vencimento (quando houver):
• Filtrar por projeto: obter tarefas de um projeto específico.
• Resumo diário de tarefas concluídas.

-- Esquema
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  color TEXT DEFAULT '#cccccc'
);

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  notes TEXT,
  due_date TEXT,
  completed INTEGER NOT NULL DEFAULT 0,
  project_id INTEGER,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id)
);

-- Índices para desempenho
CREATE INDEX idx_tasks_due ON tasks(due_date);
CREATE INDEX idx_tasks_project ON tasks(project_id);

-- Exemplos de consultas
-- Tarefas pendentes
SELECT t.id, t.title, t.due_date
FROM tasks t
WHERE t.completed = 0
ORDER BY t.due_date IS NOT NULL, t.due_date;

-- Tarefas de um projeto específico
SELECT t.id, t.title
FROM tasks t
WHERE t.project_id = 2
ORDER BY t.created_at DESC;

Projeto 2: Catálogo de Livros Local com Busca Eficiente

Objetivo: manter um catálogo local com buscas por título, autor e notas, priorizando consultas rápidas e simples índices apropriados.

Esquema recomendado

• books(id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER, isbn TEXT, notes TEXT)

Consultas-chave

• Busca por título ou autor (LIKE):
• Filtro por ano de publicação (range):

-- Índices para buscas textuais simples
CREATE INDEX idx_books_title ON books(title);
CREATE INDEX idx_books_author ON books(author);

-- Consultas de exemplo
SELECT * FROM books WHERE title LIKE '%' || ? || '%' OR author LIKE '%' || ? || '%';
SELECT * FROM books WHERE year BETWEEN ? AND ? ORDER BY year DESC;

Observação: para buscas mais avançadas de texto, SQLite oferece extensões como FTS. Este post foca em SQLite puro para manter a simplicidade do ambiente local.

Projeto 3: Auditoria simples de Eventos (Logs Local)

Objetivo: registrar eventos da aplicação localmente para diagnóstico, com consultas por intervalo de tempo e por nível de severidade.

Modelo recomendado

• logs(id INTEGER PRIMARY KEY, ts TEXT, level TEXT, message TEXT)

Consultas úteis

• Eventos do último dia:
• Contagem por nível:

SELECT * FROM logs
WHERE ts >= datetime('now', '-1 day')
ORDER BY ts DESC;

SELECT level, COUNT(*) AS total
FROM logs
GROUP BY level;

Dicas: armazene timestamps em formato ISO (YYYY-MM-DD HH:MM:SS) para facilitar comparações e ordenação.

Projeto 4: Analytics Local com Views (Resumo por Dia)

Objetivo: expor dashboards simples por meio de consultas agregadas, usando views para centralizar lógicas de agregação sem depender de serviços externos.

Conceito de view

Exemplo de pattern comum: criar uma view diária a partir de uma tabela de eventos (events) com um campo created_at.

CREATE VIEW daily_summary AS
SELECT date(created_at) AS day, COUNT(*) AS total
FROM events
GROUP BY day;

-- Uso
SELECT * FROM daily_summary WHERE day >= date('now','-7 day');

Views ajudam a manter consultas reutilizáveis e simples, especialmente quando combinadas com índices nas tabelas subjacentes.