Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add database auditability #196

Draft
wants to merge 13 commits into
base: main
Choose a base branch
from
Draft

Add database auditability #196

wants to merge 13 commits into from

Conversation

jmaupetit
Copy link
Member

@jmaupetit jmaupetit commented Oct 15, 2024 •
edited
Loading

Purpose

We need to be able to track who changed what when on most of our data for auditability purpose.

Proposal

  • add created_by_id and updated_by_id fields in auditable models
  • add track_model_changes utility that can be triggered upon SQLAlchemy events
  • integrate model changes tracking in API routes
  • assess performance impact

@jmaupetit jmaupetit added feature WIP API Item related to the API service labels Oct 15, 2024
@jmaupetit jmaupetit self-assigned this Oct 15, 2024
@jmaupetit jmaupetit linked an issue Oct 15, 2024 that may be closed by this pull request
Actions auditability #187
Open
3 tasks
jmaupetit
jmaupetit commented Oct 18, 2024
View reviewed changes
src/api/tests/api/v1/routers/test_statique.py
)
).one()
assert pdc7.paiement_cb
assert len(pdc7.audits) == 1
Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Maybe it fails because when listening the "after_update" event in an upsert context, the event is never raised on "conflict do update"?

Copy link
Member Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Maybe we should create a custom event?

@github-actions GitHub Actions
Copy link

github-actions bot commented Dec 6, 2024

Current benchmark

Type Name Request Count Failure Count Median Response Time Average Response Time Min Response Time Max Response Time Average Content Size Requests/s Failures/s 50% 66% 75% 80% 90% 95% 98% 99% 99.9% 99.99% 100% git timestamp version
GET /auth/whoami 32 0 730.0 866.2685219687517 15.376008000004049 1923.123930999992 160.0 1.1028363496839877 0.0 790 1300 1500 1500 1600 1900 1900 1900 1900 1900 1900 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
POST /dynamique/session/ 23 0 830.0 970.715874391306 28.586133000004565 2576.4407489999994 45.0 0.792663626335366 0.0 830 1200 1900 2000 2300 2300 2600 2600 2600 2600 2600 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
POST /dynamique/session/bulk 24 0 1100.0 1053.2342091666667 30.455400000022337 2598.217656999992 411.0 0.8271272622629906 0.0 1200 1400 1600 1800 2000 2100 2600 2600 2600 2600 2600 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
GET /dynamique/status/ 30 0 900.0 967.8429300666664 55.93745799998828 2303.59512299998 285.0 1.0339090778287383 0.0 1100 1200 1500 1600 1900 2200 2300 2300 2300 2300 2300 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
POST /dynamique/status/ 22 0 1100.0 1161.8225202727283 89.3319460000157 2431.947078999997 45.0 0.7581999904077414 0.0 1200 1500 1500 1800 2100 2300 2400 2400 2400 2400 2400 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
GET /dynamique/status/FRALLEGO002006P3 33 0 880.0 869.5323173030295 23.978120999998964 2261.5847520000043 270.030303030303 1.1372999856116122 0.0 880 1200 1200 1500 1800 2200 2300 2300 2300 2300 2300 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
GET /dynamique/status/FRALLEGO002006P3/history 19 0 1100.0 1207.3398554210496 82.86764199999652 2584.401211999989 34379.94736842105 0.6548090826248676 0.0 1100 1800 1900 2000 2200 2600 2600 2600 2600 2600 2600 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
POST /dynamique/status/bulk 21 0 1100.0 1211.178224523811 25.54094199999213 2170.4290489999776 411.0 0.7237363544801168 0.0 1100 1500 1700 2000 2200 2200 2200 2200 2200 2200 2200 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
POST /statique/ 24 5 810.0 1109.5629882916653 53.94265000001042 2302.1856000000016 58.208333333333336 0.8271272622629906 0.172318179638123 810 1200 2000 2100 2100 2200 2300 2300 2300 2300 2300 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
GET /statique/?limit=10 20 0 700.0 967.3993064499996 26.743387999999868 2067.5524870000004 13826.3 0.6892727185524922 0.0 940 1600 1600 1800 2000 2100 2100 2100 2100 2100 2100 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
GET /statique/?limit=100 21 0 900.0 993.7695109999988 155.39545400000063 2300.8891789999952 129006.19047619047 0.7237363544801168 0.0 900 1100 1600 1600 1900 2100 2300 2300 2300 2300 2300 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
PUT /statique/FR073EP7QU5233 32 0 900.0 953.4075774062468 52.71086700000183 2288.332237999981 1371.0 1.1028363496839877 0.0 900 1500 1600 1700 1800 2100 2300 2300 2300 2300 2300 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
GET /statique/FRALLEGO002006P3 27 0 1100.0 1111.2238942222211 23.201905000007628 2569.578974999985 1202.0 0.9305181700458643 0.0 1100 1500 1900 2000 2100 2300 2600 2600 2600 2600 2600 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
POST /statique/bulk 22 0 1500.0 1485.7709665909042 687.5487349999787 2276.007451999988 1129.6363636363635 0.7581999904077414 0.0 1500 1900 1900 1900 2200 2300 2300 2300 2300 2300 2300 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0
Aggregated 350 5 960.0 1048.6701002457137 15.376008000004049 2598.217656999992 10813.002857142856 12.062272574668611 0.172318179638123 970 1400 1600 1800 2100 2200 2300 2600 2600 2600 2600 32fdc1f 2024-12-06 15:20:27.125709+00:00 0.15.0

Comparison with the latest previous benchmark

A lower (negative) value means the current version performs better than the previous one.

Type Name Request Count Failure Count Median Response Time Average Response Time Min Response Time Max Response Time Average Content Size Requests/s
GET /auth/whoami
POST /dynamique/session/
POST /dynamique/session/bulk
GET /dynamique/status/
POST /dynamique/status/
GET /dynamique/status/FRALLEGO002006P3
GET /dynamique/status/FRALLEGO002006P3/history
POST /dynamique/status/bulk
POST /statique/
GET /statique/?limit=10
GET /statique/?limit=100
PUT /statique/FR073EP7QU5233
GET /statique/FRALLEGO002006P3
POST /statique/bulk
Aggregated

@github-actions GitHub Actions
Copy link

github-actions bot commented Dec 6, 2024

Current benchmark

Type Name Request Count Failure Count Median Response Time Average Response Time Min Response Time Max Response Time Average Content Size Requests/s Failures/s 50% 66% 75% 80% 90% 95% 98% 99% 99.9% 99.99% 100% git timestamp version
GET /auth/whoami 30 0 460.0 573.2400602999993 51.46284800002832 1393.170199999986 160.0 1.0385354050857838 0.0 470 640 810 940 1100 1300 1400 1400 1400 1400 1400 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
POST /dynamique/session/ 23 0 830.0 885.9670067391344 35.62514100002545 2666.469462000009 45.0 0.7962104772324342 0.0 830 980 1100 1400 1700 2000 2700 2700 2700 2700 2700 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
POST /dynamique/session/bulk 32 0 870.0 905.6074112812506 51.22549800000797 2242.2826459999783 411.0 1.1077710987581697 0.0 880 1100 1200 1300 1600 2100 2200 2200 2200 2200 2200 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
GET /dynamique/status/ 29 0 960.0 942.3147984827552 25.58757400004197 2648.454751999964 265.48275862068965 1.003917558249591 0.0 960 1100 1200 1300 1800 2000 2600 2600 2600 2600 2600 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
POST /dynamique/status/ 32 0 870.0 749.1528034062541 20.73021899997229 1805.978128999982 45.0 1.1077710987581697 0.0 900 1000 1100 1100 1300 1800 1800 1800 1800 1800 1800 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
GET /dynamique/status/FRALLEGO002006P3 25 0 860.0 836.4396412000044 84.40808799997512 2015.637216000016 283.0 0.8654461709048198 0.0 860 1200 1300 1400 1600 1700 2000 2000 2000 2000 2000 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
GET /dynamique/status/FRALLEGO002006P3/history 22 0 880.0 805.3933263636335 84.3983499999581 2401.8556559999524 24373.36363636364 0.7615926303962415 0.0 960 1000 1100 1100 1100 1200 2400 2400 2400 2400 2400 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
POST /dynamique/status/bulk 16 0 990.0 1067.4464616874957 370.9126469999546 2647.5501829999644 411.0 0.5538855493790847 0.0 1000 1000 1100 1100 2200 2600 2600 2600 2600 2600 2600 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
POST /statique/ 32 4 880.0 831.0985120937497 39.19857700003604 2128.1343690000085 62.125 1.1077710987581697 0.1384713873447711 890 990 1100 1100 1400 1600 2100 2100 2100 2100 2100 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
GET /statique/?limit=10 31 0 720.0 875.1507326451615 20.56057299995473 2242.14903699999 13826.709677419354 1.0731532519219766 0.0 720 1100 1300 1300 1700 2000 2200 2200 2200 2200 2200 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
GET /statique/?limit=100 35 0 1000.0 1089.0982877714268 141.71032899997726 2647.5271999999563 129006.65714285716 1.2116246392667478 0.0 1000 1200 1400 1400 2000 2200 2600 2600 2600 2600 2600 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
PUT /statique/FR073EP7QU5233 36 0 710.0 752.984780250006 52.49309200002017 2220.407424999962 1371.0 1.2462424861029406 0.0 720 940 1000 1100 1500 2200 2200 2200 2200 2200 2200 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
GET /statique/FRALLEGO002006P3 28 0 740.0 830.5227126428524 134.5299039999759 2242.1535949999907 1202.0 0.9692997114133982 0.0 750 1000 1100 1200 1600 1700 2200 2200 2200 2200 2200 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
POST /statique/bulk 21 0 980.0 1101.0918275714243 483.993393999981 2380.632354999989 1118.6666666666667 0.7269747835600486 0.0 980 1100 1200 1400 1700 2100 2400 2400 2400 2400 2400 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0
Aggregated 392 4 870.0 864.5177759693878 20.56057299995473 2666.469462000009 14363.122448979591 13.570195959787576 0.1384713873447711 870 1000 1100 1200 1600 2000 2200 2600 2700 2700 2700 d7f20c0 2024-12-06 16:11:57.882352+00:00 0.15.0

Comparison with the latest previous benchmark

A lower (negative) value means the current version performs better than the previous one.

Type Name Request Count Failure Count Median Response Time Average Response Time Min Response Time Max Response Time Average Content Size Requests/s
GET /auth/whoami
POST /dynamique/session/
POST /dynamique/session/bulk
GET /dynamique/status/
POST /dynamique/status/
GET /dynamique/status/FRALLEGO002006P3
GET /dynamique/status/FRALLEGO002006P3/history
POST /dynamique/status/bulk
POST /statique/
GET /statique/?limit=10
GET /statique/?limit=100
PUT /statique/FR073EP7QU5233
GET /statique/FRALLEGO002006P3
POST /statique/bulk
Aggregated

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees

@jmaupetit jmaupetit

Labels
API Item related to the API service feature WIP
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

Actions auditability
1 participant
@jmaupetit