File size: 3,871 Bytes
ab6d9f6
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1091d74
 
 
 
 
 
 
ab6d9f6
e5cb827
 
 
1091d74
 
 
 
 
 
 
 
ab6d9f6
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
"""SQLAlchemy models for the prediction monitoring database.

A single Table definition is parametrised by name so the production table
(``predictions_log``) and the CI test table (``predictions_log_test``) share
exactly the same schema without duplication.

The 768 engineered features are stored in a JSONB column rather than as
individual columns — three feature names exceed PostgreSQL's 63-character
identifier limit, and a flat JSONB also keeps the schema stable when the
feature engineering pipeline evolves.
"""

from __future__ import annotations

from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    Float,
    Integer,
    MetaData,
    String,
    Table,
    Text,
    func,
)
from sqlalchemy.dialects.postgresql import JSONB, UUID

PROD_TABLE_NAME = "predictions_log"
TEST_TABLE_NAME = "predictions_log_test"


def build_predictions_log_table(name: str, metadata: MetaData) -> Table:
    """Return a Table object with the prediction log schema, bound to ``metadata``.

    Args:
        name: physical table name in PostgreSQL.
        metadata: SQLAlchemy MetaData to attach the Table to. Pass a fresh
            MetaData per table to avoid duplicate-key collisions in tests.

    Returns:
        Table ready to be created via ``metadata.create_all(engine)``.
    """
    return Table(
        name,
        metadata,
        # Identity
        Column(
            "id",
            UUID(as_uuid=True),
            primary_key=True,
            server_default=func.gen_random_uuid(),
        ),
        Column(
            "timestamp",
            DateTime(timezone=True),
            nullable=False,
            server_default=func.now(),
            index=True,
        ),
        Column("sk_id_curr", Integer, nullable=False, index=True),
        Column("client_known", Boolean, nullable=False),
        # Operational metrics. All timings are stored as INTEGER milliseconds —
        # we always round at the Python layer (``round()``, not ``int()``)
        # before insert, which gives unbiased values. The handler ``latency_ms``
        # only covers the request handler body (assembly + inference + return
        # construction). The DB INSERT itself is measured separately as
        # ``db_log_ms`` so the full server-side budget can be reconstructed as
        # ``latency_ms + db_log_ms``.
        Column("latency_ms", Integer, nullable=False),
        # Fine-grained timings added in étape 4. Nullable so legacy rows
        # (pre-instrumentation) remain valid. Populated only on the success
        # path — error rows leave them NULL.
        Column("feature_assembly_ms", Integer, nullable=True),
        Column("inference_ms", Integer, nullable=True),
        Column("inference_cpu_ms", Integer, nullable=True),
        # Plumbing = latency_ms - feature_assembly_ms - inference_ms, computed
        # once in the API at log time so the dashboard (and any other reader)
        # reads a self-describing column instead of re-deriving the formula.
        Column("plumbing_ms", Integer, nullable=True),
        Column("db_log_ms", Integer, nullable=True),
        Column("status_code", Integer, nullable=False, server_default="200"),
        Column("error_message", Text, nullable=True),
        # Payloads
        Column("raw_input", JSONB, nullable=False),
        Column("features", JSONB, nullable=False),
        # Model output. Nullable so error rows (status != 200) can carry NULL.
        Column("probability_default", Float, nullable=True),
        Column("decision", String(16), nullable=False, index=True),
        Column("threshold", Float, nullable=False),
        Column("model_version", String(64), nullable=False),
        Column("top_shap", JSONB, nullable=True),
        # Ground truth — filled post-hoc when business feedback arrives
        Column("ground_truth", Integer, nullable=True),
    )