Encoding answers in the database ================================ All data points entered in the system are stored as an instance of ``Answer`` (see :doc:`database schema `). .. autoclass:: survey.models.Unit Entering quantitative measurements ---------------------------------- Quantitative (numeric) measurements are recorded through the `Records quantitative measurements API`_. When `baseline_at` is specified, the measurement refers to a relative value since `baseline_at`. When `baseline_at` is not specified, the intent is to record an absolute measurement at time `created_at`. Absolute measurements database encoding ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Absolute measurements, for example the area occupied on the ground by a factory, are recorded as an `Answer` whose `created_at` field is the date of the measurement and a `Sample` whose `created_at` field is the date the record was created in the database. .. code-block:: http POST /api/supplier-1/filters/accounts/factory-size/values HTTP/1.1 .. code-block:: json { "created_at":"2025-01-01", "items":[{ "slug":"main-factory", "measured":"65", "unit":"m2" }] } TABLE survey_answer: +----+------------+-------------+---------------+--------+---------------+ | id | created_at | measured | unit | sample | question | +====+============+=============+===============+========+===============+ | 1 | 2025-01-01 | 65 | m2 | 1 | factory-size | +----+------------+-------------+---------------+--------+---------------+ TABLE survey_sample: +----+------------+--------------+----------------+-----------+ | id | created_at | account | campaign | is_frozen | +====+============+==============+================+===========+ | 1 | 2025-01-27 | main-factory | null | true | +----+------------+--------------+----------------+-----------+ Relative measurements database encoding ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Relative measurements, like the amount of CO2 emissions for a factory during a time period are recorded as relative measurements between a start date (`baseline_at`) and an end date. When no previous baseline exists (typically the first time a relative measurement is encoded), the API records a dummy record with value zero to encode the date at which the recording period started (i.e. baseline). .. code-block:: http POST /api/supplier-1/filters/accounts/ghg-emissions/values HTTP/1.1 .. code-block:: json { "baseline_at":"2024-01-01", "created_at":"2025-01-01", "items":[{ "slug":"main-factory", "measured":"12", "unit":"tons" }] } TABLE survey_answer: +----+------------+-------------+---------------+--------+---------------+ | id | created_at | measured | unit | sample | question | +====+============+=============+===============+========+===============+ | 1 | 2024-01-01 | 0 | tons | 1 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ | 2 | 2025-01-01 | 12 | tons | 2 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ TABLE survey_sample: +----+------------+--------------+----------------+-----------+ | id | created_at | account | campaign | is_frozen | +====+============+==============+================+===========+ | 1 | 2025-01-27 | main-factory | null | true | +----+------------+--------------+----------------+-----------+ | 2 | 2025-01-27 | main-factory | null | true | +----+------------+--------------+----------------+-----------+ When the specified baseline already has measurements recorded at that date, there is no gap in measurements, and thus the API doesn't create an additional dummy record to encode the baseline date. .. code-block:: http POST /api/supplier-1/filters/accounts/ghg-emissions/values HTTP/1.1 .. code-block:: json { "baseline_at":"2024-01-01", "created_at":"2025-01-01", "items":[{ "slug":"main-factory", "measured":"8", "unit":"tons" }] } TABLE survey_answer: +----+------------+-------------+---------------+--------+---------------+ | id | created_at | measured | unit | sample | question | +====+============+=============+===============+========+===============+ | 1 | 2023-01-01 | 0 | tons | 1 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ | 2 | 2024-01-01 | 12 | tons | 2 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ | 3 | 2025-01-01 | 8 | tons | 3 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ TABLE survey_sample: +----+------------+--------------+----------------+-----------+ | id | created_at | account | campaign | is_frozen | +====+============+==============+================+===========+ | 1 | 2024-01-12 | main-factory | null | true | +----+------------+--------------+----------------+-----------+ | 2 | 2024-01-12 | main-factory | null | true | +----+------------+--------------+----------------+-----------+ | 3 | 2025-01-27 | main-factory | null | true | +----+------------+--------------+----------------+-----------+ Responding to a questionnaire ----------------------------- Answering a single choice question ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ In questionnaires (i.e. ``Campaign``) with closed questions, phrased as for example: "Does your organization has a code of conduct?", answers are saved in the database with a unit of type ``SYSTEM_ENUMERATED``. .. code-block:: http POST /api/supplier-1/sample/4c6675a5d5af46c796b8033a7731a86e/answers/code-of-conduct HTTP/1.1 .. code-block:: json { "measured": "Yes" } TABLE survey_answer: +----+------------+-------------+---------------+--------+-----------------+ | id | created_at | measured | unit | sample | question | +====+============+=============+===============+========+=================+ | 1 | 2025-01-27 | 1 | yes-no | 1 | code-of-conduct | +----+------------+-------------+---------------+--------+-----------------+ TABLE survey_choice: +----+----------+---------+---------------+ | id | unit | rank | text | +====+==========+=========+===============+ | 1 | yes-no | 1 | Yes | +----+----------+---------+---------------+ | 2 | yes-no | 2 | No | +----+----------+---------+---------------+ TABLE survey_sample: +----+------------+--------------+----------------+ | id | created_at | account | campaign | +====+============+==============+================+ | 1 | 2025-01-27 | supplier 1 | sustainability | +----+------------+--------------+----------------+ The primary key of the ``Choice`` model for the enumerated value matching the answer is saved in the ``Answer.measured`` field. Therefore most computations can be done on the Answer table alone, but you will need to do a SQL join with the ``survey_choice`` table for User Interface presentation. Answering an open question ^^^^^^^^^^^^^^^^^^^^^^^^^^ In questionnaires (i.e. ``Campaign``) with open questions, phrased as for example: "What is your improvement plan?", answers are saved in the database with a unit of type ``SYSTEM_FREETEXT``. .. code-block:: http POST /api/supplier-1/sample/4c6675a5d5af46c796b8033a7731a86e/answers/improvement-plan HTTP/1.1 .. code-block:: json { "measured": "Introducing recycle bins in cafetaria" } TABLE survey_answer: +----+------------+-------------+---------------+--------+------------------+ | id | created_at | measured | unit | sample | question | +====+============+=============+===============+========+==================+ | 1 | 2025-01-27 | 1 | freetext | 1 | improvement-plan | +----+------------+-------------+---------------+--------+------------------+ TABLE survey_choice: +----+----------+---------+-------------------------------------------------+ | id | unit | rank | text | +====+==========+=========+=================================================+ | 1 | freetext | - | Introducing recycle bins in cafetaria | +----+----------+---------+-------------------------------------------------+ TABLE survey_sample: +----+------------+--------------+----------------+ | id | created_at | account | campaign | +====+============+==============+================+ | 1 | 2025-01-27 | supplier 1 | sustainability | +----+------------+--------------+----------------+ ``Answer.measured`` is used in that case to index the actual text inputed by a user inside the ``survey_choice`` table. Reporting metrics over a time period ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ In the database, an answer about GHG Emissions of "10 Tons of CO2e in 2021" is encoded as follow in the database: TABLE survey_answer: +----+------------+-------------+---------------+--------+---------------+ | id | created_at | measured | unit | sample | question | +====+============+=============+===============+========+===============+ | 0 | 2023-01-27 | 10 | tons-period | 1 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ | 1 | 2023-01-27 | 2021-01-01 | starts-at | 1 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ | 2 | 2023-01-27 | 2021-12-31 | ends-at | 1 | ghg-emissions | +----+------------+-------------+---------------+--------+---------------+ TABLE survey_sample: +----+------------+--------------+----------------+ | id | created_at | account | campaign | +====+============+==============+================+ | 1 | 2023-01-27 | supplier 1 | sustainability | +----+------------+--------------+----------------+ Special cases ------------- Absolute measurements in alternative units ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ``Answer`` will be created such that Answer.unit == Answer.question.default_unit .. _Records quantitative measurements API: https://www.djaodjin.com/docs/reference/djaopsp/2022-09-14/api/#createAccountsFilterValues