# Solicitações LAI (e-SIC) — Modelagem de Dados

> Fonte: banco `prefeituras_v5` (PostgreSQL 10) da imagem `v6_6f22e1bd-postgres-1`, lido em modo somente-leitura com `\d` e `information_schema`.

---

## 1. Tabela principal: `informations_requests`

Criada por `v6/database/migrations/2018_04_13_192114_create_informations_requests_table.php`
e alterada por `v6/database/migrations/2025_10_14_154644_add_mask_request_to_informations_request_table.php`.

| Coluna | Tipo (PostgreSQL) | Null | Default | Origem |
|---|---|---|---|---|
| `id` | `integer` | não | `nextval('informations_requests_id_seq')` | `increments('id')` |
| `created_at` | `timestamp(0) without time zone` | sim | — | `timestamps()` |
| `updated_at` | `timestamp(0) without time zone` | sim | — | `timestamps()` |
| `deleted_at` | `timestamp(0) without time zone` | sim | — | `softDeletes()` |
| `request` | `text` | **não** | — | `longText("request")` — texto original do pedido |
| `ip` | `character varying(191)` | **não** | — | `string("ip")` — IP do solicitante |
| `browser` | `character varying(191)` | **não** | — | `string("browser")` — User-Agent |
| `protocol` | `character varying(191)` | **não** | — | `string("protocol")` — `date('YmdHis')` (14 dígitos) |
| `persons_id` | `integer` | sim | — | FK → `persons.id` |
| `status_id` | `integer` | sim | — | FK → `statuses.id` |
| `mask_request` | `text` | sim | — | migration de 14/10/2025 — texto mascarado (LGPD) |

Chaves e índices (verificado com `\d`):

| Objeto | Definição |
|---|---|
| PK | `informations_requests_pkey` — `btree (id)` |
| FK | `informations_requests_persons_id_foreign` → `persons(id)` |
| FK | `informations_requests_status_id_foreign` → `statuses(id)` |
| Referenciada por | `answers.informations_request_id` → `informations_requests(id)` |

Observações de modelagem relevantes:

- `ip`, `browser`, `protocol` e `request` são **NOT NULL**, mas a migration
  original os define como `string`/`longText` sem `nullable` — o banco real
  herdou esse estado de instalações antigas.
- Não existe índice próprio em `protocol`; a unicidade é garantida apenas em
  tempo de aplicação pela regra `unique:informations_requests`.
- Não há colunas de prazo (`due_date`), de data de resposta, de data de
  prorrogação nem de justificativa de prorrogação. A prorrogação é
  representa apenas por `status_id = 4` e por `updated_at`.
- `deleted_at` nunca é preenchido pelo módulo (o `SoftDeletes` está no model,
  mas não há rota de exclusão funcional: `destroy()` está vazio).

## 2. Tabelas de apoio

### 2.1 `statuses` (e `status_categories`)

`v6/database/migrations/2018_04_02_181803_create_statuses_table.php` e
`v6/database/migrations/2018_04_02_181799_create_status_categories_table.php`.

Conteúdo real da tabela `statuses` (consulta ao banco):

| id | name | status_category_id |
|---|---|---|
| 1 | Em Tramitação | 1 (Solicitação de Informação) |
| 2 | Atendido | 1 |
| 3 | Indeferido | 1 |
| 4 | Prorrogado | 1 |
| 5 | Aberta | 2 (Licitações) |
| 6 | Finalizada | 2 |
| 7 | Cancelada | 2 |

Os status 1..4 são os do e-SIC; 5..7 pertencem ao módulo de Licitações e
compartilham a mesma tabela.

### 2.2 `persons`, `addresses`, `emails`, `phones`

Colunas usadas pelo módulo (verificado com `\d`):

| Tabela | Colunas relevantes | Observação |
|---|---|---|
| `persons` | `id`, `cpf`, `rg`, `name`, `cnpj`, `social_name`, `fantasy_name`, `email`, `phone`, `users_id`, `created_at`, `updated_at`, `deleted_at` | `softDeletes` |
| `addresses` | `id`, `persons_id`, `addresses_type_id`, `street_name`, `street_number`, `zip_code` (integer), `complement`, `neighborhood`, `cities_id` | `zip_code` é `integer` no banco, mas gravado como string pelo controller |
| `emails` | `id`, `emails_type_id`, `persons_id`, `email_address` | |
| `phones` | `id`, `phones_type_id`, `persons_id`, `area_code`, `phone_number` | |

Cada solicitação cria **um novo conjunto** `persons` + `addresses` + `emails` +
`phones` — não há reaproveitamento por CPF/CNPJ, o que gera duplicidade
crescente de titulares.

### 2.3 `answers` (respostas)

`v6/database/migrations/2018_04_13_192115_create_answers_table.php`.

| Coluna | Tipo | Null | FK |
|---|---|---|---|
| `id` | `integer` | não | PK |
| `created_at` / `updated_at` / `deleted_at` | `timestamp(0)` | sim | — |
| `answer` | `text` | não | — |
| `archives_id` | `integer` | sim | → `archives(id)` |
| `informations_request_id` | `integer` | não | → `informations_requests(id)` |

### 2.4 `answer_archives` (pivot) e `archives`

`v6/database/migrations/2019_08_06_171403_create_answer_archives_table.php`:
`id`, `answer_id` (FK `answers.id`), `archive_id` (FK `archives.id`).

`archives` (tabela compartilhada com todo o portal) tem, entre outras colunas:
`id`, `title`, `description`, `type`, `hash`, `path`, `name`, `extension`,
`status_create`, `highlight`, `view_pdf`, `contents_id`, `static_pages_id`,
`biddings_id`, `calendar_events_id`, `requerimento_id`, `tv_camara_id`,
`highlight_id`, `type_size_id`, `width`, `height`.

O anexo da LAI é gravado com `path = url("storage/answer/{id}/{nome}")` e
`storeAs("answer/{id}/", $nome)` (disco `public`, isto é, o diretório
`storage/app/public/answer/` com uma subpasta por resposta).

## 3. Views SQL

Criadas e recriadas por migrations; a definição final no banco é a de
`v6/database/migrations/2023_05_29_100113_em_tramitacao_formato_24_horas.php`
(idem `v6/database/migrations/2023_05_29_100519_atendidos_formato_24_horas.php`,
`v6/database/migrations/2023_05_29_100322_prorrogados_formato_24_horas.php`,
`v6/database/migrations/2023_05_29_100935_indeferidos_formato_24_horas.php`).

| View | Migration que a define | Filtro |
|---|---|---|
| `em_tramitacao` | `v6/database/migrations/2023_05_29_100113_em_tramitacao_formato_24_horas.php` | `deleted_at is null and status_id = 1` |
| `atendidos` | `v6/database/migrations/2023_05_29_100519_atendidos_formato_24_horas.php` | `deleted_at is null and status_id = 2` |
| `indeferidos` | `v6/database/migrations/2023_05_29_100935_indeferidos_formato_24_horas.php` | `deleted_at is null and status_id = 3` |
| `prorrogados` | `v6/database/migrations/2023_05_29_100322_prorrogados_formato_24_horas.php` | `deleted_at is null and status_id = 4` |

Definição (extraída de `pg_views` no banco):

```sql
select row_number() over (order by ir.created_at desc) as value,
    ir.id,
    s."name" as status,
    CASE
        WHEN p.name IS NOT NULL THEN p.name
        ELSE p.social_name
    END AS solicitante,
    ir.protocol as protocolo,
    to_char(ir.created_at::timestamp with time zone, 'DD/MM/YYYY às HH24:MI'::text) AS criado_em
from informations_requests ir
join persons p on p.id = ir.persons_id
join statuses s on s.id = ir.status_id
where ir.deleted_at is null and status_id = 1
order by ir.created_at desc;
```

Pontos de atenção:

- As quatro views expõem **o nome completo do solicitante e o protocolo em
  claro** (sem `masked_protocol`/`masked_name`); elas são lidas pelo CMS, onde
  isso é aceitável, mas são views de banco e podem ser consultadas por qualquer
  conexão com acesso ao schema.
- `join persons` é **inner join**: solicitações cujo `persons_id` é nulo não
  aparecem em nenhuma listagem do CMS.
- As views não aplicam `mask_request`; a versão mascarada só existe nas views
  Blade do portal.

## 4. Migrations do módulo

| Migration (v6) | Efeito |
|---|---|
| `v6/database/migrations/2018_04_13_192114_create_informations_requests_table.php` | Cria `informations_requests` (com `engine = InnoDB`, irrelevante em PostgreSQL). |
| `v6/database/migrations/2025_10_14_154644_add_mask_request_to_informations_request_table.php` | Acrescenta `mask_request` (`text`, nullable) depois de `request`. **Sem** guarda `hasColumn`. |
| `v6/database/migrations/2023_04_11_161932_created_view_to_em_tramitacao.php` | Cria a view `em_tramitacao` (formato antigo, `DD/MM/YYYY`). |
| `v6/database/migrations/2023_04_11_163430_created_view_to_lai_atendidos.php` | Cria a view `atendidos`. |
| `v6/database/migrations/2023_04_11_163839_created_view_to_indeferidos.php` | Cria a view `indeferidos`. |
| `v6/database/migrations/2023_04_11_164348_created_view_to_prorrogados.php` | Cria a view `prorrogados`. |
| `v6/database/migrations/2023_05_29_100113_em_tramitacao_formato_24_horas.php` | Recria `em_tramitacao` com `HH24:MI`. |
| `v6/database/migrations/2023_05_29_100519_atendidos_formato_24_horas.php` | Recria `atendidos` com `HH24:MI`. |
| `v6/database/migrations/2023_05_29_100322_prorrogados_formato_24_horas.php` | Recria `prorrogados` com `HH24:MI`. |
| `v6/database/migrations/2023_05_29_100935_indeferidos_formato_24_horas.php` | Recria `indeferidos` com `HH24:MI`. |

Migrations de apoio (não exclusive do módulo, mas usadas por ele):
`v6/database/migrations/2018_03_16_193948_create_persons_table.php`,
`v6/database/migrations/2018_04_02_181803_create_statuses_table.php`,
`v6/database/migrations/2018_04_02_181799_create_status_categories_table.php`,
`v6/database/migrations/2018_04_02_181807_create_archives_table.php`,
`v6/database/migrations/2018_04_13_192115_create_answers_table.php`,
`v6/database/migrations/2019_08_06_171403_create_answer_archives_table.php`.

## 5. Seeds

| Seed (v6) | O que faz |
|---|---|
| `v6/database/seeds/LaiCaturiteSeeder.php` | Popula `persons`, `addresses` e ~15 `informations_requests` de teste (com `protocol` vazio e `ip`/`browser` vazios). **Não popula `answers`.** |
| `v6/database/seeds/UpdateRequestToMaskRequestInInformationRequest.php` | Backfill: percorre `informations_requests` com `created_at < '2025-10-14 00:00:00'` e preenche `mask_request` chamando `(new InformationController())->RequestMaskWithRegex(...)` (mascaramento **determinístico**, sem IA). |
| `v6/database/seeds/UpdateConfigurationOrderSeederRelatorioLai.php` | Insere/atualiza a configuração `relatorio_completolai` ("Desabilitar Listagem da Lai - Relatório Estatístico?", valor 0, categoria 1). |
| `v6/database/seeds/AddToConfigurationDisabledFullListRequests.php` | Insere `full_list_requests_disabled` (checkbox) — é a chave efetivamente lida pelo relatório. |
| `v6/database/seeds/ResponseTimeInConfigurationsSeeder.php` | Insere `response_time` = "Prazo de resposta em dias" = 20 — **usado só pela Ouvidoria**, não pela LAI. |
| `v6/database/seeds/ConfigurationsTableSeeder.php` | Insere `email_lai` (id 14), `titulo_site`, `email`. |
| `v6/database/seeds/StatusCategoriesTableSeeder.php` / `v6/database/seeds/StatusesTableSeeder.php` | Status e categorias usados pela LAI. |
| `v6/database/seeds/MenuOrderSeeder.php` | `MenuOrder` de slug `lai` apontando para `modulo_id = 33`. |
| `v6/database/seeds/ResourcesTableSeeder.php` | `resources` de slug `lai` (id 37) e `respostas-lai` (id 38), ambos `modulo_id = 33`. |
| `v6/database/seeds/RolesTableSeeder.php` | Perfis com as chaves `lai.*` e `respostas-lai.*`. |

## 6. Relações declaradas no model

`v6/app/Http/Model/Prefecture/InformationsRequest.php`:

| Método | Declaração | SQL gerado (verificado em execução) | Situação |
|---|---|---|---|
| `status()` | `hasOne(Status::class, 'id', 'status_id')` | `select * from "statuses" where "statuses"."id" in (?)` | Funciona: os argumentos estão trocados em relação ao convention, mas o resultado é o correto (`statuses.id = informations_requests.status_id`). |
| `person()` | `hasOne(Person::class, 'id', 'persons_id')` | `select * from "persons" where "persons"."id" in (?)` | Funciona pelo mesmo motivo. |
| `answers()` | `hasMany(Answer::class, 'id', 'answers_id')` | `select * from "answers" where "answers"."id" in (?)` com binding `null` | **Quebrada**: `informations_requests` não tem coluna `answers_id`; o binding é sempre `null`, portanto a relação nunca retorna respostas. A relação correta seria `hasMany(Answer::class, 'informations_request_id', 'id')`. |
| `getMaskedProtocolAttribute()` | `maskProtocol($this->protocol)` | — |_accessor_ para exibição pública. |
| `getMaskedNameAttribute()` | `maskName(optional($this->person)->name)` | — | Devolve "Anônimo" quando o nome é vazio. |

Relações inversas em `v6/app/Http/Model/Prefecture/Answer.php`:

| Método | Declaração | Observação |
|---|---|---|
| `informationsRequests()` | `belongsTo(InformationsRequest::class, 'informations_request_id', 'id')` | Correta. |
| `archives()` | `belongsToMany(Archive::class, 'answer_archives')` | Correta (pivot `answer_archives`). |
| `archive()` | `hasOne(Archive::class, 'id', 'archives_id')` | Resolve `archives.id = answers.archives_id`; na prática `archives_id` quase nunca é preenchido (o upload usa o pivot). |
| `statuses()` | `belongsTo(Status::class, 'statuses_id', 'id')` | Aponta para `answers.statuses_id`, **coluna inexistente** em `answers`. Não é usada pelo fluxo da LAI. |

## 7. Scopes de prazo (usados pelo Relatório Estatístico)

`v6/app/Http/Model/Prefecture/InformationsRequest.php`:

| Scope | SQL gerado (verificado) | Observação |
|---|---|---|
| `getByBetweenDate($year, $statusId, $comparationForm)` | `select * from "informations_requests" where "created_at" between ? and ? and "status_id" = ? and EXTRACT(DAY FROM now() - created_at) > 20 group by "status_id"` | O número **20** (prazo legal) está fixo no `whereRaw`. Com `groupBy('status_id')` sobre `select *`, a consulta **falha** em PostgreSQL (`column "informations_requests.id" must appear in the GROUP BY clause`) — verificado executando a query equivalente no banco. Só funciona quando encapsulada em `->count()`, que troca o `select *` por `count(*)`. |
| `getByBetweenDateWithAnswer($year, $statusId, $comparationForm)` | `select * from "answers" inner join "informations_requests" on "answers"."informations_request_id" = "informations_requests"."id" where "informations_requests"."status_id" = ? and EXTRACT(DAY FROM answers.created_at - informations_requests.created_at) > 20 and "informations_requests"."created_at" between ? and ?` | O prazo é medido entre a criação do pedido e a criação da resposta. |
| `byDateBetweenMain($year)` | filtra `created_at` entre o início e o fim do ano e ordena por `created_at desc` | Alimenta a listagem do relatório. |
| `checkParameters($statusId, $comparationForm)` | — | Para status 1/2/3 usa `created_at`; para status 4 usa `updated_at`. A comparação é `>` quando `comparationForm == 1` (fora do prazo) e `<=` caso contrário. |

## 8. Índice de objetos de banco do módulo

| Objeto | Tipo | Origem |
|---|---|---|
| `informations_requests` | tabela | migration 2018-04-13 |
| `informations_requests_id_seq` | sequence | auto-incremento |
| `answers` | tabela | migration 2018-04-13 |
| `answer_archives` | tabela | migration 2019-08-06 |
| `em_tramitacao`, `atendidos`, `indeferidos`, `prorrogados` | views | migrations 2023-04-11 e 2023-05-29 |
| `statuses` (ids 1-4) | registros | `StatusCategoriesTableSeeder` / `StatusesTableSeeder` |
| `persons`, `addresses`, `emails`, `phones` | tabelas | migrations 2018-03-16 e relacionadas |
| `configurations` (`email_lai`, `full_list_requests_disabled`, `relatorio_completolai`, `response_time`, `gemini_api_key`) | registros | seeds de configuração |
| `modulos` (id 33, slug `solicitacoes-lai`) | registro | `v6/database/migrations/2024_11_01_150206_create_modulos_table.php` |
| `resources` (slugs `lai`, `respostas-lai`) | registros | `ResourcesTableSeeder` |
| `menu_orders` (slug `lai`) | registro | `MenuOrderSeeder` |