File size: 2,489 Bytes
42dfbed
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
CREATE TABLE IF NOT EXISTS api_requests (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL,
    endpoint VARCHAR(50) NOT NULL,
    status_code INTEGER NOT NULL,
    success BOOLEAN NOT NULL,
    error_message TEXT,
    latency_ms FLOAT,
    model_name VARCHAR(255),
    model_version VARCHAR(50),
    input_json JSONB,
    output_json JSONB
);

CREATE TABLE IF NOT EXISTS inference_steps (
    id SERIAL PRIMARY KEY,
    request_id INTEGER REFERENCES api_requests(id),
    step_name VARCHAR(100) NOT NULL,
    success BOOLEAN NOT NULL,
    error_message TEXT,
    duration_ms FLOAT,
    details_json JSONB
);

CREATE TABLE IF NOT EXISTS api_predictions (
    id SERIAL PRIMARY KEY,
    request_id INTEGER REFERENCES api_requests(id),
    area VARCHAR(255),
    item VARCHAR(255),
    recommended_crop VARCHAR(255),
    predicted_yield FLOAT,
    n_candidates INTEGER,
    prediction_type VARCHAR(50)
);


CREATE TABLE IF NOT EXISTS api_recommendation_details (
    id SERIAL PRIMARY KEY,
    request_id INTEGER REFERENCES api_requests(id),
    rank INTEGER NOT NULL,
    item VARCHAR(255) NOT NULL,
    predicted_yield FLOAT NOT NULL
);

CREATE TABLE IF NOT EXISTS country_crop_mapping (
    id SERIAL PRIMARY KEY,
    area VARCHAR(255) NOT NULL,
    item VARCHAR(255) NOT NULL,
    source VARCHAR(50) DEFAULT 'train',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(area, item)
);

CREATE TABLE IF NOT EXISTS reference_distributions (
    id SERIAL PRIMARY KEY,
    feature_name VARCHAR(100) NOT NULL,
    feature_value TEXT NOT NULL,
    source VARCHAR(50) DEFAULT 'train',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_reference_distributions_feature
ON reference_distributions(feature_name);CREATE INDEX IF NOT EXISTS idx_country_crop_mapping_area
ON country_crop_mapping(area);

CREATE INDEX IF NOT EXISTS idx_country_crop_mapping_item
ON country_crop_mapping(item);

CREATE INDEX IF NOT EXISTS idx_api_requests_timestamp
ON api_requests(timestamp);

CREATE INDEX IF NOT EXISTS idx_api_requests_endpoint
ON api_requests(endpoint);

CREATE INDEX IF NOT EXISTS idx_api_predictions_area
ON api_predictions(area);

CREATE INDEX IF NOT EXISTS idx_api_predictions_recommended_crop
ON api_predictions(recommended_crop);

CREATE INDEX IF NOT EXISTS idx_recommendation_details_request_id
ON api_recommendation_details(request_id);

CREATE INDEX IF NOT EXISTS idx_recommendation_details_item
ON api_recommendation_details(item);