GTI_GERENCIADOR_FISCAL/fiscal/docs/superpowers/plans/2026-09-16-firebird-mission-00.md

Firebird Mission 00 Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Connect read-only to the installed Firebird environment, inspect only D:\PROJETOS\GTISISTEMAS\Projeto\TESTESOFICIAIS.FDB, perform evidence-backed discovery, and document the result without application writes.

Architecture: A read-only connection module will validate the absolute target path and wrap a compatible Firebird connection configured read-only. A capability-aware collector will query catalog metadata and return sanitized evidence, while separate mapping and fingerprint functions classify findings conservatively. External ERP files are inventory inputs only and are never modified.

Tech Stack: Python 3.10+, fdb already available in the environment, installed Firebird server/client and native tools when present, pytest, existing gti_discovery modules.

Global Constraints

• Files outside D:\PROJETOS\GTISISTEMAS\Projeto are READ_ONLY_EXTERNAL_SYSTEM.
• Only D:\PROJETOS\GTISISTEMAS\Projeto\TESTESOFICIAIS.FDB may be opened or queried.
• OTHER_DATABASES_ACCESSED = 0.
• APPLICATION_DDL_DML_WRITES = 0.
• Do not install an arbitrary Firebird version or change service state.
• Do not execute procedures, generators, DML, DDL, migrations, or metadata changes.
• Never store passwords, connection secrets, raw BLOBs, full routine source, customer data, or unnecessary commercial data.
• Use installed Firebird compatibility evidence before selecting a client or driver.
• Preserve unrelated staged and unstaged work already present in the repository.
• Do not commit unless Git identity is already configured; otherwise report GIT_IDENTITY_NOT_CONFIGURED.

---

Task 1: Inventory the installed Firebird environment

Files:

• Read-only external paths under D:\PROJETOS\GTISISTEMAS\
• Create: docs/superpowers/sdd/2026-09-16-firebird-discovery/task-5-report.md

Interfaces:

• Produces a sanitized inventory of services, processes, executable/DLL versions, tools, and relevant ERP configuration paths.

• Does not open any .FDB during inventory; only the explicitly authorized target may be opened in a later task.

• Step 1: Capture read-only Windows service and process evidence

Run PowerShell commands that only query state:

Get-CimInstance Win32_Service |
  Where-Object { $_.Name -match 'Firebird|InterBase' -or $_.DisplayName -match 'Firebird|InterBase' } |
  Select-Object Name,DisplayName,State,StartMode,PathName
Get-CimInstance Win32_Process |
  Where-Object { $_.Name -match 'firebird|fbserver|fb_inet_server|ibserver' } |
  Select-Object Name,ExecutablePath,ProcessId

Record names, state, executable path, and architecture indicators only. Do not start, stop, restart, or reconfigure anything.

• Step 2: Locate client DLLs, server configuration, and tools without opening databases

Search only names and file properties under D:\PROJETOS\GTISISTEMAS\ and standard program directories for fbclient.dll, firebird.conf, databases.conf, isql.exe, gstat.exe, gbak.exe, fbserver.exe, and fb_inet_server.exe. Use file version metadata, not file replacement or loading against another database.

• Step 3: Read ERP configuration files as sanitized text

Read only .ini, .xml, .cfg, .conf, and .txt candidates that mention Firebird or database connectivity. Extract host, port, path pattern, client library, charset, and dialect when present. Replace every password, token, user secret, and credential value with <redacted> before recording evidence. Do not record the ERP's original database as a target.

• Step 4: Run offline regression tests

Run python -m pytest tests -q. Expected: all existing tests pass and no external file is changed.

• Step 5: Record sanitized inventory

Write only the resulting facts and unavailable checks to task-5-report.md; include ERP_FILES_MODIFIED = 0, no target database access yet, and any version/architecture uncertainty.

• Step 6: Commit only if identity is configured

Check git config user.name and git config user.email. If both are non-empty, stage only the new report and commit:

git add docs/superpowers/sdd/2026-09-16-firebird-discovery/task-5-report.md
git commit -m "chore: record Firebird installation inventory"

If either is empty, do not configure Git and record GIT_IDENTITY_NOT_CONFIGURED.

Task 2: Add an authorized-target connection boundary

Files:

• Modify: src/gti_discovery/read_only.py
• Create or modify: src/gti_discovery/firebird_connection.py
• Test: tests/test_firebird_connection.py

Interfaces:

• Produces AUTHORIZED_DATABASE_PATH: Path and validate_authorized_database_path(path) -> Path.

• Produces connect_authorized_read_only(path, driver, client_library=None), which rejects every path except the resolved authorized target and returns a connection adapter with read_only is True.

• Step 1: Write failing path-guard tests

def test_only_authorized_target_is_accepted(tmp_path):
    target = Path(r"D:\PROJETOS\GTISISTEMAS\Projeto\TESTESOFICIAIS.FDB")
    assert validate_authorized_database_path(target) == target.resolve()

def test_other_database_is_rejected(tmp_path):
    with pytest.raises(ValueError, match="authorized"):
        validate_authorized_database_path(tmp_path / "other.FDB")

Also test case/relative path normalization, missing target rejection, and rejection of a configured ERP-original path.

• Step 2: Run the focused tests and verify failure

Run python -m pytest tests/test_firebird_connection.py -q. Expected: FAIL because the path guard module does not yet exist.

• Step 3: Implement the smallest path guard and adapter

Resolve both requested and authorized paths, compare them using Windows case-insensitive semantics, require the target to be a regular file, and reject any other .FDB. Wrap the installed driver connection behind an adapter exposing only execute, close, and an explicit read_only = True marker. Configure a read-only transaction through the driver API when supported; if it cannot be proven, fail closed rather than claim read-only.

• Step 4: Add a minimal identity-query contract

Expose a function that executes only SELECT MON$DATABASE_NAME FROM MON$DATABASE through ReadOnlyConnection, with no write probe. Assert the adapter is constructed only after the target guard and read-only configuration succeed.

• Step 5: Run focused and full tests

Run python -m pytest tests/test_firebird_connection.py tests/test_read_only.py -q, then python -m pytest tests -q. Expected: PASS.

• Step 6: Commit the boundary when Git identity permits

git add src/gti_discovery/read_only.py src/gti_discovery/firebird_connection.py tests/test_firebird_connection.py
git commit -m "feat: guard authorized Firebird read-only target"

Task 3: Establish real read-only connectivity

Files:

• Create: scripts/run_firebird_discovery.py
• Create: .gitignore update only if required for sanitized local outputs
• Create: docs/superpowers/sdd/2026-09-16-firebird-discovery/task-6-report.md

Interfaces:

• Consumes the installed service/client inventory and connect_authorized_read_only.

• Produces sanitized connection facts: driver version, client path/version, architecture, engine version, dialect, ODS, charset, page size, and engine-reported database name.

• Step 1: Add a no-write script entry point test

Test that the script's target argument is fixed to D:\PROJETOS\GTISISTEMAS\Projeto\TESTESOFICIAIS.FDB, that an alternate path exits before driver invocation, and that the first SQL is exactly the minimal identity query.

• Step 2: Implement installed-client selection

Use only the version and architecture found in Task 1. Prefer the Firebird server connection form and the matching fbclient.dll; do not download or install another client. If no compatible client can be proven, stop with MISSION_00_BLOCKED and document the exact reason.

• Step 3: Connect and execute the first query

Connect only to the authorized absolute path, begin a read-only transaction, run the minimal identity query, and retrieve engine information using read-only metadata APIs. Never query or log credentials.

• Step 4: Verify no-write boundaries

Inspect the executed SQL log in memory and assert every statement is validated SELECT/WITH; assert no call to commit for application writes, rollback as a probe, procedure execution, or generator modification occurs.

• Step 5: Run the script once and record sanitized output

Write task-6-report.md with connection outcome, sanitized strategy, errors, and explicit safety values. Do not write raw query dumps or full database rows.

• Step 6: Run offline tests again

Run python -m pytest tests -q. Expected: PASS. If connection fails, preserve the failure as evidence and continue only with offline capability tests.

Task 4: Expand capability-aware catalog collection

Files:

• Modify: src/gti_discovery/collector.py
• Modify: src/gti_discovery/fingerprint.py
• Test: tests/test_collector.py
• Test: tests/test_fingerprint.py
• Create: docs/superpowers/sdd/2026-09-16-firebird-discovery/task-7-report.md

Interfaces:

• Produces collect_metadata(connection) -> dict with database facts, relations, fields, keys, indexes, foreign keys, generators, dependencies, triggers, procedures, parameters, packages, and optional monitoring capability results.

• Produces sanitized rows and explicit errors, unavailable, and incomplete fields.

• Step 1: Add failing fixture tests for optional catalogs

Use in-memory fake read-only readers to verify a missing RDB$PACKAGES, RDB$DEPENDENCIES, RDB$GENERATORS, or monitoring relation becomes an unavailable/optional result, while permission/query failures remain query_error.

• Step 2: Add all catalog SELECT statements

Add only catalog queries for the required system relations. Do not project trigger/procedure/package source, defaults, BLOB contents, secrets, or application routine execution. Validate every query through ReadOnlyConnection.

• Step 3: Implement conservative row sanitization

Redact binary values, sensitive key names and values, oversized strings, tuple-like raw DB-API rows where necessary, and routine source fields. Preserve only metadata needed for schema mapping and fingerprinting.

• Step 4: Implement capability detection and collection

Execute each optional query independently, record Firebird error type only, and continue with available metadata. Never infer absence from an unavailable catalog.

• Step 5: Update fingerprint context

Include sanitized engine context, relevant catalog structure, product mapping evidence, fiscal fields, image structure, and critical trigger metadata. Preserve ordering for positional fields and index segments; sort unordered entities deterministically.

• Step 6: Run tests and record metadata outcome

Run python -m pytest tests/test_collector.py tests/test_fingerprint.py tests/test_read_only.py -q, then run the real collector only against the authorized target. Save sanitized outcome in task-7-report.md.

Task 5: Identify products and map fiscal/image/incremental evidence

Files:

• Create: src/gti_discovery/mapping.py
• Test: tests/test_mapping.py
• Create: docs/superpowers/sdd/2026-09-16-firebird-discovery/task-8-report.md

Interfaces:

• Produces identify_product_candidates(metadata, samples) -> list[dict].

• Produces build_schema_mapping(metadata, samples) -> dict with each mapping classified as CONFIRMED, INFERRED, or UNKNOWN.

• Produces no write operation and never returns raw BLOB or credential contents.

• Step 1: Write failing mapping tests

Create fixtures with explicit PK/FK/index metadata and masked sample rows. Assert that product identity is CONFIRMED only when structural and data evidence agree; name-only candidates are INFERRED; absent evidence is UNKNOWN.

• Step 2: Implement candidate scoring from catalog evidence

Use relation/field names, data types, PK/FK/index information, approximate counts when available, and minimal permitted scalar samples. Do not copy full rows or treat conventional names as proof.

• Step 3: Implement product and stock mapping

Map only fields directly supported by evidence: PK, internal code, descriptions, barcode, reference, unit, group, brand/manufacturer, status, timestamps, stock relation, and relevant constraints.

• Step 4: Implement fiscal inventory mapping

Inventory NCM, CEST, CST, CSOSN, CFOP, origin, ICMS, PIS, COFINS, IPI, FCP, ST, rates, reductions, nature/operation, company, branch, regime, and UF relationships. Describe representation only; do not implement tax rules.

• Step 5: Implement image and incremental mapping

Identify image/file/path/URL/BLOB candidates with subtype and relationship metadata, excluding binary contents. Identify timestamps, audit tables, generators, version fields, and triggers that can support new/changed product detection. If evidence is absent, return UNKNOWN and document the gap.

• Step 6: Analyze trigger and routine impact as metadata

Record relevant trigger timing/events and textual dependencies/source only after sanitization. Identify possible secondary tables and generators from metadata without executing any routine. State that future direct UPDATE safety remains unresolved unless evidence proves semantics.

• Step 7: Run mapping tests and produce sanitized evidence

Run python -m pytest tests/test_mapping.py tests -q; generate the mapping report with only masked samples and classifications.

Task 6: Update current-state documentation and final verification

Files:

• Modify: docs/AI_PROJECT_STATE.md
• Modify: docs/DATABASE_DISCOVERY.md
• Modify: docs/SCHEMA_MAPPING.md
• Create: docs/MISSION_00_REPORT.md
• Create or modify: .superpowers/sdd/2026-09-16-firebird-discovery/task-9-report.md

Interfaces:

• Consumes sanitized reports from Tasks 1 through 5 and the generated fingerprint.

• Produces the mandatory final report with a single verdict and explicit database/filesystem safety assertions.

• Step 1: Replace stale connectivity claims with current evidence

Update the three current-state documents to distinguish confirmed connection facts from unavailable catalogs and inferred mappings. Do not state a schema finding without direct evidence.

• Step 2: Write the mandatory final report sections

Include verdict, filesystem safety, authorized database, other database count, application write count, installed Firebird details, connectivity, database facts, products, fiscal, images, incremental processing, triggers/procedures, fingerprint, tests, Git, gaps, and the next-step prohibition list.

• Step 3: Scan for secrets and unauthorized targets

Search project text for password-like keys, connection strings, raw BLOB markers, ERP-original paths, and other .FDB access claims. Remove only newly introduced sensitive content; do not alter unrelated user changes.

• Step 4: Run final verification commands

Run:

python -m pytest tests -q
git diff --check
git status --short --branch
git config user.name
git config user.email

Verify TESTESOFICIAIS.FDB remains ignored, no external path was modified, no DDL/DML exists in executable discovery paths, and no other .FDB was opened.

• Step 5: Commit only the mission changes when identity is valid

If Git identity is configured, stage only the mission files and create one commit:

git add src tests docs .superpowers/sdd/2026-09-16-firebird-discovery
git commit -m "feat: complete read-only Firebird discovery"

If identity is not configured, leave the working tree uncommitted and report GIT_IDENTITY_NOT_CONFIGURED; never change global Git configuration.

Plan Self-Review

• The approved design's connection, path guard, sanitization, capability detection, mapping, fingerprint, testing, documentation, and Git requirements are covered by Tasks 1-6.
• Every database operation is constrained to the authorized target and read-only SQL; no task permits service lifecycle changes or external file writes.
• Later interfaces use the exact names and return shapes defined in earlier tasks.
• No task depends on an unproved Firebird version; compatibility is established from installed evidence before connection.
• A failed connection remains a documented MISSION_00_BLOCKED outcome rather than a reason to use another database or install an arbitrary client.