Encoding answers in the database

All data points entered in the system are stored as an instance of Answer (see database schema).

class survey.models.Unit(*args, **kwargs)

Unit in which an Answer.measured value is collected.

There are 6 types of units, each with its own use.

Unit Type		Description
SYSTEM_STANDARD		SI or International System of Units (ex: m, kg, J)
SYSTEM_IMPERIAL		Imperial/US customary measurement system (ex: ft, lb)
SYSTEM_RANK		Natural positive integer (ex: score, percentage)
SYSTEM_ENUMERATED		Finite set of named values (ex: Yes, No, N/A)
SYSTEM_FREETEXT		UTF-8 text string
SYSTEM_DATETIME		Date/time in Gregorian calendar

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.

POST /api/supplier-1/filters/accounts/factory-size/values HTTP/1.1

{
      "created_at":"2023-01-01",
      "items":[{
        "slug":"main-factory",
        "measured":"65",
        "unit":"m2"
      }]
}

TABLE survey_answer:

id	created_at	measured	unit	sample	question
1	2022-01-01	65	m2	1	factory-size

TABLE survey_sample:

id	created_at	account	campaign	is_frozen
1	2023-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).

POST /api/supplier-1/filters/accounts/ghg-emissions/values HTTP/1.1

{
      "baseline_at":"2022-01-01",
      "created_at":"2023-01-01",
      "items":[{
        "slug":"main-factory",
        "measured":"12",
        "unit":"tons"
      }]
}

TABLE survey_answer:

id	created_at	measured	unit	sample	question
1	2022-01-01	0	tons	1	ghg-emissions
2	2023-01-01	12	tons	2	ghg-emissions

TABLE survey_sample:

id	created_at	account	campaign	is_frozen
1	2023-01-27	main-factory	null	true
2	2023-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.

POST /api/supplier-1/filters/accounts/ghg-emissions/values HTTP/1.1

{
  "baseline_at":"2023-01-01",
  "created_at":"2024-01-01",
  "items":[{
    "slug":"main-factory",
    "measured":"8",
    "unit":"tons"
  }]
}

TABLE survey_answer:

id	created_at	measured	unit	sample	question
1	2022-01-01	0	tons	1	ghg-emissions
2	2023-01-01	12	tons	2	ghg-emissions
3	2024-01-01	8	tons	3	ghg-emissions

TABLE survey_sample:

id	created_at	account	campaign	is_frozen
1	2023-01-27	main-factory	null	true
2	2023-01-27	main-factory	null	true
3	2024-01-12	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.

POST /api/supplier-1/sample/4c6675a5d5af46c796b8033a7731a86e/answers/code-of-conduct HTTP/1.1

{
      "measured": "Yes"
}

TABLE survey_answer:

id	created_at	measured	unit	sample	question
1	2023-01-01	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	2023-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.

POST /api/supplier-1/sample/4c6675a5d5af46c796b8033a7731a86e/answers/improvement-plan HTTP/1.1

{
      "measured": "Introducing recycle bins in cafetaria"
}

TABLE survey_answer:

id	created_at	measured	unit	sample	question
1	2023-01-01	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	2023-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