Debris ingestion persists nothing, and the model schema fails to build on SQLite

[Amayyas]

Summary

Three defects introduced by the MongoDB → PostgreSQL migration. Each has a one-line cause but an outsized effect — the most serious one means Kepler currently ingests no debris at all, on either backend.

I found these while checking that the multi-source provider work (#15) still ran after the migration; the backend test suite does not currently pass on main.

---

1. Debris ingestion persists nothing 🔴

_gp_to_doc emits a single satellite-shaped dict for every provider, but debris is a narrower table. It has none of objectType, countryCode, launchDate, updatedAt, status, fuel_percentage, operational_mode.

Passing those keys into the write makes every debris record fail:

PostgreSQL: CompileError: Unconsumed column names: countryCode, launchDate,
            updatedAt, status, fuel_percentage, objectType, operational_mode
SQLite:     TypeError: 'objectType' is an invalid keyword argument for Debris

On PostgreSQL the whole batch is caught by the except, rolled back and marked failed; on SQLite it fails row by row. Either way the debris catalog stays empty — on a platform whose purpose is debris collision avoidance.

Reproduce:

from sqlalchemy.dialects.postgresql import insert as pg_insert
from models.db_models import Debris
from orbital.spacetrack import spacetrack_service

doc = spacetrack_service._gp_to_doc({"NORAD_CAT_ID": "99999", "OBJECT_NAME": "DEB"}, "DEBRIS")
pg_insert(Debris).values([doc]).compile(dialect=postgresql.dialect())   # CompileError

2. updatedAt is written as a string into a DateTime column 🟠

orbital/spacetrack.py builds now = datetime.datetime.utcnow().isoformat() — a string — and assigns it to updatedAt, which is DateTime(timezone=True). This is a MongoDB-era leftover: Mongo stored ISO strings, PostgreSQL does not.

sqlalchemy.exc.StatementError: SQLite DateTime type only accepts Python
datetime and date objects as input.

Satellite ingestion therefore fails on SQLite. (utcnow() is also deprecated in Python 3.12.)

3. spaceWeather.recorded_at is indexed twice 🟠

The column is indexed both explicitly and implicitly:

__table_args__ = (Index("ix_spaceweather_recorded_at", "recorded_at"),)   # explicit
recorded_at = mapped_column(..., index=True)   # → ix_spaceWeather_recorded_at

The two names differ only by case, which behaves differently per backend:

• SQLite — identifiers are case-insensitive, so create_all() aborts:
  sqlite3.OperationalError: index ix_spaceweather_recorded_at already exists.
  This takes 10 tests down at fixture setup.
• PostgreSQL — SQLAlchemy quotes the mixed-case name, so two redundant indexes are created on the same column. No error, but every write pays for both.

Confirmed by compiling the DDL:

CREATE INDEX "ix_spaceWeather_recorded_at" ON "spaceWeather" (recorded_at)
CREATE INDEX ix_spaceweather_recorded_at  ON "spaceWeather" (recorded_at)

---

Impact

	Before	After fix
Backend test suite	40 passed, 10 errors	55 passed
Debris ingested	0	25/25 real CelesTrak records

Steps to reproduce

cd backend
pip install -r requirements-dev.txt
pytest tests/ -q

Environment

main @ 2510ede, Python 3.12, SQLAlchemy 2.0.36.

Proposed fix

• Project each document onto the target model's real columns before the write (fixes 1 for both dialects).
• Use a timezone-aware datetime instead of an ISO string (fixes 2).
• Drop the redundant index=True, keeping the explicitly-named index that matches the convention used by the other tables (fixes 3).

Plus regression tests that fail without the fixes: schema invariants (unique index names, no duplicate indexes, create_all on a clean database) and two ingestion tests for debris persistence and the timestamp type.

I have this ready and verified — happy to open the PR.